Question

ne 5500 per 40 CHAPTER TWO wlucts. The the unit and out what will the beneveld in publem What of the plant developed in 20 als to plant in question s Talent to plant in question 3. -5000 ay wants to sele c tion plan for a family of product nie inventory is 100 units and an increase to expected by the of the pan. The demand for each month is given in what follows Calculate the action daily production and production and ending inventory for each expected by the end Calculate the total inventory for each month Aug Total May Month 20 10 19 21 Working Days 125 140 Forecast Demand Planned Production Planned Inventory 100 Answer. Total monthly production for May = 168 units The ending inventory for May = 153 units 2.12. A company wants to develop a level production plan for a family of products. The opening inventory is 500 units, and a decrease to 250 units is expected by the end of the plan. The demand for each of the months is given in what follows. How much should the company produce each month? What will be the ending inventory in each month? Do you see any problems with the plan? Jun Month Jan Total Apr May Feb Mar 20 Working Days 18 22 19 Forecast Demand 1200 1300 800700 700 900 Planned Production 500 Planned Inventory 2.13. Because of its labor contract, a company must hire enough labor for 100 units of production per week on one shift or 200 units per week on two shifts. It cannot hire, lay off, or assign overtime. During the fourth week, workers will be available from another department to work part or all of an extra shift (up to 100 units). There is a planned shutdown for maintenance in the second week, which will cut production to half. Develop a production plan. The opening inventory is 200 units, and the desired ending inventory is 300 units. Week 5 Total Forecast Demand 120 160 240 240 160 160 Planned Production Planned Inventory

how to solve 2.11 and 2.12?

Answer 1

2.11.

Below will be calculation to find the level production quantity for each month -

Total Working Days = 70 (sum of all working days)

Total Forecasted Demand = Sum of all forecast demand = 530

Beginning Inventory = 100

Ending Inventory = 130

Level Production Quantity = (Total Forecasted Demand + Ending Inventory - Beginning Inventory)*Number of Working Day of respective month/Total Working Days

For example,

Level Production Quantity for May = (530+130-100)*21/70 = 168 units.

I have used the same logic to find the solution for both questions in excel.

Below is the screenshot of the formula applied -

Book5 - Excel Abhishek Rail - ESSE File Home Insert Page Layout Formulas Data Review View Help * ? Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Function - Used - - Time Reference Trigº Functions Manager Function Library fx A .l le - e Calculate Now Tell me what you want to do Define Name Ft Use in Formula Create from Selection Defined Names - Trace Precedents 5 Show Formulas Trace Dependents ! Error Checking Remove Arrows - Evaluate Formula Formula Auditing Watch Window Calculation Calculate Sheet Options Calculation A4 X fic Forecast Demand F Jun Jul Aug Total 20 2 Month 3 Working Days 4 Forecast Demand 1 5 Planned Production 6 Planned Inventroy May 21 115 -($G$4-$B$6+130)/70'C3 100 =MAX(C5-C4+B6,0) I 125 = ($G$4-$B$6+130)/70'D3 -MAX(D5-D4+C60) -SUM(C3:F3) -SUM(C4:F4) -SUM(C5:F5) | = ($G$4-$B$6-130)/70'E3 =MAX(E5-E4+D6,0) = ($G$4-$B$6+130)/70"F3 =MAX(F5-F4+E6,0) 22 20 Total SUMC11:H11 - SUM(C12:H12) 18 700 Feb Jan Mar Apr May Jun 20 20 1200 1300 700 -ROUND ($I$12-$B$14+250)/$1$11'C11,0) =ROUND ($1$12-$B$14.250) $1$11'D11,0 -ROUND ($1$12-$B$14-250)$I$11'11,0) =ROUND{{$I$12-$B$14+250)|$1$11'F11,0) =ROUND($I$12-$B$14-250)/$1$11G11,0) =ROUND ($I$12-$B$14-250)/$1$11 H11,0 500 EC13-C12-B14 -D13-D12C14 =E13-E12-D14 =F13-F12+E14 =G13-G12-F14 =H13-H12 G14 Backorders Backorders 800 10 Month 11 Working Daus 12 Forecast Demand 13 Planned Production 14 Planned Inventroy 15 900

Below is the desired production plan -

Book5 - Excel Abhishek R ESSE File Home Insert Page Layout Formulas Data Review View Help * ? Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Function - Used - - - Time Reference Trigº Functions Manager Function Library fx A .l le - Tell me what you want to do Define Name Ft Use in Formula Create from Selection Defined Names - Trace Precedents 5 Show Formulas Trace Dependents ! Error Checking Remove Arrows - Evaluate Formula Formula Auditing Watch Window Calculati Options Working Days A May Aug Total 21 2 Month 3 Working Days 4 Forecast Demand 5 Planned Production 6 Planned Inventroy 125 150 115 168 153 140 160 530 560 152 180 100 200 130 Jan Feb Mar Apr May Jun 22 20 20 19 Total 119 5600 700 10 Month 11 Working Days 12 Forecast Demand 13 Planned Production 14 Planned Inventroy 15 1200 899 199 1300 989 -112 800 899 -13 700 899 186 809 900 854 249 500 295 Rackorders Rackorders

In question 2.12, from above it is clear that there will be case of back orders will arise in Feb & Mar. To avoid this, we will have to increase the quantity of level production but that will also increase the ending inventory.