Question

Assume that Product Z is made up of one unit of A and five units of B. A is made of three units of C and four units of D. D is made of two units of E. The demand for Z is 500 units in Week 8 and 500 units in Week 10. For Z, A, C, and D, there are no units on hand, no safety stock, and no units allocated. Item E has 900 units on hand, with a safety stock of 300 units and no units allocated. Item B has 400 units on hand, 200 units of safety stock and 200 units already allocated. There are 200 units of Item C currently scheduled to arrive in Week 1 and 200 units of item D are scheduled to arrive in Week 1.

Week	6	7	8	9	10
Demand	--	--	500	--	500

Additional information is the following:

                                   

Item	Z	A	B	C	D	E
On hand	--	--	400	--	--	900
Safety stock	--	--	200	--	--	300
Allocated	--	--	200	--	--	--
Scheduled Receipts	--	--	--	200 in Week 1	200 in Week 1	--

Provide the additional number of all items, including Z, that are required to meet the total demand shown for Product Z. Ignore the timing of these requirements.

please use excel solver if applicable

Answer 1

Product Z	Requires 1 unit of A and 5 units of B which means for 500 units we need 500 units of A &2500 units of B
A unit	Requires 3 unit of C and 4 units of D which means for 500 units of A we will need 1500units (500*3) of C and 2000 (500*4) units of D
D unit	Requires 2 units of E which means for 2000 units of D we would need 4000 units (2000*2)
Qty needed	500	500	2500	1500	2000	4000
Item	Z	A	B	C	D	E
On hand			400			900
Safety stock			200			300
Allocated			200
Scheduled				200 in week1	200 in week1
Actual Qty needed for week8	500	500	1900	1300	1800	2800
Week 10	500	500	2500	1500	2000	4000
Please Note
1	IT is asssumed that the allocated stock is for the product Z itself
2	We would need safety stock in hand hence while calculating the actual stock required that is excluded
3	Actual Qty required is calculated after removing the on hand, allocated and scheduled stock
4	Week 10 it is assumed that there is no in hand , allocated abd scheduled stock