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-,5590)Total Cost Month 2 =,5470)Total Cost Month 3 =Total Cost Month 4 =Total Cost Month 5=,5420)Total Cost Month 6-AAVAAATotal Cost All Periods40180)Answer Format: No Dollar ($) signs or commas- Answers should be whole numbers.Hint: Range (5470Hint: Range (5350Hint: Range (5320Hint: Range (38480 Given 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 150 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 70 per month and the total purchases from the subcontractor over the 6 month periodcannot be over 220 units. Backorders cannot exceed 60 units in any given month (Cost per Unit - $5) and must be nomore than 10 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 OutputSubcontractBeginning InventoryMonthTotal Available for SaleLess ForecastPlus Backlog-Current PeriodLess Backlog-PreviousPeriodEnding InventoryAverage Inventory201901170232701804160190106TotalRequired: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.Acti

Regular time cost per unit = $30Overtime cost per unit = $45Subcontract cost per unit = $54Backorder…

Answered: Given the projected demands for the…

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

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?
Assume you are the manager of a shop that assembles power tools. You have just received an order for 55 chain saws, which are to be shipped at the start of week 8. Pertinent information on the saws follows: Item Lead Time (weeks) On Hand Saw A B C D E F 2 1 2 2 1 1 2 Gross requirements Scheduled receipts Projected on hand Net requirements Planned order receipt Planned order release Ĉ E(3) & E(2) LT = 1 week Lot size: Lot-for-Lot 15 10 5 65 20 10 30 Skip Extension Tip: Double click to open in new tab Show Transcribed Text Develop the material requirements plan for component E using lot-for-lot ordering for all items. (Leave no cells blank - be certain to enter "0" wherever required.) Components A(2), B(1), C(4) E(3), D(1) D (2), F(3) E(2), D (2) Beg. Inv. 1 2 3 190 5 210 6
Discuss the following statement: “Since we use a rolling production schedule, Ireally don’t need to know the demand beyond next month.”
Quantity of a raw material listed in a purchase order counts as demand when you check the "Stock/Requirement List" for the raw material? True or False? Explain
Given the following data, calculate the projected available balance and the plannedMPS receipts. The lot size is 200. The demand time fence is 2 weeks.
Given the bill of material and the on-hand quantities below, compute the net requirements for each item. The gross requirement for A is 150 units. Summarize your solution in a table. Item On-Hand Inventory A 40 B 50 C 100 D 70 E 25
< The president of Hill Enterprises, Terri Hill, projects the firm's aggregate demand requirements over the next 8 months as follows: January February March April 1,400 1,500 1,600 1,800 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 $25 per unit per month. Ignore any idle-time costs. The plan is called plan B. Period Month 0 December 1 January 2 February 3 March 4 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. April May June 7 July 56 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…
The table below contains monthly (six months) demand estimates and working days per month. Month Demand Production Days JAN 1,500 20 FEB 1,200 24 MAR 1,000 25 APR 1,600 25 MAY 1,100 20 JUN 1,300 26 For the implementation of “Level” strategy, the average demand (in units/day) over the six- month planning horizon is equal to; a. 40 b. 50 c. 55 d. 75
An engineering plant has developed the accompanying supply, demand, cost and inventory data. The engineering plant has a constant workforce and meets all its demands. Allocate production capacity to satisfy demand at a minimum cost. What is the cost of this plan? (Assume that back ordering is not a viable alternative for the plant) Demand forecast Period Demand (Unit) 1 650 2 700 3 900 Supply Capacity available (units) Period Regular time Overtime Subcontract 1 350 100 250 2 450 100 250 3 500 100 250 Other Data Initial Inventory 100 units Regular-time cost per unit R 50 Overtime cost per unit R 65 Subcontract cost per unit R 80 Carrying cost per unit per period R 1 Back order cost per unit per period R 4
Jet skis and snowmobiles are assembled by Mobile Incorporated. Because both end-items use the same small engine, you can aggregate demand for the engine assembly. Develop an aggregate plan that uses a level production strategy each quarter and the information that follows. What is the total cost? Assume lost sales are backordered and filled during the next quarter. Summarize the plan, its costs, and consequences using manual computations. Initial Inventory Level = 1,000 units Regular Time = $15 Lost Sales Cost/Unit = $24 Inventory Carry Cost per unit = $3 Quarter 1 2 3 4 Totals Beginning Inventory Jet Ski Engine 10,000 15,000 16,000 3,000 44,000 600 Snowmobile Engine 9,000 7,000 19,000 10,000 45,000 400 Total Engines 19,000 22,000 35,000 13,000 89,000 1,000 Average Demand Rate/Quarter 22,000 units Regular Time Production Rate 22,000 units
Jet skis and snowmobiles are assembled by Mobile Incorporated. Because both end - items use the same small engine, you can aggregate demand for the engine assembly. Develop an aggregate plan that uses a level production strategy each quarter and the information that follows. What is the total cost? Assume lost sales are backordered and filled during the next quarter. Summarize the plan, its costs, and consequences using manual computations. Initial Inventory Level = 1,000 units Regular Time = $15 Lost Sales Cost/Unit = $24 Inventory Carry Cost per unit = $3 Quarter One Two Three Four Total Beginning Inventory Jet Ski Engine 10, 000 15,000 16,000 3,000 44,000 600 Snowmobile Engine 9,000 7,000 19,000 10,000 45,000 400 Total Engines 19,000 22,000 35,000 13,000 89, 000 1,000 Average Demand Rate/Quarter 22, 000 Units Regular Time Production Rate 22,000 Units

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