Is this simple or complicated?

[hannahskellam]

Hi I am wondering if someone can help me. I have this question...

Everyday a factory makes 200 cupcakes
When the factory makes 550 cupcakes it needs to deliver them to the shop
Make a spreadsheet that lets you know when it is time to make a delivery.
*The factory cannot deliver on weekends.

I am a teacher and was going to set this as a problem to my class, however I'm not even sure I can solve it! I have used an IF statement to determine wether a delivery should go out or not. But I'm stuck because I'm not sure I can make the spreadsheet deduct 550 everytime the IF statement says its time for a delivery.

Can anybody help please? I'm starting to wonder if I have come up with an impossible task!

​

 

[miss_ell]

Hello,

I've gotten it to work in the following way. First I create a table with columns A-H with the following headings and formulas:

A = Date
B = Day of the week (not needed for calculations; just to show clearly the day)
C = Units brought forward
D = Units produced
E = Cumulative total of units produced
F = Units delivered
G = Units carried forward
H = Delivery time


Formulas, starting from A1 etc, copied down:

A: (as an example, I've listed the dates from 1-31 March, all the way down to A32)
B: =A1 (formatted as dddd)
From C2 onwards: =G2
D: 200
E: =SUM(C2:D2)
F: =INT(E2/550)*550*NETWORKDAYS(A2,A2)
G: =E2-F2
H: =IF(G2>550,"Time to deliver!","")

This will result in there being no deliveries on Saturday and Sunday even when the total is over 550, and on the Monday the necessary amount will automatically be deducted from the total.

I hope this helps a bit.


Forgot to add what it should look like:

Date	           Day	Units B/F	Units Produced	Stock Total	Units delivered	Units carried forward	Time to deliver?
01/03/2013	Friday		200	200	0	200	
02/03/2013	Saturday	200	200	400	0	400	
03/03/2013	Sunday	400	200	600	0	600	Time to deliver!
04/03/2013	Monday	600	200	800	550	250	
05/03/2013	Tuesday	250	200	450	0	450	
06/03/2013	Wednesday	450	200	650	550	100	
07/03/2013	Thursday	100	200	300	0	300	
08/03/2013	Friday	300	200	500	0	500	
09/03/2013	Saturday	500	200	700	0	700	Time to deliver!
10/03/2013	Sunday	700	200	900	0	900	Time to deliver!
11/03/2013	Monday	900	200	1100	1100	0

 

[hannahskellam]

Hello,

I've gotten it to work in the following way. First I create a table with columns A-H with the following headings and formulas:

[/code]

Wow this is great thank you!

The formula youve put in F: =INT(E2/550)*550*NETWORKDAYS(A2,A2) is confusing to me, I want to learn more about spreadsheets so can I ask what type of formula/function this is so I can learn more about it?

Thank you very much for this!! xxxx

 

[miss_ell]

Hello,

I'm very happy it works for you. The formula works as follows:-

=INT(E2/550) takes the total units produced divided by 550 and rounds the figure into the nearest whole number, to determine how many deliveries are needed. For example, on 04/03/2013 there are 800 units. 800 divided by 550 gives 1.45454545... so the INT function returns 1 (delivery). Therefore 1 x 550 units are delivered, with 250 remaining.

=NETWORKDAYS(A2,A2) checks the date to see if it's a weekend, when there is no delivery. For example, on 09/03/2013 there are 700 units but none are delivered because it's a Saturday. NETWORKDAYS calculates the number of weekdays between two dates, so if it's a Saturday the answer is zero, so..

700/550 x 0 = 0 units delivered.

I hope this helps.