Question

Please complete all work in excel. Use excel to make the calculations (cells can be clicked on to view any formulas used) and be sure to identify your answer, including units.

A firm has 82 units of a product on hand. Forecasts of demand are 30 units per week. An MPS quantity of 100 units is planned to arrive in period 3. Customer orders are 32 for period 1, 28 for period 2, and 25 for period 3. What is the projected on-hand inventory at the end of period 3?

Answer 1

Projected-on-hand inventory for end of period 3= Projected-on-hand inventory for end of period 2+MPS quantity-customer orders for period 2 or forecast of demand for period 2 (whichever is higher)

Initially firm has 82 units. Customer order is 32 and forecasts of demand are 30. So we will deduct 32 out of 82 to get projected on hand inventory for end of period 1. And similarly for future periods as follows:

Inventory on hand for end of period 1= 82-32=50.

Projected-on-hand inventory for end of period 2= 50-3=20

Projected-on-hand inventory for end of period 3=20+100-30=90

Projected on-hand inventory at the end of period 3 is 90

Please give an Up-vote, this will be quite encouraging for me, thank you! If you have any doubt, kindly comment. I would love to help you!!!