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 =,4980)Total Cost Month 2 =,5730)Total Cost Month 3 =Total Cost Month 4 =Total Cost Month 5 =,3430)Total Cost Month 6 =Total Cost All Periods =A/A/A/A/A/A/A,26410)Answer Format: No Dollar (S) signs or commas --- Answers should be whole numbers.Hint: Range (4880Hint: Range (5670Hint: Range (3370Hint: Range (25410 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 180 units per month (Cost per Unit = $20). Overtimeis limited to a maximum of 20 units per month (Cost per Unit =$30). Units purchased from the subcontractor (Cost perUnit = $36) cannot exceed 40 per month and the total purchases from the subcontractor over the 6 month periodcannot be over 160 units. Backorders cannot exceed 40 units in any given month (Cost per Unit = $1 ) and must be nomore than 10 in Period 6. Average Inventory Holding cost per Unit = $4. Forecasted Demand as well as Beginning anddesired Ending Inventory are listed in the table below.MonthRegular OutputOvertime OutputSubcontractBeginning InventoryTotal Available for SaleLess ForecastPlus Backlog-Current PeriodLess Backlog-PreviousPeriodEnding InventoryAverage Inventory022012102310318013051506TotalRequired: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.

Find the Calculation methods below: Output Forecast = (Regular Output - Forecast) + Overtime +…

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

Refer to the data in Problem 6, If backorders are allowed, Costs Productiondetermine the total cost of a plan if overtime is limited to Regular Time $ 40.00 per unit Regular 60040 units per month and subcontracting is limited to Overtime $ 60.00 per unit Overtime 4020 units per month. Backordering costs are $30 per unit. Subcontracting $ 80.00 per unit Subcontracting 20Holding $ 10.00 per unit per monthBack Order $ 30.00 per unitMonth Forecast1 5702 6003 6304 6505 6706 690Totals
You are selling a new line of T-shirts on the boardwalk. The selling price will be $25 per shirt. The labor cost is $5 per shirt. The administrative costs of operating the company are estimated to be $60,000 annually, and the sales and marketing expenses are $20,000 a year. Additionally, the cost of materials will be $10 per shirt. What is the break-even quantity? Multiple Choice 2,000 shirts 3,200 shirts 5,334 shirts 8,000 shirts 16,000 shirts
The SCOR model consists of ________. Group of answer choices Bill, deliver, make, plan, source and return Coordinate, deliver, enable, make, source and return Deliver, enable, make, plan, source and return Deliver, finance, make, plan, source and return
Please help me solve this table. Rockit Electronics manufactures a line of digital blue ray players. The player is made from one subassembly of B and one subassembly of C. B is made of three units of D and two of E. C is made of two units of F and two of E.The blue ray has a lead time of one week. B, C, and E have lead times of two weeks, and D and F have lead times of three weeks.As the production manager, you need to figure out the MRP planning schedule. 1. What is the bill of materials (product structure tree)?2. If 1,000 units of blue ray players are required in week 10, what is the MRP schedule? Specifically, when items are to be ordered and received. Assume we have no units of inventory on hand.
Hello. I need help with this Order-up-to policy question for homework. I'm stuck! You play a Retailer in the Beer Game. You receive orders directly from the Customer and place orders with the Wholesaler. When you place an order, it takes 2 weeks for the order to be received by the Wholesaler and 2 weeks for the shipment from the Wholesaler to arrive to your warehouse. One period is one week in the game. You are charged $0.5 for each unit of inventory per week, and $1.00 for each unit of backlog per week. It has been estimated that the Customer weekly demand follows a normal distribution with a mean 50,000 cases and a standard deviation 10,000 cases. What should be the order-up-to level to minimize holding and backorder costs? What safety stock should you keep? Assume that the Wholesaler keeps enough inventory to cover your demand and delivers the beer after the lead time with no uncertainty.
Please do not give solution in image formate thanku. Develop a production plan and calculate the annual cost for a firm whose demand forecast is fall, 10,000; winter, 8,000; spring, 7,000; summer, 12,000. Inventory at the beginning of fall is 500 units. At the beginning of fall, you currently have 30 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 if overtime is necessary to prevent stock outs at the end of those quarters. Overtime is not available during the fall. Relevant costs are hiring, $100 for each temp; layoff, $200 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.
A company wants to develop a level production plan. The beginning inventory is zero. Demand for the next four periods is given in what follows. What production rate per period will give a zero inventory at the end of period 4? When and in what quantities will back orders occur? What level production rate per period will avoid back orders? What will be the ending inventory in period 4? Period 1 2 3 4 Total Forecast demand 9 5 9 9 Planned production Planned inventory 0
I need help with everything. I don't know what I am doing 1. What is the best decision according to the Laplace criteria? A. Order 130 dozen B. Do nothing C. Order 60 dozen D. Order 25 doze 2. Droid Costume for Halloween Scenario Please do the computations to find the best alternative under the Minimax Regret criteria. What is the maximum regret if 25 dozen are ordered? 3. Droid Costume Scenario What is the maximum regret if 60 dozen are ordered? 4. Droid Costume for Halloween Scenario What is the maximum regret if 130 dozen are ordered?
The El Dorado Star is the only newspaper in El Dorado, New Mexico. Certainly, the Star competes with The Wall Street Journal, USA Today, and the New York Times for national news reporting, but the Star offers readers stories of local interest, such as local news, weather, high-school sporting events, and so on. The El Dorado Star faces the revenue and cost schedules shown in the spreadsheet that follows: A template for the spreadsheet is provided in the Course Materials. You may download my template or create your own. Since we are using dollars and cents, be sure to go out two decimal places on your calculations. Add columns to show, respectively, marginal cost (MC), marginal revenue (MR), and total profit. 1. Dell Computer Corp., the world's largest personal-computer maker, is keenly aware of everything its rival PC manufacturers decide to do. Explain why Dell usually reacts more quickly and more substantially to pricing, product design, and advertising decisions made by…
. MBPF Inc. manufactures prefabricated garages. The manufacturing facility purchases sheet metal that is formed and assembled into finished products—garages. Each garage needs a roof and a base, and both components are punched out of separate metal sheets prior to assembly. Production and demand data for the past eight weeks are shown in Table below. Observe that both production and demand vary from week to week. Now, track the inventory at the end of each week, measured in number of finished garages. Also, construct the inventory build-up diagram. MBPF Inc. manufactures prefabricated garages. The manufacturing facility purchases sheet metal that is formed and assembled into finished products—garages. Each garage needs a roof and a base, and both components are punched out of separate metal sheets prior to assembly. Production and demand data for the past eight weeks are shown in Table below. Observe that both production and demand vary from week to week. Now, track the inventory at…
Table below provides the demand forecast and production day information for an aggregate plan. Assume that no back orders are allowed in this case. Month January February March April May June Demand Forecast 3,000 4,500 3,400 2,500 4,000 3,200 Production Days 22 18 21 21 22 20 The costing information that is used to evaluate the total cost of each plan is as follows:• Inventory carrying cost: $4 per unit per month• Subcontracting cost per unit: $15 per unit• Working hours: 8 hours per day• Average pay rate: $12 per hour ($96 per day per worker)• Labour-hours to produce a unit: 2 hours per unit• Cost of increasing daily production rate (hiring and training): $300 per unit• Cost of decreasing daily production rate (layoffs): $700 per unitUse these information to formulate an aggregate plan. The firm would like to use level strategy: constant workforce for the aggregate planning. The firm chooses the average demand of the…
You create your own start-up company that supply bowl directly to a number of customers. You receive a number of tentative orders and you now have to tell your customers which orders you are going to take. Before embarking on this journey, you first want to allocate your production capabilities in order to devise a feasible daily production plan that maximizes your profit.There are only three different grades of bowls that you order at this early stage of the company: Bowl #1, Bowl #2 and Bowl #3.Each type of bowl has to be processed, packaged and delivered. Each operation is run by yourself. Since your company only operates for 8 hours per day, the total number of available bowl processing hours is 4, the total number of packaging hours is 2, and the total number of delivery hours is 2.Processing 10 units of Bowl #1 requires 1 hour of time, packaging is done at the rate of 20 units per hour, and delivery at the rate of 30 units per hour. The cost of the raw materials for 1 unit is…

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