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

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
Should BestLine’s ordering department have been more involved during the project?2. Sarah used expert interviews for this project. Were there more effective methods for gathering therequirements?3. What crucial step(s) did Sarah skip in the requirements process?4. Which downstream activities would likely be affected by her approach? At what point in the projectwould the problems likely emerge?5. Should Sarah have presented the requirements to the ordering manager at this point? Should the orderingmanager have signed off on them?
A single inventory item is ordered from an outside supplier. The anticipateddemand for this item over the next 12 months is 6, 12, 4, 8, 15, 25, 20, 5, 10, 20, 5,12. Current inventory of this item is 4, and ending inventory should be 8. Assume aholding cost of $1 per period and a setup cost of $40. Determine the order policyfor this item based onc. Part period balancing
Please help me with show all calculation thanku
How does Material Requirement Planning help organizations balance inventory carrying costs with the risk of stockouts and production delays?
Snip
Costco also employs a just-in-time inventory management system, which includes sharing data directly with many of its largest suppliers. Companies like Kimberly-Clark calculate re-order points in real time and send new inventory, as needed, to replenish store shelves. Costco also works to redesign product packaging to squeeze more bulky goods onto trucks and shelves, reducing the number of orders Costco needs to place with suppliers.Occasionally, the company leverages its 75 million square feet of warehouse space to reduce purchasing costs. For example, when Procter & Gamble recently announced a 6% price increase for its paper goods, Costco bought 258 truckloads of paper towels at the old rate and stored them using available capacity in its distribution centers and warehouses.These inventory management techniques have allowed Costco to succeed in tough times while others have failed. Costco turns its inventory nearly 12 times a year, far more often than other retailers. With many…
The president of Hill Enterprises, Terri Hill, projects the firm's aggregate demand requirements over the next 8 months as follows: January 1,400 May February 1,500 June 2,200 2,200 March April 1,800 1,700 July August 1,700 1,500 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. 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 $80 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 whole numbers). Period…
A stock or s+ Туpe Chase Production Plan: Example • Manager T. C. Downs of Plum Engines, a producer of lawn mowers and leaf blowers, must develop an aggregate plan given the forecast for engine demand shown in the table. The department has a regular output capacity of 130 engines per month. Regular output has a cost of $60 per engine. The beginning inventory is zero engines. Overtime has a cost of $90 per engine. • Develop a chase plan that matches the forecast and compute the total cost of your plan. Regular production can be less than regular capacity. 3. 4. 7 8. MONTH 1 140 120 125 125 140 135 Forecast 120 135 Chase Production Plan Calculation
ABC COMPANY uses graphical techniques in its aggregate planning efforts. Over the next 12 months (its intermediate period), it estimates the sum of demands to be 300,000 units. The firm has 300 production days per year. In February, which has 20 production days, demand is estimated to be 20,000 units. A graph of demand versus level production will show that: O O level production is approximately 900 units per month. level production of 1000 units per day is above the February requirement. the firm must hire workers between January and June. the February requirement is equal level production of 1000 units. level production is approximately 800 units per day.
evelop a production plan and calculate the annual cost for a firm whose demand forecast is fall, 9,700; winter, 8,000; spring, 7,000; summer, 11,700. Inventory at the beginning of fall is 485 units. At the beginning of fall you currently have 35 workers, but you plan to hire temporary workers at the beginning of summer and lay them off at the end of summer. In addition, you have negotiated with the union an option to use the regular workforce on overtime during winter or spring only if overtime is necessary to prevent stockouts at the end of those quarters. Overtime is not available during the fall. Relevant costs are hiring, $80 for each temp; layoff, $160 for each worker laid off; inventory holding, $5 per unit-quarter; backorder, $10 per unit; straight time, $5 per hour; overtime, $8 per hour. Assume that the productivity is 0.5 unit per worker hour, with eight hours per day and 60 days per season. In each quarter, produce to the full output of your regular workforce, even if that…

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