Question: The ABC Company is making an aggregate plan for the next year (four quarters) using Excel Solver. Demand forecasts are known, and assume 75 working

The ABC Company is making an aggregate plan for

The ABC Company is making an aggregate plan for the next year (four quarters) using Excel Solver. Demand forecasts are known, and assume 75 working days per quarter. The company plans to keep 50 workers constantly over the next year. Each worker produces up to 50 units per quarter and earns $3,000 per quarter. The company estimates its inventory carrying cost to be $10 per unit of ending inventory per quarter and its backorder cost to be $50 per unit of ending backorder per quarter. The company has partially set up the spreadsheet as shown in the picture below with decision Variables and costs derned. The company is how to define the production capacity and Inventory balance cons ? SOP Example_LP_Exam.xlsx - Excel REVIEW VIEW - OX Sign in FILE HOME INSERT PAGE LAYOUT FORMULAS DATA Arial D EWrap Text General - 10-AA === > A = = = Paste B T U . Merce Center 0% R 09 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells A1 Aggregate Production Planning Requiremer Quarter 1 2200 Quarter 2 1800 Quarter 3 2500 Quarter 4 2000 Demand Forecast Number of Working Days by Quarter Number of Workers 50 3000 Labor Cost ($ per quarter per worker) Inventory Holding Cost (s per unit per quarter) Backorder Cost (per unit per quarter) Production Rate (units per quarter per worker) 50 50 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Decisions Production Quantity Ending Inventory Quantity Ending Backorder Quantity Total =SUM(D18:G20) Costs Labor Cost Inventory Holding Cost Backorder Cost Quarter 1 =D5 $D$7 =D14*$D$8 =D15*$D$9 Quarter 2 =E5 SDS7 =E14*$D$8 =E15*SDS9 Quarter 3 =F5 SDS7 =F14*$D$8 F15*$D$9 Quarter 4 =G5 SDS7 =G14*$D$8 =G15*SD$9 Production Capacity Quarter 1 =D13 Quarter 2 =E13 Quarter 3 =F13 Quarter 4 =G13 A = = = Paste B T U . Merce Center 0% R 09 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells A1 Aggregate Production Planning Requiremer Quarter 1 2200 Quarter 2 1800 Quarter 3 2500 Quarter 4 2000 Demand Forecast Number of Working Days by Quarter Number of Workers 50 3000 Labor Cost ($ per quarter per worker) Inventory Holding Cost (s per unit per quarter) Backorder Cost (per unit per quarter) Production Rate (units per quarter per worker) 50 50 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Decisions Production Quantity Ending Inventory Quantity Ending Backorder Quantity Total =SUM(D18:G20) Costs Labor Cost Inventory Holding Cost Backorder Cost Quarter 1 =D5 $D$7 =D14*$D$8 =D15*$D$9 Quarter 2 =E5 SDS7 =E14*$D$8 =E15*SDS9 Quarter 3 =F5 SDS7 =F14*$D$8 F15*$D$9 Quarter 4 =G5 SDS7 =G14*$D$8 =G15*SD$9 Production Capacity Quarter 1 =D13 Quarter 2 =E13 Quarter 3 =F13 Quarter 4 =G13

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!

Big Rock Brewery currently rents a bottling machine for $50,000 per year, including all maintenance expenses. The company is considering purchasing a machine instead and is comparing two options: a....

You are asked to make a sales and operation plan for the next year using a level strategy. The demand forecasts for the next four quarters are 9 , 0 0 0 , 1 3 , 0 0 0 , 1 2 , 0 0 0 , and 1 4 , 0 0 0...

You are asked to make a sales and operation plan for the next year using a level strategy. The demand forecasts for the next four quarters are given in the table below. The production rate is 4 units...

The owner of a small mill-working plant that builds cabinets is developing his aggregate plan for the next year. The relevant cost data and forecast for the next 4 quarters is provided below. The...

P301 - Problem 7 The owner of a small mill-working plant that builds cabinets is developing his aggregate plan for the next year. The relevant cost data and forecast for the next 4 quarters is...

The owner of a small mill-working plant that builds cabinets is developing his aggregate plan for the next year. The relevant cost data and forecast for the next 4 quarters is provided below. The...

A local company makes athletic clothing and they are preparing aggregate production plans on a quarterly basis for the coming year for their line of women's wear. They have the following information...

answer all (a) M 8.3 Raw Materials Decision - Local Purchase vs Other Purchase X Ltd has two factories, one at Lucknow and another at Pune producing 7,200 Tonnes and 10,800 Tonnes of a product...

The owner of a small mill-working plant that builds cabinets is developing his aggregate plan for the next year. The relevant cost data and forecast for the next 4 quarters is provided below. The...

Prepare an aggregate plan for the coming year, assuming that the sales forecast is perfect. Use the work- sheet "Bradford Manufacturing" that is in the "19 Sales and Operations Planning" spreadsheet....

/3 S (cscx - secx)(sin x + cos x)dx T/6

Constants | Periodic Table Element X reacts with element Y to give a product containing X+ ions and Y- ions. You may want to reference (Pages 73-94) Chapter 3 while completing this problem. Part A...

What are the implications for investing if the capital market is semi - strong - form efficient? A . It is impossible to use technical analysis to consistently earn an abnormal return. B . It is...

Consider a large hospital and small physician group who are considering merging practices (vertical integration). Vertical integration in this setting may potentially be beneficial if the...