Required:Find the Minimum Cost Production Plan by Creating a Spreadsheet in Excel. Use Solver to find the Minimum CostSolution. Leave a copy of your Spreadsheet in the DropBox.Total Cost Month 1 =,6670)Total Cost Month 2 =,8890)Total Cost Month 3 =Total Cost Month 4 =Total Cost Month 5=,5260)Total Cost Month 6=AAAAAAA/Total Cost All Periods =,40490)Answer Format: No Dollar (S) signs or commas- Answers should be whole numbers.Hint: Range (6550Hint: Range (8820Hint: Range (5220Hint: Range (3929C Aggregate PlanningGiven the projected demands for the next six months, prepare an aggregate plan that uses inventory, regular time,overtime, subcontract and backorders. Regular time is limited to 170 units per month (Cost per Unit-$30). Overtimeis limited to a maximum of 20 units per month (Cost per Unit -$45). Units purchased from the subcontractor (Cost perUnit = $54) cannot exceed 50 per month and the total purchases from the subcontractor over the 6 month periodcannot be over 150 units. Backorders cannot exceed 40 units in any given month (Cost per Unit - $5) and must be nomore than 30 in Period 6. Average Inventory Holding cost per Unit - $10. Forecasted Demand as well as Beginningand desired Ending Inventory are listed in the table below.=Regular OutputOvertime OutputMonthSubcontractBeginning InventoryTotal Available for SaleLess ForecastPlus Backlog-Current PeriodLess Backlog-PreviousPeriodEnding InventoryAverage Inventory102101210233101704160617006Total

Answered: as or commas A/ A/ << < A 2 A A Answers…

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...

See similar textbooks

Similar questions

WSS company makes weatherproof surveillance systems for parking lots. Demand estimates for the next four quarters are 25, 9, 13, and 17 units. Prepare an aggregate plan that uses inventory, regular time, and over time, and backorders. Subcontracting is not allowed. The regular time capacity is 15 units for quarters 1 and 2, 18 units for quarters 3 and 4. Overtime capacity is 3 units per quarter. The regular time cost is $2000 per unit, while the overtime cost is $3000 per unit. Backorder costs $300 per unit per quarter; inventory holding costs $100 per unit per quarter. The beginning inventory is zero. How many total units will be produced in quarter 1 for delivery in quarter 1? How many units in total will be used to fill back orders over the four quarters? What is the cost to produce one unit in Quarter 4 using overtime to fill a back order in quarter one?
WSS company makes weatherproof surveillance systems for parking lots. Demand estimates for the next four quarters are 25, 9, 13, and 17 units. Prepare an aggregate plan that uses inventory, regular time and overtime and back orders. Subcontracting is not allowed. Regular time capacity is 15 units for quarters 1 and 2, 18 units for quarters 3 and 4. Overtime capacity is 3 units per quarter. Regular time cost is $2000 per unit, while overtime cost is $3000 per unit. Back order cost is $300 per unit per quarter; inventory holding cost is $100 per unit per quarter. Beginning inventory is zero. Questions At the end of quarter 3, what is the ending inventory of finished systems? Answer: No ending Inventory =0 What is the total cost? 15*2000 + 6*2300 +4*2600 + 9*2000 + 13*2000 + 17*2000 =$132, 200 What is the average cost per unit? Answer 132200 / 64 = $2,066.
Prepare a master schedule based on the following information: Week 1 2 3 4 5 6 7 8 Forecast 500 500 600 600 750 750 900 800 Orders 540 470 325 200 105 45 10 0 Currently there are 700 units in inventory. Policy calls for a fixed order quantity of 1250 units. Can you receive another order for 250 items in week 1? Why? How many orders can be received in each period without altering the plan. If there are 1250 items in stock, what would be the answer of questions 2 and 3?
Given the projected demands for the next six months, prepare aggregate plan must wind up with no units in ending inventory in Period 6. Regular time capacity is 150 units per month. Overtime cost is $20 per unit, backorder cost is $30 per unit, inventory holding cost is $10 per unit based on maximum, subcontracting cost is $40 per unit, regular time cost of $20 per unit, and beginning inventory is 10. Month 1 2 3 45 6 Forecast 150 170 140 160 130 160 a. Prepare an aggregate plan with inventory and backlog allowed. Overtime and subcontracting are not allowed. b. Prepare an aggregate plan if the management decided to switch to chase strategy. They only allow backlog if absolutely needed. Regular production capacity is now 160.
Planners for a company that makes several models of skateboards are about to prepare the aggregate plan that will cover six periods. They have assembled the following information. Period 1 2 3 4 5 6 Total Forecast 200 200 300 400 500 200 1,800 Costs Output Regular time=$2 per skateboard Overtime =$3 per skateboard Subcontract =$6 per skateboard Inventory =$1 per skateboard per period on average inventory Back orders =$5 per skateboard per period They now want to evaluate a plan that calls for a steady rate of regular-time output, mainly using inventory to absorb the uneven demand but allowing some backlog. Prepare an aggregate plan and determine its cost using the preceding information.
Plan production for a four-month period: February through May. For February and March, you should produce to exact demand forecast. For April and May, you should use overtime and Inventory with a stable workforce; stable means that the number of workers needed for March will be held constant through May. However, government constraints put a maximum of 5,000 hours of overtime labor per month in April and May (zero overtime in February and March). If demand exceeds supply, then backorders occur. There are 100 workers on January 31. You are given the following demand forecast: February, 80,640; March, 67,200; April, 100,280; May, 40,280. Productivity is four units per worker hour, eight hours per day, 21 days per month. Assume zero Inventory on February 1. Costs are: hiring, $50 per new worker, layoff, $70 per worker laid off, Inventory holding, $11 per unit-month; regular time labor, $12 per hour; overtime, $18 per hour; backorder, $22 per unit. Develop a production plan and calculate…
Plan production for a four-month period: February through May. For February and March, you should produce to exact demand forecast. For April and May, you should use overtime and inventory with a stable workforce; stable means that the number of workers needed for March will be held constant through May. However, government constraints put a maximum of 5,000 hours of overtime labor per month in April and May (zero overtime in February and March). If demand exceeds supply, then backorders occur. There are 100 workers on January 31. You are given the following demand forecast: February, 90,000; March 65,000; April 110,000; May, 55,000. Productivity is four units per worker hour, eight hours per day, 20 days per month. Assume zero inventory on February 1. Costs are hiring, $50 per new worker; layoff, $70 per worker laid off; inventory holding, $10 per unit-month; straight-time labor, $10 per hour; overtime, $15 per hour; backorder, $20 per unit a. Find the total cost of this plan?
A local machine shop employs 60 workers who have a variety of skills. The shopaccepts one-time orders and also maintains a number of regular clients. Discuss someof the difficulties with using the aggregate planning methodology in this context.
The president of Hill Enterprises, Terri Hill, projects the firm's aggregate demand requirements over the next 8 months as follows: 2,100 2,200 January February March April 5 6 Her operations manager is considering a new plan, which begins in January with 200 units on hand and ends with zero inventory Stockout cost of lost sales is $100 per unit. Inventory holding cost is $20 per unit per month. Ignore any idle-time costs. The plan is called plan B. Period Month Demand 0 December January 1 2 February 3 March 4 April May June 7 Plan B: Produce at a constant rate of 1,400 units per month, which will meet minimum demands. Then use subcontracting, with additional units at a premium price of $75 per unit. Subcontracting capacity is limited to 800 units per month. Evaluate this plan by computing the costs for January through August in order to arrive at the costs, first compute the ending inventory and subcontracting units for each month by filling in the table below (enter your responses as…
In production plan 2, vary inventory if the ending inventory for March is -32, what will be the beginning inventory for the month of April?
Given the projected demands for the next six months, prepare an aggregate plan that uses inventory, regular time and overtime, and backorders. The plan must wind up with no units in ending inventory in Period 6. Regular time capacity is 160 units per month. There are 10 units backlogged from before. Overtime cost is $30 per unit, backorder cost is $20 per unit, inventory holding cost is $5 per unit, regular time cost of $20 per unit, and beginning inventory is zero. Month Forecast 1 180 2 170 3 140 4 150 5 130 6 150 a. Prepare an aggregate plan with inventory and backlog allowed. Overtime and subcontracting are not allowed.b. Prepare an aggregate plan if the management decided to switch to…
Create a level plan with a zero ending inventory for the forecast shown in the table. There is no beginning inventory and regular production capacity is 300 units. Overtime costs K15 extra and is limited to 25 units per month and subcontracting is limited to 60 units per month and costs K10 per unit. Back orders cost KS0 per unit and there is a cost of KS per month to hold a unit in inventory. What is the total plan cost? |Month January February March Forecast 250 300 400 |April 350

SEE MORE QUESTIONS

Recommended textbooks for you

Practical Management Science

Operations Management

ISBN:

9781337406659

Author:

WINSTON, Wayne L.

Publisher:

Cengage,

Operations Management

ISBN:

9781259667473

Author:

William J Stevenson

Publisher:

McGraw-Hill Education

Operations and Supply Chain Management (Mcgraw-hi…

Operations Management

ISBN:

9781259666100

Author:

F. Robert Jacobs, Richard B Chase

Publisher:

McGraw-Hill Education

Practical Management Science

Operations Management

ISBN:

9781337406659

Author:

WINSTON, Wayne L.

Publisher:

Cengage,

Operations Management

ISBN:

9781259667473

Author:

William J Stevenson

Publisher:

McGraw-Hill Education

Operations and Supply Chain Management (Mcgraw-hi…

Operations Management

ISBN:

9781259666100

Author:

F. Robert Jacobs, Richard B Chase

Publisher:

McGraw-Hill Education

Business in Action

Operations Management

ISBN:

9780135198100

Author:

BOVEE

Publisher:

PEARSON CO

Purchasing and Supply Chain Management

Operations Management

ISBN:

9781285869681

Author:

Robert M. Monczka, Robert B. Handfield, Larry C. Giunipero, James L. Patterson

Publisher:

Cengage Learning

Production and Operations Analysis, Seventh Editi…

Operations Management

ISBN:

9781478623069

Author:

Steven Nahmias, Tava Lennon Olsen

Publisher:

Waveland Press, Inc.

SEE MORE TEXTBOOKS

as or commas A/ A/ << < A 2 A A Answers should be whole numbers. Hi Hi Hi