A company has received an order to manufacture 75 customized units of an entertainment cabinet. The...

A company has received an order to manufacture 75 customized units of an entertainment cabinet. The company is being offered $960 per cabinet and the CEO must decide whether or not to accept the order. An analysis of the production process for the cabinet reveals that a new, special-purpose lathe will be required. The estimated cost of the lathe is $14,400, although there is some uncertainty due to new tariffs. The variable cost is more difficult to estimate because the customization of the cabinet will require some new production techniques. The CEO has decided to use $600 per cabinet as an initial estimate. The company has a policy of accepting orders if there is a profit rate (net profit/total revenue) of at least 15%.

1. Develop a spreadsheet model for this situation:
   1. Be sure to put ALL numerical values in separate cells from formulas.
   2. Format all dollar amounts as Currency with 0 decimals and use a Percentage format with 1 decimal for the profit rate.
   3. Name this sheet “Model.”
   4. Set the Orientation of the sheet (under Page Layout) to Landscape.

2. Write a formula to calculate the breakeven number of cabinets on your spreadsheet. Format this cell as a Number with 0 decimals.   

3. The CEO wants to see how the profit rate reacts to changes in both the variable cost per cabinet and the fixed cost of the lathe. Construct a data table based on your spreadsheet model using Excel’s Data Table command to show the profit rate associated with the variable cost per cabinet ranging from $500 to $700 (in increments of $20) as the fixed cost varies from $12,000 to $16,800 (in increments of $1200 across the top of the table).
   1. Put a border around the contents of the table.
   2. Format values outside the border as Currency with 0 decimals.
   3. Format values inside the table as Percentage with 1 decimal.
   4. Apply conditional formatting to the cells in the table to indicate amounts that are 15% or higher.

4. Create a line chart (with markers) showing the profit rate for lathe costs of $14,400, $15,600, and $16,800 as the variable cost changes, using the corresponding three columns from your data table.
   1. Move this chart to a “New Sheet” using the option in the Location group on the Design tab leaving its default name of Chart1.
   2. Give the chart a title of “Profit Rate Analysis”.
   3. Set the Orientation of the sheet (under Page Layout) to Landscape.
   4. Format the plot area with a solid line border.
   5. Use appropriate axis titles and axis formats, including solid axis lines.
   6. Name each data series with the fixed cost in a chart legend on the right.
   7. Put the series in order from lowest to highest cost.

5. Assign names to all of the cells and display (paste) a list of these names on your spreadsheet. Then use Excel’s Scenario Manager tool to perform an analysis of the following scenarios, relative to the initial estimates:

• Optimistic case: Fixed cost of the lathe is $12,000 and variable cost is $500 per cabinet.
• Pessimistic case: Fixed cost of the lathe is $16,800 and variable cost is $700 per cabinet.
• Additional sales: The number of cabinets ordered increases to 100.
• Price increase: The amount offered per cabinet increases to $1,084.

The changing cells for your scenario analysis should include all of the model inputs: price, sales volume, and fixed and variable costs. Your Summary report should give the results for total revenue, total cost, net profit, profit rate, and breakeven volume. DO NOT edit this report except to format the page for printing.

6. Answer the questions on the attached sheet.

This assignment should be completed according to the Instructions for Analytics Exercises (posted separately on Canvas). Print copies of the following items to turn in:

• Cover sheet
• Model worksheet with data table
  • Format Landscape to fit on 1 page
  • Print with Excel’s gridlines and row/column headings.
• Formulas for the Model worksheet, including data table (TABLE) formulas
  • Adjust column widths to display complete formulas
  • Format Landscape to fit on 1 page
  • Print with Excel’s gridlines and row/column headings
• Chart sheet, formatted Landscape.
• Scenario Summary report, formatted Landscape.
• Your (typed or neatly handwritten) Answer Sheet.

Place your pages in the order listed above and staple them together. Also be sure to submit your Excel file (properly named) in the Assignment on Canvas. You must submit both a hard copy and an electronic file on time for this assignment to receive credit for your work!

Answer Sheet for Analytics Exercise 1

1. If the company accepts the order, it can expect to earn a net profit of $_________________ under the initial estimates. A minimum of __________ cabinets must be ordered to breakeven.

2. The company ___________________(should/should not) accept the order based on initial estimates because _________________________________________________________

________________________________________________________________________.

3. If the lathe cost is uncertain, the data table results indicate that the variable cost per cabinet cannot exceed $____________ to guarantee a profit rate that meets company policy.

4. The relationship between variable cost and the profit rate is ______________________ (linear/nonlinear), because the effect of a $20 increase in the variable cost _______________ (is/is not) constant over the range from $500 to $700 .

5. There is a $________________ difference in net profit between the pessimistic and optimistic scenarios. The profit rate will meet company policy in ______ of the 4 what-if scenarios.