Question

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.

Answer 1

Answer:

Total Revenue= Cabinet demanded * Price of Cabinet

Profit Rate= (Total Revenue-75* Variable Cost- Fixed Cost)/ Total Revenue

	cabinet demanded	75
	Price per cabinet	960
	Total revenue	72000
		Fixed Cost
	Profit Rate	12000	13200	14400	15600	16800
Variable cost	500	31.3%	29.6%	27.9%	26.3%	24.6%
	520	29.2%	27.5%	25.8%	24.2%	22.5%
	540	27.1%	25.4%	23.8%	22.1%	20.4%
	560	25.0%	23.3%	21.7%	20.0%	18.3%
	580	22.9%	21.3%	19.6%	17.9%	16.3%
	600	20.8%	19.2%	17.5%	15.8%	14.2%
	620	18.8%	17.1%	15.4%	13.8%	12.1%
	640	16.7%	15.0%	13.3%	11.7%	10.0%
	660	14.6%	12.9%	11.3%	9.6%	7.9%
	680	12.5%	10.8%	9.2%	7.5%	5.8%
	700	10.4%	8.8%	7.1%	5.4%	3.8%

For Fixed Cost= $14400 and Variable cost= $600 per cabinet

Net Profit= total Revenue- 75* variable cost-fixed cost=72000-75*600-14400=$12600

Breakeven point = Fixed cost/(Cabinet Price- Variable Cost)=14400/(960-600)=40 cabinets

Profit rate for 75 cabinets=12600/72000=17.5%

Since profit rate is greater than expected profit rate so he should accept the order.

Variable cost cannot exceed $580 dollar for guaranteed return for 15%.

Relationship between variable cost and Profit rate is linear and effect of $20 increase in variable cost is constant.

35.0% 30.0% 25.0% 20.0% 2 15.0% OL 10.0% 5.0% 0.0% 500 520 540 560 580 600 620 640 660 680 700 Variable cost

Profit in optimistic scenario=72000-75*500-12000=$22500
Profit in Pessimistic scenario=72000-75*700-16800=$2700

Difference in Net Profit=22500-2700=$19800

Profit Rate in optimistic scenario=22500/72000=31.3%

Profit Rate in Pessimistic scenario=2700/72000=3.8%

Profit rate for 100 cabinets= (100*960-100*600-14400)/100*960=37.5%

Profit rate for price $1084 per cabinets=(1084*75-75*600-14400)/1084*75=26.9%

From above calculation it is clear that profit rate will meet company policy in 3 out of 4 what if scenarios.