In: Accounting
create a semiannual production plan for the Apple iPhone 12 product using notional demand and inventory data. The initial production should be based on the market estimates of what is intended to be sold and produce. The production plan should be supported a market forecast.
Prompt: The plan can be submitted in a basic tabular (spreadsheet) format, and must include the following;
1. Create a semiannual production plan using notional demand and inventory
2. Estimate of labor hours consumed
3. Estimate the number of worker requirements considering a standard work week, current inventory levels, receipts of new inventory during each month, and varying demand levels for each month of production
The productiong plan will start with forecasted sales and the considering opeening and closing inventory we will get production unit. For requirement of person, we will get total work week divided by 4 with assumption that i a month there are 4 wroking week.
Following table where i have included sample data. On sales i have assumed 10% increase month on month
Particulars | Months | |||||
1 | 2 | 3 | 4 | 5 | 6 | |
Forecasted sales Unit (a) | 1,000.00 | 1,100.00 | 1,210.00 | 1,331.00 | 1,464.10 | 1,610.51 |
Opening Inventory (b) | 100.00 | 50.00 | 50.00 | 50.00 | 50.00 | 50.00 |
Closing Inventory (Minimum Inventory level) (c ) | 50.00 | 50.00 | 50.00 | 50.00 | 50.00 | 50.00 |
Units to be Manufactured (d=(a-b+c) | 1,050.00 | 1,100.00 | 1,210.00 | 1,331.00 | 1,464.10 | 1,610.51 |
Standard Labour hour required per unit (e ) | 10.00 | 10.00 | 10.00 | 10.00 | 10.00 | 10.00 |
Estimated Total Labour hours required (f=d*e) | 10,500.00 | 11,000.00 | 12,100.00 | 13,310.00 | 14,641.00 | 16,105.10 |
Standard hours in week (g) | 40.00 | 40.00 | 40.00 | 40.00 | 40.00 | 40.00 |
Number of work week required (h=g/e) | 262.50 | 275.00 | 302.50 | 332.75 | 366.03 | 402.63 |
Number of worker required (i=h/4) | 65.63 | 68.75 | 75.63 | 83.19 | 91.51 | 100.66 |
Following table with excel formula :
Particulars | Months | |||||
1 | 2 | 3 | 4 | 5 | 6 | |
Forecasted sales Unit (a) | 1000 | =B3*110% | =C3*110% | =D3*110% | =E3*110% | =F3*110% |
Opening Inventory (b) | 100 | =B5 | =C5 | =D5 | =E5 | =F5 |
Closing Inventory (Minimum Inventory level) (c ) | 50 | =B5 | =C5 | =D5 | =E5 | =F5 |
Units to be Manufactured (d=(a-b+c) | =B3+B4-B5 | =C3+C4-C5 | =D3+D4-D5 | =E3+E4-E5 | =F3+F4-F5 | =G3+G4-G5 |
Standard Labour hour required per unit (e ) | 10 | 10 | 10 | 10 | 10 | 10 |
Estimated Total Labour hours required (f=d*e) | =B6*B7 | =C6*C7 | =D6*D7 | =E6*E7 | =F6*F7 | =G6*G7 |
Standard hours in week (g) | 40 | 40 | 40 | 40 | 40 | 40 |
Number of work week required (h=g/e) | =B8/B9 | =C8/C9 | =D8/D9 | =E8/E9 | =F8/F9 | =G8/G9 |
Number of worker required (i=h/4) | =B10/4 | =C10/4 | =D10/4 | =E10/4 | =F10/4 | =G10/4 |