How to do this using excel solver? 1 2 3 4 5 6 7 8 9 10
Fantastic news! We've Found the answer you've been seeking!
Question:
How to do this using excel solver?
Transcribed Image Text:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 20 Inputs Exercise 1 - Resource Allocation Resources Needed Per Unit of Product Resource Product 1 Product 2 Product 3 Product 4 Plastic (ounces) 6 6 6 6 3 4 4 3 2 1 1 1 Product 1 8 1.5 2 Product 2 1 2 Product 3 Product 4 $ 10 $ 11 $ 9 Electrical components Wiring (hours) Assembly (hours) Revenue (per unit) $ Plastic (ounces) Electrical components Wiring (hours) Assembly (hours) Available Resources 250 120 80 75 Your company makes four different products (Products 1, 2, 3 and 4). Each unit of product requires the following resources of each type: Resources Needed Per Unit of Product Resources Product 1 Product 2 Product 3 Product 4 Plastic (ounces) 66 66 Electrical components 3 4 4 3 Wiring (hours) 11.5 21 Assembly (hours) 1212 In addition, each product generates the following amount of revenue (per unit). Product 1 Product 2 Product 3 Product 4 $ 8 $ 10 $ 11 $ 9 Lastly, you have the following amount of each resource available: Resource Available Resources Plastic (ounces) 250 Electrical components 120 Wiring (hours) 80 Assembly (hours) 75 (Step 1) Formulate and solve an optimization model in Excel. Be sure the Solver dialog box is populated and the correct (optimal answer) is displayed when you submit your file. (Step 2) In a cell or textbox in the worksheet containing this problem, indicate HOW MANY of each product the company should make AND what the maximum revenue will be. (Step 3) In another cell or textbox in the worksheet containing this problem, indicate which resources are "scarce" meaning they are preventing you from making more products and therefore more revenue. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 20 Inputs Exercise 1 - Resource Allocation Resources Needed Per Unit of Product Resource Product 1 Product 2 Product 3 Product 4 Plastic (ounces) 6 6 6 6 3 4 4 3 2 1 1 1 Product 1 8 1.5 2 Product 2 1 2 Product 3 Product 4 $ 10 $ 11 $ 9 Electrical components Wiring (hours) Assembly (hours) Revenue (per unit) $ Plastic (ounces) Electrical components Wiring (hours) Assembly (hours) Available Resources 250 120 80 75 Your company makes four different products (Products 1, 2, 3 and 4). Each unit of product requires the following resources of each type: Resources Needed Per Unit of Product Resources Product 1 Product 2 Product 3 Product 4 Plastic (ounces) 66 66 Electrical components 3 4 4 3 Wiring (hours) 11.5 21 Assembly (hours) 1212 In addition, each product generates the following amount of revenue (per unit). Product 1 Product 2 Product 3 Product 4 $ 8 $ 10 $ 11 $ 9 Lastly, you have the following amount of each resource available: Resource Available Resources Plastic (ounces) 250 Electrical components 120 Wiring (hours) 80 Assembly (hours) 75 (Step 1) Formulate and solve an optimization model in Excel. Be sure the Solver dialog box is populated and the correct (optimal answer) is displayed when you submit your file. (Step 2) In a cell or textbox in the worksheet containing this problem, indicate HOW MANY of each product the company should make AND what the maximum revenue will be. (Step 3) In another cell or textbox in the worksheet containing this problem, indicate which resources are "scarce" meaning they are preventing you from making more products and therefore more revenue.
Expert Answer:
Related Book For
Economics
ISBN: 978-0073375694
18th edition
Authors: Campbell R. McConnell, Stanley L. Brue, Sean M. Flynn
Posted Date:
Students also viewed these economics questions
-
Determine CPI, ETC (1), and EAC. Activity Total PV 1 2 3 4 5 6 7 8 9 10 11 12 13 14 1 6 6 2 20 10 10 3 30 9 6 6 6 3 4 20 8 2 5 5 5 16 4 4 4 4 6 18 9 9 7 8 4 4 Status Report: Ending Period 5 Task %...
-
Each of the four independent situations below describes a lease requiring annual lease payments of $10,000. For each situation, determine the appropriate lease classification by the lessee. (FV of...
-
(1) Given A and i. (1a) Find the equivalent amount at the end of year 1. (1b) Find the equilvalent amount at the end of year B. (2) Given X and i, Find the annual equivalent A from year S to 8. *...
-
Factor completely. 3(r + 5) - 11(r + 5) 4
-
ABC Ltd. was in a contract to supply 1,000 widgets @$1.00 each to XYZ Ltd by a specified date. Due to a mechanical failure at its factory, ABC Ltd. cannot fill the order on time and has advised XYZ...
-
What does it mean when we say that the confidence interval methods of this section are robust against departures from normality? Refer to the accompanying screen display that results from the Verizon...
-
Helgeson Enterprises, Corp., completed the following selected transactions and prepared these adjusting entries during January: 2. Compute January net income or net loss under the accrual basis of...
-
Build a House of Quality (showing only the Voice of the customer, Technical features, Interrelationships, and Relationship matrix from Exhibit 6.2) for designing and producing chocolate chip cookies....
-
The mean of a test is 80 and the standard deviation is 25. It is normally distributed. -Approximately what score is the first quartile for the test? -Approximately what score is the first quartile...
-
Start with the partial model in the file Ch21 P08 Build a Model.xlsx on the textbook's Web site. Kasperov Corporation has an unlevered cost of equity of 12% and is taxed at a 40% rate. The 4-year...
-
Select a product that you can purchase at a grocery store or a discount retailer and respond to the following: Identify the factors that would impact the demand for the product that you have...
-
What sensors are typically used to commutate six-step motors? Why are these sensors not used for sine-wave commutation?
-
An individual demand curve for pizza can be derived with the following: a. prices of pizza, one other good, and income b. price of pizza and two other goods c. income d. price of pizza
-
Which of the following requires that expenses should be matched and recognized in the same period as the related revenue? a. Cost principle b. Going concern assumption c. Matching principle d....
-
Suppose you are a manager and you have three potential capital investment projects from which to choose. Funds are limited, so you can only choose one of the three projects. Describe at least three...
-
Regarding periodic reporting, which of the following statements is not correct? a. Frequent periodic reporting is helpful for providing timely information; b. Typical fiscal year starts at January 1...
-
How are business plans evaluated? Who can legitimately evaluate business plans?What other plans or analyses are formulated when developing a business plan?What are the benefits of a business plan?
-
Revol Industries manufactures plastic bottles for the food industry. On average, Revol pays $76 per ton for its plastics. Revol's waste-disposal company has increased its waste-disposal charge to $57...
-
What are the four supply factors of economic growth? What is the demand factor? What is the efficiency factor? Illustrate these factors in terms of the production possibilities curve.
-
Assume a monopolistic publisher has agreed to pay an author 15 percent of the total revenue from the sales of a text. Will the author and the publisher want to charge the same price for the text?...
-
What is the estimated size of the union wage advantage? How might this advantage diminish the efficiency with which labor resources are allocated in the economy? Normally, labor resources of equal...
-
A hydrocarbon \(\mathrm{H}_{2}\left(\mathrm{CH}_{2} ight)_{n}\) is dissolved in a solvent \(S\) which freezes at \(9.0^{\circ} \mathrm{C}\). A solution which contains \(0.90 \mathrm{~g}\) of...
-
Estimate the depression in the freezing point if \(58.5 \mathrm{~g}\) of \(\mathrm{NaCl}\) is added to \(1 \mathrm{~L}\) of water at atmospheric pressure.
-
Calculate the mass of methyl alcohol which, when dissolved in \(100 \mathrm{~g}\) of water, would just prevent the formation of ice at \(-10^{\circ} \mathrm{C}\), given that \(K_{\mathrm{f}}\) is...
Study smarter with the SolutionInn App