Question 3A Company produces five products using two machines, two raw materials and labour.The profit per unit for each product, availability of each resource and the requirementof each resource for each product are as below Excel sheet. In addition, each producthas a maximum market demand. The company does not wish to exceed this for anyproduct. The objective is to maximise profit subject to availability of resources andmarket demands.A12 Prod 13 Prod 24 Prod 35 Prod 46 Prod 578B15. 38461530. 769231910 Prod 111 Prod 212 Prod 313 Prod 414 Prod 515161718 Total Profit19 2838. 46150200100CDMax Market Profit/unit1005050200100Machine Machine 20.30.50.40.50.40.80.60.90.50.4$6.00$8.00$5.00$9.00$7.00EFMaterial 1 Material 2 Labour0.50.70.40.80.20.90.30.60.40.5Machine160 KMachine 170.76923<Material 1120<Material 2 205.38462Labour137. 692310.60.60.5G0.40.3HIAvail160400120280300 (1) Formulate the problem in Excel Solver. Present your solver screen.(2) Generate and present your Sensitivity report.(3) As a factory manager, generate a report focus on resource usage and availability.(4) Consider the market demand and pricing, generate a report of profitability.

Answered: Image /qna-images/answer/ec9d3cf1-59f8-4714-ad49-2b68d08bede7.jpg

Answered: A Company produces five products using…

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

Market Company’s policy is to have 30% of the next month's sales on hand at the end of the current month. Projected sales for August, September, and October are 26,000 units, 21,000 units, and 31,000 units, respectively. How many units must be purchased in September?
The following information relates to the performance of a manager in 2020. Benchmark weight Return on the benchmark Actual returnActual Weight Equity Bonds Cash 7.28% 0.6 0.5 6.00% 3.20% 0.90% 3.60% 0.90% 0.2 0.3 0.2 0.2 Details of investments in different sectors of the equity market are as follows: Sector Beginning of month weights S&P 500 Sector return Basic material 1.96 6.30 6.9 Business services 5.80 7.10 7.0 Capital goods 1.91 8.80 4.1 Consumer cyclical 8.47 12.50 8.8 Consumer non-cyclical 40.37 19.40 10.0 Credit sensitive 24.01 20.80 5.0 Energy Technology 13.53 13.20 2.6 3.95 11.90 0.6
Develop a production schedule to produce the exact production requirements by varying the workforce size for the following problem. Also, evaluate the cost of the schedule. The monthly forecasts for Product X for January, February, and March are 1,000, 1,500, and 1,200, respectively. Safety stock policy recommends that half of the forecast for that month be defined as safety stock. There are 22 working days in January, 19 in February, and 21 in March. Beginning inventory is 500 units. Manufacturing cost is $200 per unit, storage cost is $3 per unit per month (based on expected end-of-month levels), standard pay rate is $6 per hour, overtime rate is $9 per hour, cost of stock-out is $10 per unit per month, marginal cost of subcontracting is $10 per unit, hiring and training cost is $200 per worker, layoff cost is $300 per worker, and worker productivity is 0.1 unit per hour. Assume that you start off with 50 workers and that they work 8 hours per day. Note: Leave the cells blank,…
Buskin’s Donuts can make donuts for $0.25 each with their current equipment. They could purchase a different fryer for $12,000 which would reduce their costs to $0.20 each. Or, they could buy a bigger fryer for $42,000 and decrease their cost to $0.17 each. Find the break-even points and graph your solution. Over what output range would each option be preferred?
You work for the Brad's Nailer Company which manufactures two types of nailers: a pneumatic model and a cordless model. You have contracted to supply a national retail chain with both pneumatic nailers and cordless nailers. However, Brad's nailer production capability is limited in three departments: production, testing, and packaging. Your answer will be the production hours needed to execute a production plan that minimizes costs - rounded to the nearest whole hour. (The number of nailers may not be a whole number, which is acceptable in this chapter.) Use Scenario 2 Pneum. Cost Pneum. Prod Hrs Pneum. Test Hrs Pneum. Pkg Hrs Pneum. Sales Cordless Cost Cordless Prod Hrs Cordless Test Hrs Cordless Pkg Hrs Cordless Sales Prod Hrs max Testing Hrs max Pkg Hrs max Scenario 1 Scenario 2 Scenario 3 52 0.2 0.3 0.1 22,000 67 0.3 0.4 0.1 15,500 10,000 15,000 4,800 55.50 0.4 0.3 0.1 24,500 69.75 0.3 0.5 0.1 15,500 20,000 18,000 4,200 38.50 0.25 0.4 0.1 18,000 44 0.4 0.4 0.2 14,000 20,000 18,000…
The investment cost of the fruit juice business was determined as 3,200,000 TL. The depreciation rate of the enterprise is determined as 10%. Fruit juice and concentrate are produced in equal amounts at the facility. The business operates 300 days a year and 12 hours a day. The maintenance and repair expenses of the enterprise are determined as 65,000 TL/year, the unexpected expenses are 36.000 TL/year, the fixed tax total is 82.000 TL/year, and the total personnel salaries are 300.000 TL/year. Fruit juice production cost was calculated as 2.6 TL/kg product and concentrate production cost as 6.8 TL/kg product. Fruit dry matter rate was 16%, fruit juice dry matter rate was 6%, and concentrated dry matter rate was 72%. Since the raw material price is 3.5 TL/kg of raw material, fruit juice and concentrate sales prices are 5.8 TL/kg and 32 TL/kg, what is the minimum raw material amount that must be processed per hour for the business to make a profit?
A production plant has the capacity to produce 2,000 tons per year. At full capacity, there are total variable costs of $2,000,000 and fixed costs of $700,000. Solve, a. What is the annual profit for the plant when working at full capacity (2,000 tons) and the product sells for $0.80 per pound? b. What is the fixed cost per pound at the breakeven point? c. What is the total cost per pound at full capacity?
Fitness Express sells three different types of elliptical machines. They have manufactured 50 of each machine for a special sale. The store already ha orders for 46 of machine A and 16 of machine B. Use the following information to determine how many of machine C the store needs to sell to break even? Show your work on a separate sheet of paper. Manufacturing Cost $105.00 $140.00 $175.00 Elliptical Machine Machine A Machine B Machine C Total costs of all machines: $ Total revenue: $ Number of machine Cs to sell to break even: Retail Cost $250.00 $305.00 $420.00
It is summertime and your winter coat factory is trying to decide its production schedule and staffing level for the next season. You are forecasting a demand of 5000 coats in October, 8000 coats in November, 8000 coats in December, 12000 coats in January, 12000 coats in February, and 9000 coats in March. You want to manufacture these coats at the lowest cost possible/ The first decision you need to make is how many people to hire. Each employee will cost $2500 a month. You don’t want to hire or fire any employees mid-season in order to minimize disruptions in your factory floor. You can have employees idle if there isn’t sufficient work for them to do. Employees still need to be paid even if they are idle. An employee works 20 business days a month and takes half a day to produce one coat. The employees can also be asked to work overtime, in which case you need to pay them $70 per coat they produce. You don’t need to assume there is any capacity constraint on the number of overtime…
Тасо Loco Taco Loco is considering a new addition to their menu. They have test marketed a number of possibilities and narrowed them down to three new products, X, Y, and Z. Each of these products is made from a different combination of beef, beans, and cheese, and each product has a price point. Taco Loco feels they can sell an X for $17, a Y for $13, and a Z for $14. The company's management science consultant formulates the following linear programming model for company management. Max R = 14Z+ 13Y + 17X %3D subject to: Beef 2Z + 3Y + 4X0 The sensitivity report from the computer model reads as follows:
A publisher has a fixed cost of $250,000 associated with the production of a college mathematics book. The contribution to profit and fixed cost from the sale of each book is $6.25. (a) Determine the number of books which must be sold in order to break even. (b) What is the expected profit if 50,000 books are sold?
Amalgamated Fenderdenter’s sales are $10 million. The company spends $3.5 million for purchase of direct materials and $2.5 million for direct labor; overhead is $3.5 million and profit is $500,000. Direct labor and direct material vary directly with the cost of goods sold, but overhead does not. The company wants to double its profit

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