3. [15 points] After finding your optimal solution, at the bottom of the 'Model' worksheet, perform...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
3. [15 points] After finding your optimal solution, at the bottom of the 'Model' worksheet, perform the following additional work so that you can compare your results with controller's information presented in the table above: Allocate the two Fixed Costs proportionally across the four products based on the optimal production quantities and total quantity (not original quantities). Calculate the new Net Profit for each of the four products, as well as the Total Net Profit. Calculate the new Net Profit Per Pound for each of the four products. ● ● 4. [30 points total, 5 points each, on the 'Answers' worksheet]. Answer the following questions. Your answers to parts a-e should be derived from the 'Sensitivity Report'. Be explicit and precise with quantities and numerical impacts. Clearly label. a) If you could decrease the production of any product, which one? How much would each pound decrease change the Objective Function? By how many pounds would you decrease production? b) If you could increase the production of any product, which one? How much would each pound increase change the Objective Function? By how many pounds would you increase production? c) If you could acquire more of any raw material, which one would you acquire, how much would you acquire, and what is the value of these additional units of this raw material? d) If you could add more time to any of the machines, to which machine would you add time, how much time would you add, and what is the value of this additional time? e) Management is considering raising the price of the Whole product by $0.25. Will this change the current optimal solution? f) Based on the optimal solution and your responses above, would your answer to the question in Task 1 be different now? Why or why not? 2. [50 points] Build, and solve, your Linear Optimization model on a worksheet labeled 'Model'. Create the Sensitivity Report for the optimal solution (make sure the worksheet is labeled 'Sensitivity Report'). Three hints on building the model: Given Allocated Fixed Costs are assigned based on the proportion of pounds of each product compared to the total pounds produced, you should not consider these in your Optimization/Solver model since the values will change based on the optimal solution. Instead, your optimization model should be based only on the other values, which are constant regardless of production quantities. This is value is often called the Contribution (equal to Revenue minus Variable Costs). ● • Therefore, you cannot use Profit directly in the model. You need to perform some calculations to find key coefficient values for use in the model. However, for the sake of clarity, these calculations should NOT appear on your Optimization model spreadsheet but can be included on a separate worksheet within your workbook (labeled 'Calculations') and you can then copy/paste the values or use references in your model back to these calculations, but make sure the coefficients are shown on your 'Model' sheet. Make sure to include any upper or lower limit constraints in a manner that facilitates comprehensive analysis via the Sensitivity Report (as discussed in ● Controller's Report: Sales Revenue Variable Costs Direct Materials • Direct Labor . • Manufacturing Selling & Administrative Allocated Fixed Costs Manufacturing Overhead S&A Overhead Net Profit Pounds Sold Net Profit Per Pound Tasks: Whole Cluster Crunch Roasted Total $5,125.00 $1,800.00 $500.00 $930.00 $8,355.00 $560.00 $122.50 $320.00 $2,332.50 $1,330.00 $1,081.50 $400.00 $95.00 $130.00 $1,706.50 $330.00 $140.00 $45.00 $90.00 $605.00 $550.00 $180.00 $65.00 $120.00 $915.00 $659.72 $347.22 $104.17 $138.89 $554.17 $291.67 $87.50 $116.67 $620 950 ($119) ($19) 150 ($0.24) ($0.13) $0.07 500 $0.65 $14 200 $1,250 $1,050 $496 1800 $0.28 Perform the tasks outlined in the order provided below. You should have a total of four worksheets in your workbook. One 'Calculations' worksheet, one 'Model' worksheet, one 'SensitivityReport' worksheet, and one 'Answers' on which you will answer questions for Tasks 1 & 4. 1. [5 points, on the 'Answers' worksheet] Before building the Linear Optimization model, do you agree with the controller's recommendation to cease production of the Cluster and Crunch products? Why or why not? Hawaiian Nut Company The Hawaiian Nut Company (HNC) makes four different products from macadamia nuts grown in the Hawaiian Islands: chocolate-coated whole nuts (Whole), chocolate-coated nut clusters (Clusters), chocolate-coated nut crunch bars (Crunch), and plain roasted nuts (Roasted). The company is barely able to keep up with demand for these products. However, increasing raw materials prices and foreign competition are forcing HNC to watch its margins to ensure it is operating as efficiently as possible. To meet marketing demands for the coming week, HNC must produce at least 900 pounds of the Whole product, between 200 and 500 pounds of the Cluster product, no more than 150 pounds of the Crunch product, and no more than 250 pounds of the Roasted product. Each pound of the Whole, Cluster, Crunch, and Roasted product contains, respectively, 60%, 40%, 20% and 100% macadamia nuts with the remaining weight made up of chocolate coating. The company has 1,100 pounds of macadamia nuts and 800 pounds of chocolate available for use next week. The various products are made using four different machines that hull the nuts, roast the nuts, coat the nuts in chocolate (if needed), and package the product. The following table summarizes the time required by each product on each machine. Each machine has 60 hours of time available in the coming week. Machine. Hulling Roasting Coating Packaging Whole 1.00 2.00 1.00 2.50 Minutes Required Per Pound Cluster 1.00 1.50 0.70 1.60 Crunch 1.00 1.00 0.20 1.25 Roasted 1.00 1.75 0.00 1.00 3. [15 points] After finding your optimal solution, at the bottom of the 'Model' worksheet, perform the following additional work so that you can compare your results with controller's information presented in the table above: Allocate the two Fixed Costs proportionally across the four products based on the optimal production quantities and total quantity (not original quantities). Calculate the new Net Profit for each of the four products, as well as the Total Net Profit. Calculate the new Net Profit Per Pound for each of the four products. ● ● 4. [30 points total, 5 points each, on the 'Answers' worksheet]. Answer the following questions. Your answers to parts a-e should be derived from the 'Sensitivity Report'. Be explicit and precise with quantities and numerical impacts. Clearly label. a) If you could decrease the production of any product, which one? How much would each pound decrease change the Objective Function? By how many pounds would you decrease production? b) If you could increase the production of any product, which one? How much would each pound increase change the Objective Function? By how many pounds would you increase production? c) If you could acquire more of any raw material, which one would you acquire, how much would you acquire, and what is the value of these additional units of this raw material? d) If you could add more time to any of the machines, to which machine would you add time, how much time would you add, and what is the value of this additional time? e) Management is considering raising the price of the Whole product by $0.25. Will this change the current optimal solution? f) Based on the optimal solution and your responses above, would your answer to the question in Task 1 be different now? Why or why not? 2. [50 points] Build, and solve, your Linear Optimization model on a worksheet labeled 'Model'. Create the Sensitivity Report for the optimal solution (make sure the worksheet is labeled 'Sensitivity Report'). Three hints on building the model: Given Allocated Fixed Costs are assigned based on the proportion of pounds of each product compared to the total pounds produced, you should not consider these in your Optimization/Solver model since the values will change based on the optimal solution. Instead, your optimization model should be based only on the other values, which are constant regardless of production quantities. This is value is often called the Contribution (equal to Revenue minus Variable Costs). ● • Therefore, you cannot use Profit directly in the model. You need to perform some calculations to find key coefficient values for use in the model. However, for the sake of clarity, these calculations should NOT appear on your Optimization model spreadsheet but can be included on a separate worksheet within your workbook (labeled 'Calculations') and you can then copy/paste the values or use references in your model back to these calculations, but make sure the coefficients are shown on your 'Model' sheet. Make sure to include any upper or lower limit constraints in a manner that facilitates comprehensive analysis via the Sensitivity Report (as discussed in ● Controller's Report: Sales Revenue Variable Costs Direct Materials • Direct Labor . • Manufacturing Selling & Administrative Allocated Fixed Costs Manufacturing Overhead S&A Overhead Net Profit Pounds Sold Net Profit Per Pound Tasks: Whole Cluster Crunch Roasted Total $5,125.00 $1,800.00 $500.00 $930.00 $8,355.00 $560.00 $122.50 $320.00 $2,332.50 $1,330.00 $1,081.50 $400.00 $95.00 $130.00 $1,706.50 $330.00 $140.00 $45.00 $90.00 $605.00 $550.00 $180.00 $65.00 $120.00 $915.00 $659.72 $347.22 $104.17 $138.89 $554.17 $291.67 $87.50 $116.67 $620 950 ($119) ($19) 150 ($0.24) ($0.13) $0.07 500 $0.65 $14 200 $1,250 $1,050 $496 1800 $0.28 Perform the tasks outlined in the order provided below. You should have a total of four worksheets in your workbook. One 'Calculations' worksheet, one 'Model' worksheet, one 'SensitivityReport' worksheet, and one 'Answers' on which you will answer questions for Tasks 1 & 4. 1. [5 points, on the 'Answers' worksheet] Before building the Linear Optimization model, do you agree with the controller's recommendation to cease production of the Cluster and Crunch products? Why or why not? Hawaiian Nut Company The Hawaiian Nut Company (HNC) makes four different products from macadamia nuts grown in the Hawaiian Islands: chocolate-coated whole nuts (Whole), chocolate-coated nut clusters (Clusters), chocolate-coated nut crunch bars (Crunch), and plain roasted nuts (Roasted). The company is barely able to keep up with demand for these products. However, increasing raw materials prices and foreign competition are forcing HNC to watch its margins to ensure it is operating as efficiently as possible. To meet marketing demands for the coming week, HNC must produce at least 900 pounds of the Whole product, between 200 and 500 pounds of the Cluster product, no more than 150 pounds of the Crunch product, and no more than 250 pounds of the Roasted product. Each pound of the Whole, Cluster, Crunch, and Roasted product contains, respectively, 60%, 40%, 20% and 100% macadamia nuts with the remaining weight made up of chocolate coating. The company has 1,100 pounds of macadamia nuts and 800 pounds of chocolate available for use next week. The various products are made using four different machines that hull the nuts, roast the nuts, coat the nuts in chocolate (if needed), and package the product. The following table summarizes the time required by each product on each machine. Each machine has 60 hours of time available in the coming week. Machine. Hulling Roasting Coating Packaging Whole 1.00 2.00 1.00 2.50 Minutes Required Per Pound Cluster 1.00 1.50 0.70 1.60 Crunch 1.00 1.00 0.20 1.25 Roasted 1.00 1.75 0.00 1.00
Expert Answer:
Answer rating: 100% (QA)
To compare the results with the controllers information we need to allocate the fixed costs proportionally across the four products based on the optimal production quantities and total quantity Lets c... View the full answer
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these accounting questions
-
Preferred Stock, 7 % , $ 5 Par, 6 0 , 0 0 0 shrs Issued & Outstanding....... 3 0 0 , 0 0 0 PICEP - Preferred Stock........................................................................... 6 0 , 0 0...
-
Developing an Excel model and word document solution. Business Decision Modeling using Excel. Swiss Chocolate Company The Swiss Chocolate Company (SCC) makes four different products from macadamia...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Differentiate f(x) = log 10 (2 + sin x).
-
Compare the recognition lag and the implementation lag.
-
Predict the major products of the following reactions. (a) (b) (c) (d) CHO + Ag.o HO CHO + K,Cr,O/H,SO HO CHO + Ag(NHsh OH 0 CHO +KMnO (cold, dilute
-
In addition to the social media sites identified in this chapter, are there other social media sites that would be particularly helpful in a family law case? A personal injury case? Find other social...
-
An uninsulated steam pipe is used to transport high-temperature steam from one building to another. The pipe is of 0.5-m diameter, has a surface temperature of 150C, and is exposed to ambient air at...
-
A ceramic manufacturer promised to deliver 25 crates of vases to a Japanese importer under a "CFR" INTERCOM agreement. During transit, however, a large number of vases were broken. The buyer wants to...
-
The pesticide Atrazine (C 8 H 14 C1N 5 , mol wt. 216 g/ mol) degrades in soil by a first-order reaction process. Consider the situation shown in the figure below, where there is a spill of solid...
-
(5 points) -2 -6 -6 Let y =| -9 |, uj =| 6 |, uz -7 -2 -3 Compute the distance d from y to the plane in R' spanned by uj and uz. %3D
-
Suppose YE is an all-equity firm with an EBIT of $27,000 per year that is expected to stay the same for the foreseeable future. Your research shows that the beta equity of YE is 1.50 and it has...
-
your client needs to buy a car the vehicle price is 29200 and they intend to make a down payment of 4800 assume compounding and payments at 12 times per year interest rate = 5.60% period = 8 years...
-
Consider a version of a binary search tree (BST) for sorted maps that stores additional information as data members of each node w of the tree to account for the smallest and largest keys in the...
-
1. *For following circuit where C = C = 100 uF, R = R = 2000. ww R x(1) R a) Find its transfer function H(s). b) Find its poles. c) Find its unit impulse response h(t). C y(t)
-
On January 1, 2024, Morey, Incorporated, exchanged $180,575 for 25 percent of Amsterdam Corporation. Morey appropriately applied the equity method to this investment. At January 1, the book values of...
-
American Food Services, Incorporated leased a packaging machine from Barton and Barton Corporation. Barton and Barton completed construction of the machine on January 1, 2024. The lease agreement for...
-
SBS Company have received a contract to supply its product to a Health Care Service Hospital. The sales involve supplying 1,250 units every quarter, the sales price is RM 85 per unit. The Client...
-
David and Darlene Jasper have one child, Sam, who is 6 years old. The Jaspers reside at 4639 Honeysuckle Lane, Los Angeles, CA 90248. David's Social Security number is 577-11-3311, Darlene's is...
-
Leslie is a single taxpayer who is under age 65 and in good health. For 2012, she has a salary of $23,000 and itemized deductions of $1,000. Leslie is entitled to one exemption on her tax return. a....
-
Mike sells his home to Jane on April 2, 2012. Jane pays the property taxes covering the full calendar year in October, which amount to $2,500. How much may Mike and Jane each deduct for property...
-
Fernetti Advertising Agency was founded by John Fernetti in January of 2007. Presented below are both the adjusted and unadjusted trial balances as of December 31, 2008. Instructions (a) Journalize...
-
A review of the ledger of Remington Company at December 31, 2008, produces the following data pertaining to the preparation of annual adjusting entries. 1. Salaries Payable \($0.\) There are eight...
-
Givens Graphics Company was organized on January 1, 2008, by Sue Givens. At the end of the first 6 months of operations, the trial balance contained the following accounts. Analysis reveals the...
Study smarter with the SolutionInn App