Sales and Operations Planning

profileashersdad92
boz4_problem_CS_ch10_CS.xlsx
Home>Business & Finance homework help>Operations Management homework help>Sales and Operations Planning

1

SERVICE SERVICE MIX LABOR HOURS PER JOB a.) b.)
Light Cleaning 20% 0.2 0.04 10% 0.02
Medium Cleaning 60% 0.25 0.15 65% 0.16
Deep Cleaning 20% 0.35 0.07 25% 0.09
Weighted Planning Value 0.26 0.27
b.) The weighted planning value increased because a higher percentage of the service mix was in the more labor intense services.

2

GAUGE SET PRODUCT MIX MACHINE HOURS PER UNIT LABOR HOURS PER UNIT a.) b.)
Machine Labor Mix Machine Labor
A20 60% 0.20 0.15 0.12 0.09 45% 0.09 0.07
B30 15% 0.35 0.10 0.05 0.02 30% 0.11 0.03
C40 25% 0.25 0.12 0.06 0.03 25% 0.06 0.03
Weighted Planning Values 0.24 0.14 0.26 0.13
a.) The weighted planning values are the weighted average of the machine hours and labor hours required, based on the product mix.
b.) The weighted planning values changed because of the change to the product mix. The greater percentage of B30 results in more machine hours and less labor hours, on average.

3

Model PRODUCT MIX MACHINE HOURS PER UNIT LABOR HOURS PER UNIT a.) b.)
Machine Labor Mix Machine Labor
Deluxe Models 45% 10.0 5.0 4.50 2.25 30% 3.00 1.50
Regular Models 30% 8.0 4.0 2.40 1.20 30% 2.40 1.20
Economy Models 25% 6.0 3.5 1.50 0.88 40% 2.40 1.40
Weighted Planning Values 8.40 4.33 7.80 4.10
a.) The assumptions that must be made in order to use the weighted per-unit planning values for labor and machine time are that the averages of machine and labor hours are accurate (and there is a small standard deviation from the average) and that the product mix is accurate.
c.) When the product mix changes from month to month, Bangor should use a bottom-up approach to sales and operations planning. Top-down approach should be used only when the product mix remains stable.

4

MONTH SALES FORECAST WORKER HOURS MACHINE HOURS WORKERS NEEDED MACHINES NEEDED
October 44,000 39600 880 248 6
November 52,000 46800 1040 293 7
December 68,000 61200 1360 383 9
January 69,000 62100 1380 388 9
February 58,000 52200 1160 326 7
March 46,000 41400 920 259 6
Average Worker Hours Per Unit 0.9
Average Machine Hours Per Unit 0.02
Hours Available Per Month 160

5

MONTH FORECAST PRODUCT A FORECAST PRODUCT B a.) COMBINED FORECAST b.) TOTAL LABOR HOURS REQUIRED
January 3500 700 4200 10010
February 3300 1000 4300 10050
March 3200 1200 4400 10160
April 3000 1500 4500 10200
May 2700 1900 4600 10170
June 2600 2100 4700 10280
Average Labor Per Product A 2.5
Average Labor Per Product B 1.8
a.) If the combined forecast was the only information available, an increase in resource requirements from January to June would be expected.
b.) Although the total number of products forecasted increases from January to June, the product mix also changes. The result is that the total number of labor hours does not increase as much as would be expected if the only information available was the combined forecast.
c.) Bottom-up planning would be better suited for S&OP in this situation because the product mix changes from month to month. Top-down planning should be used when the product mix is stable.

6

MONTH FORECASTED SALES SALES IN WORKER HOURS WORKERS NEEDED TO MEET SALES AVERAGE = 252 ACTUAL WORKERS ACTUAL PRODUCTION LAYOFFS HIRINGS ENDING INVENTORY
227 1,000
March 1,592 31,840 199 252 2,016 0 25 1,424
April 1,400 28,000 175 252 2,016 0 0 2,040
May 1,200 24,000 150 252 2,016 0 0 2,856
June 1,000 20,000 125 252 2,016 0 0 3,872
July 1,504 30,080 188 252 2,016 0 0 4,384
August 1,992 39,840 249 252 2,016 0 0 4,408
September 2,504 50,080 313 252 2,016 0 0 3,920
October 2,504 50,080 313 252 2,016 0 0 3,432
November 3,000 60,000 375 252 2,016 0 0 2,448
December 3,000 60,000 375 252 2,016 0 0 1,464
January 2,504 50,080 313 252 2,016 0 0 976
February 1,992 39,840 249 252 2,016 0 0 1,000
227 25 0
Totals 24,192 24,192 25 25 32,224
Costs Information: Layoff Hiring Inventory
Hiring: $75,000 Totals: 25 25 32,224
Layoff: $50,000 Costs: $50,000 $75,000 $193,334
Inventory: $193,344 Cost of plan: $318,334
Cost of plan: $318,344 Planning values
Starting inventory: 1000
Starting and ending workforce: 227
Hours worked per month per worker: 160
Hours per unit: 20
Hiring cost per worker: $3,000
Layoff cost per worker: $2,000
Monthly per-unit holding cost: $6

7

MONTH FORECASTED SALES SALES IN WORKER HOURS WORKERS NEEDED TO MEET SALES AVERAGE = 252 ACTUAL WORKERS ACTUAL PRODUCTION LAYOFFS HIRINGS ENDING INVENTORY
227 1,000
March 1,592 31,840 199 199 1,592 28 0 1,000
April 1,400 28,000 175 175 1,400 24 0 1,000
May 1,200 24,000 150 150 1,200 25 0 1,000
June 1,000 20,000 125 125 1,000 25 0 1,000
July 1,504 30,080 188 188 1,504 0 63 1,000
August 1,992 39,840 249 249 1,992 0 61 1,000
September 2,504 50,080 313 313 2,504 0 64 1,000
October 2,504 50,080 313 313 2,504 0 0 1,000
November 3,000 60,000 375 375 3,000 0 62 1,000
December 3,000 60,000 375 375 3,000 0 0 1,000
January 2,504 50,080 313 313 2,504 62 0 1,000
February 1,992 39,840 249 249 1,992 64 0 1,000
227 22 0
Totals 24,192 24,192 250 250 12,000
Costs Information: Layoff Hiring Inventory
Hiring: $750,000 Totals: 250 250 12,000
Layoff: $500,000 Costs: $500,000 $750,000 $72,000
Inventory: $72,000 Cost of plan: $1,322,000
Cost of plan: $1,322,000 Planning values
Starting inventory: 1000
Starting and ending workforce: 227
Hours worked per month per worker: 160
Hours per unit: 20
Hiring cost per worker: $3,000
Layoff cost per worker: $2,000
Monthly per-unit holding cost: $6

8

MONTH FORECASTED SALES SALES IN WORKER HOURS WORKERS NEEDED TO MEET SALES AVERAGE = 252 ACTUAL WORKERS ACTUAL PRODUCTION LAYOFFS HIRINGS ENDING INVENTORY
50 500
March 2,000 8,000 50 47 1,880 3 0 380
April 1,920 7,680 48 47 1,880 0 0 340
May 1,840 7,360 46 47 1,880 0 0 380
June 1,800 7,200 45 47 1,880 0 0 460
July 1,800 7,200 45 47 1,880 0 0 540
August 1,800 7,200 45 47 1,880 0 0 620
September 1,750 7,000 44 36 1,440 11 0 310
October 1,640 6,560 41 36 1,440 0 0 110
50 0 14
Totals 14,550 14,160 14 14 3,140
Information: Layoff Hiring Inventory
Totals: 14 14 3,140
Costs: $2,800 $4,200 $12,560
Cost of plan: $19,560
Planning values
Starting inventory: 500
Starting and ending workforce: 50
Hours worked per month per worker: 160
Hours per unit: 4
Hiring cost per worker: $300
Layoff cost per worker: $200
Monthly per-unit holding cost: $4
This production plan represents a mix strategy as there is a stable production for several months and then the production level changes for the final two months. The strategy is not completely level but it also does not chase the forecasted sales each month.

9

MONTH FORECASTED SALES SALES IN WORKER HOURS WORKERS NEEDED TO MEET SALES AVERAGE = 252 ACTUAL WORKERS ACTUAL PRODUCTION LAYOFFS HIRINGS ENDING INVENTORY
227 1,000
March 1,592 31,840 199 252 2,016 0 25 1,424
April 1,400 28,000 175 252 2,016 0 0 2,040
May 1,200 24,000 150 252 2,016 0 0 2,856
June 1,000 20,000 125 252 2,016 0 0 3,872
July 1,504 30,080 188 252 2,016 0 0 4,384
August 1,992 39,840 249 252 2,016 0 0 4,408
September 2,504 50,080 313 252 2,016 0 0 3,920
October 2,504 50,080 313 252 2,016 0 0 3,432
November 3,000 60,000 375 252 2,016 0 0 2,448
December 3,000 60,000 375 252 2,016 0 0 1,464
January 2,504 50,080 313 252 2,016 0 0 976
February 1,992 39,840 249 252 2,016 0 0 1,000
227 25 0
Totals 24,192 24,192 25 25 32,224
Costs Information: Layoff Hiring Inventory
Hiring: $75,000 Totals: 25 25 32,224
Layoff: $50,000 Costs: $50,000 $75,000 $193,334
Inventory: $193,344 Cost of plan: $318,334
Cost of plan: $318,344 Planning values
Starting inventory: 1000
Starting and ending workforce: 227
Hours worked per month per worker: 160
Hours per unit: 20
Hiring cost per worker: $3,000
Layoff cost per worker: $2,000
Monthly per-unit holding cost: $6
The spreadsheet calculates new results for as the forecasted sales or actual workers values are changed. Besides slight formatting changes, this spreadsheet is the same as the one used to answer questions 6 and 7.

10

Part a.)
MONTH BEEFEATER FORECAST DEUTSCHLANDER FORECAST AGGREGATE FORECAST SALES IN WORKER HOURS WORKERS NEEDED TO MEET SALES AVERAGE = 75 ACTUAL WORKERS ACTUAL PRODUCTION LAYOFFS HIRINGS ENDING INVENTORY
20 0
Nov-16 650 3,048 3,698 11,834 74 75 3,750 0 55 52
Dec-16 676 2,899 3,575 11,440 72 75 3,750 0 0 227
Jan-17 624 3,198 3,822 12,230 76 75 3,750 0 0 155
Feb-17 624 2,671 3,295 10,544 66 75 3,750 0 0 610
Mar-17 696 2,919 3,615 11,568 72 75 3,750 0 0 745
Apr-17 475 3,102 3,577 11,446 72 75 3,750 0 0 918
May-17 566 2,964 3,530 11,296 71 75 3,750 0 0 1,138
Jun-17 819 2,409 3,228 10,330 65 75 3,750 0 0 1,660
Jul-17 754 3,381 4,135 13,232 83 75 3,750 0 0 1,275
Aug-17 982 3,965 4,947 15,830 99 75 3,750 0 0 78
20 55 0
Totals 37,422 37,500 55 55 6,858
Costs Information:
Hiring: $16,500 Starting inventory: 0
Layoff: $16,500 Starting and ending workforce: 20
Inventory: $102,870 Hours worked per month per worker: 160
Production: $11,250,000 Production cost per unit: $300
Cost of plan: $11,385,870 Hours per unit: 3.2
Hiring cost per worker: $300
Layoff cost per worker: $300
Monthly per-unit holding cost: $15
Part b.)
MONTH BEEFEATER FORECAST DEUTSCHLANDER FORECAST AGGREGATE FORECAST SALES IN WORKER HOURS WORKERS NEEDED TO MEET SALES AVERAGE = 75 ACTUAL WORKERS ACTUAL PRODUCTION LAYOFFS HIRINGS ENDING INVENTORY
20 0
Nov-16 650 3,048 3,698 11,834 74 74 3,700 0 54 2
Dec-16 676 2,899 3,575 11,440 72 72 3,600 2 0 27
Jan-17 624 3,198 3,822 12,230 76 76 3,800 0 4 5
Feb-17 624 2,671 3,295 10,544 66 66 3,300 10 0 10
Mar-17 696 2,919 3,615 11,568 72 72 3,600 0 6 -5
Apr-17 475 3,102 3,577 11,446 72 72 3,600 0 0 18
May-17 566 2,964 3,530 11,296 71 71 3,550 1 0 38
Jun-17 819 2,409 3,228 10,330 65 65 3,250 6 0 60
Jul-17 754 3,381 4,135 13,232 83 83 4,150 0 18 75
Aug-17 982 3,965 4,947 15,830 99 99 4,950 0 16 78
20 79 0
Totals 37,422 37,500 98 98 308
Costs Information:
Hiring: $29,400 Starting inventory: 0
Layoff: $29,400 Starting and ending workforce: 20
Inventory: $4,620 Hours worked per month per worker: 160
Production: $11,250,000 Production cost per unit: $300
Cost of plan: $11,313,420 Hours per unit: 3.2
Hiring cost per worker: $300
Layoff cost per worker: $300
Monthly per-unit holding cost: $15
Part c.)
If hiring and layoff costs increased dramatically the level plan would be less expensive than the chase plan. This is because of the difference between the number of hirings and layoffs in the two plans. The chase plan is much more sensitive to changes in the hiring and layoff costs while the level plan is more sensitive to changes in inventory costs.

11

MONTH SALES FORECAST REGULAR PRODUCTION OVERTIME PRODUCTION INVENTORY / BACKORDERS CASH INFLOWS CASH OUTFLOWS NET FLOW CUMULATIVE NET FLOW
100
January 750 840 0 190 $2,100,000 $1,687,600 $412,400 $412,400
February 760 840 0 270 $2,128,000 $1,690,800 $437,200 $849,600
March 800 840 0 310 $2,240,000 $1,692,400 $547,600 $1,397,200
April 800 840 0 350 $2,240,000 $1,694,000 $546,000 $1,943,200
May 820 840 0 370 $2,296,000 $1,694,800 $601,200 $2,544,400
June 840 840 0 370 $2,352,000 $1,694,800 $657,200 $3,201,600
July 910 840 0 300 $2,548,000 $1,692,000 $856,000 $4,057,600
August 910 840 0 230 $2,548,000 $1,689,200 $858,800 $4,916,400
September 910 840 0 160 $2,548,000 $1,686,400 $861,600 $5,778,000
October 880 840 0 120 $2,464,000 $1,684,800 $779,200 $6,557,200
November 860 840 0 100 $2,408,000 $1,684,000 $724,000 $7,281,200
December 840 840 0 100 $2,352,000 $1,684,000 $668,000 $7,949,200
Information:
Cash inflow per cabinet: $2,800
Regular production outflow: $2,000
Overtime production outflow: $0
Monthly inventory holding cost: $40
When comparing this option to the mix strategy shown in Table 10.10, finance would likely select the mix strategy over the level strategy shown here. The mix strategy accumulates a higher net cash flow and does so quicker during the year than the level plan.

12

Part a.)
MONTH SALES FORECAST REGULAR PRODUCTION OVERTIME PRODUCTION ENDING INVENTORY CASH INFLOWS CASH OUTFLOWS NET FLOW CUMULATIVE NET FLOW
0
January 800 1150 0 350 $400,000 $406,000 ($6,000) ($6,000)
February 1000 1150 0 500 $500,000 $407,500 $92,500 $86,500
March 1200 1150 0 450 $600,000 $407,000 $193,000 $279,500
April 1400 1150 0 200 $700,000 $404,500 $295,500 $575,000
May 1600 1150 150 0 $800,000 $470,000 $330,000 $905,000
June 1500 1150 350 0 $750,000 $560,000 $190,000 $1,095,000
Information:
Cash inflow per cabinet: $500
Regular production outflow: $350
Overtime production outflow: $450
Monthly inventory holding cost: $10
b.) The net cash flows for April and May are much higher than the other months because the number of units sold is much higher than the number of units produced. The implications of building up and draining down inventory are that the production costs will remain stable, although inventory costs may be high.

13

S&OP Spreadsheet
Labor hrs. per unit: 20
Worker hrs. per month: 160
Beginning & ending workforce: 100
Beginning & ending inventory: 100
Total plan cost
Production cost per unit: $550.00 $6,600,000
Hiring cost: $300.00 $37,500
Layoff cost: $200.00 $25,000
Holding cost per unit per month: $4.00 $4,800
$6,667,300 Grand total
Month Sales Forecast Sales (in labor hrs.) Sales (in workers) Actual Workers Actual Production Hirings Layoffs Ending Inventory / Back Orders
100 100
January 500 10,000 62.5 62.50 500.00 0.00 37.50 100.00
February 600 12,000 75.0 75.00 600.00 12.50 0.00 100.00
March 700 14,000 87.5 87.50 700.00 12.50 0.00 100.00
April 800 16,000 100.0 100.00 800.00 12.50 0.00 100.00
May 900 18,000 112.5 112.50 900.00 12.50 0.00 100.00
June 1,000 20,000 125.0 125.00 1,000.00 12.50 0.00 100.00
July 1,000 20,000 125.0 125.00 1,000.00 0.00 0.00 100.00
August 1,100 22,000 137.5 137.50 1,100.00 12.50 0.00 100.00
September 1,200 24,000 150.0 150.00 1,200.00 12.50 0.00 100.00
October 1,300 26,000 162.5 162.50 1,300.00 12.50 0.00 100.00
November 1,400 28,000 175.0 175.00 1,400.00 12.50 0.00 100.00
December 1,500 30,000 187.5 187.50 1,500.00 12.50 0.00 100.00
100 0.00 87.50
Totals: 12,000 12,000.00 125.00 125.00 1,200.00
Average: 125

14

Sales & Operations Planning Spreadsheet for Kumquats Unlimited
(with solver optimization)
Production cost per batch: $2,400 Production costs: $14,448,000
Line hours per batch: 16 Line start-up costs: $300,000
Production line hours per month: 320 hours Line shutdown costs: $72,000
Cost to start up a line: $25,000 Inventory holding costs: $144,000
Cost to shut down a line: $6,000
Inventory holding cost: $300 per batch, per month Grand total: $14,964,000
Beginning and ending lines: 55 production lines
Beginning and ending inventory: 100 batches
Month Sales Forecast Sales (in line hours) Sales (in production lines) Actual Production Lines Actual Production Production Line Start-ups Production Line Shutdowns Ending Inventory
55 100
January 1,000 16,000 50 55.00 1,100 0 0 200
February 1,200 19,200 60 55.00 1,100 0 0 100
March 1,200 19,200 60 55.00 1,100 0 0 0
April 1,000 16,000 50 50.00 1,000 0 5 0
May 800 12,800 40 43.00 860 0 7 60
June 800 12,800 40 43.00 860 0 0 120
55 12 0
Total = 6,000 6,020 12 12 480
Average = 50

CaseStudy

MONTH SALES FORECAST SALES IN WORKER HOURS WORKERS NEEDED TO MEET SALES AVERAGE = 49 ACTUAL WORKERS ACTUAL PRODUCTION LAYOFFS HIRINGS ENDING INVENTORY
50 10,000
September-17 30,000 7,500 47 49 31,360 1 0 11,360
October-17 31,500 7,875 49 49 31,360 0 0 11,220
November-17 35,000 8,750 55 49 31,360 0 0 7,580
December-17 37,000 9,250 58 49 31,360 0 0 1,940
January-18 22,000 5,500 34 49 31,360 0 0 11,300
February-18 18,000 4,500 28 49 31,360 0 0 24,660
March-18 17,500 4,375 27 49 31,360 0 0 38,520
April-18 27,000 6,750 42 49 31,360 0 0 42,880
May-18 38,000 9,500 59 49 31,360 0 0 36,240
June-18 40,000 10,000 63 49 31,360 0 0 27,600
July-18 42,000 10,500 66 49 31,360 0 0 16,960
August-18 40,000 10,000 63 49 31,360 0 0 8,320
50 0 1
Totals 378,000 376,320 1 1 238,580
Costs Information:
Hiring: $1,250 Starting inventory: 10,000
Layoff: $500 Starting and ending workforce: 50
Inventory: $1,908,640 Hours worked per month per worker: 160
Production: $28,035,840 Production cost per unit: $74.50
Cost of plan: $29,946,230 Hours per unit: 0.25
Hiring cost per worker: $1,250
Layoff cost per worker: $500
Monthly per-unit holding cost: $8
Plant capacity: 35,000
Question 1.) Advantages to a level production plan are that the hiring and layoff costs are very low and the company is able to meet overall sales forecast without expanding current plant capacity. Covolo could not implement a pure chase plan because capacity is currently 35,000 per month. If sales continue to grow, Covolo will need to consider how to add additional production capacity.
Question 2.) Monthly S&OP updates will rolling planning horizons will help alleviate Patricia's concerns because there will be an opportunity to adjust the sales forecast every month. Significant advantages to S&OP, even though forecasts may change, include bringing marketing, operations, and finance together to agree upon the plan for the coming months and how to best handle changes in the forecast.
Question 3.) I agree with David Griffin *if* the cost of retaining an employee while not producing is less or equal to the cost of a layoff plus a new hire. Holding costs of an existing employee include their salary and benefits paid, while producing an extra gauge results in materials and inventory costs. If workers are idle, the production cost per unit will increase to distribute the cost of the employee over fewer units.