1

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

Question

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.

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

  1. 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.

  1. 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.

  1. 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.

  1. 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.

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

________________________________________________________________________.

  1. 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.

  1. 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 .

  1. 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.

Answers

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.


Similar Solved Questions

1 answers
3. (4 pts.) Suppose that a shiny silver spherical Christmas-tree ornament makes a nicely reflective convex...
3. (4 pts.) Suppose that a shiny silver spherical Christmas-tree ornament makes a nicely reflective convex mirror. The omament has a diameter of 12.0 cm. You position your face (the "object") 15.0 cm away from the surface of the ornament. When you look at the ornament, you see your reflectio...
1 answers
These are in order The following information about the payroll for the week ended December 30...
these are in order The following information about the payroll for the week ended December 30 was obtained from the records of Boltz Co. Salaries: Deductions: Sales salaries $330,000 Income tax withheld $117.200 Warehouse salaries 184,000 U.S. savings bonds 14,520 Office salaries 146,000 Grou...
1 answers
Compute for the years of 2017 for the companies of Walmart and Amazon separately what their...
compute for the years of 2017 for the companies of Walmart and Amazon separately what their profit margin is, what their asset turnover is, what the return on assets is, and what the return on common stockholder's equity is. How would you evaluate each of the two companies profitability...
1 answers
The adjusted trial balance for Morrison Services Ltd, at July 31, 2018 is as follows: Morrison...
The adjusted trial balance for Morrison Services Ltd, at July 31, 2018 is as follows: Morrison Services Ltd. Adjusted Trial Balance July 31, 2018 Credit Cash Debit $7,490 19,875 Accounts receivable Supplies 3,460 Prepaid insurance 3,620 Equipment 25,300 Accumulated depreciation-equipment $5,785 Acco...
1 answers
At the Spring Valley Company, the cost of the personnel department has always been charged to...
At the Spring Valley Company, the cost of the personnel department has always been charged to production departments based upon number of employees. Recently, opinions gathered from the department managers indicate that the number of new hires might be a better predictor of personnel costs. Total pe...
1 answers
(1) X30 pnts) Mixing together NH(aq) and CIO (aq) liberate a number of toxic gases. The...
(1) X30 pnts) Mixing together NH(aq) and CIO (aq) liberate a number of toxic gases. The following reactions are the dominant pathways for the production of toxic gasses: 2010 (aq) + 2NH(aq) Cl(aq) + 2NH, (aq) + H2O(1) 2NH3(aq) + Cl:(aq) + 2NH2Cl(g) Cl(aq) Cl.(g) "The amount of NH Cl(o) produced ...
1 answers
Question 18 For which of the following goods would the imposition of a tax most likely...
Question 18 For which of the following goods would the imposition of a tax most likely improve the efficiency of the market outcome? o the production of a good with external benefits o the production of a rival good with no external costs o the production of a good with external costs o the producti...
1 answers
B. A compound containing nitrogen and oxygen is decomposed in the laboratory. It produces 24.5g nitrogen...
b. A compound containing nitrogen and oxygen is decomposed in the laboratory. It produces 24.5g nitrogen and 70 g oxygen. Calculate the empirical formula...
1 answers
3. (15 pts) Let A be an m x n matrix with rank r, and let...
3. (15 pts) Let A be an m x n matrix with rank r, and let V = C(A). (a) V CIRP for what p? (b) What is V. in terms of a fundamental subspace for A? (c) How many vectors are in a basis for V, and how many in a basis for v 1? (d) For what m, n, and r docs Ax=b have a solution for every b? (e) Is a set...
1 answers
E4.2 (L02) Computation of Net Income). The following are changes in all account balances of Jackson...
E4.2 (L02) Computation of Net Income). The following are changes in all account balances of Jackson Furniture Co. during the current year, except for retained earnings. Increase (Decrease) Cash £ 69,000 Accounts Receivable (net) 45,000 Inventory 127,000 Investments (47,000) Accounts Payable &p...
1 answers
A large weather balloon whose mass is 222 kg is filled with helium gas until its...
A large weather balloon whose mass is 222 kg is filled with helium gas until its volume is 330 m3. Assume the density of air is 1.20 kg/m3 and the density of helium is 0.179 kg/m3 (a) Calculate the buoyant force acting on the balloon. (b) Find the net force on the balloon and determine whether the b...
1 answers
Describe how common law rules for perfect tender, mirror image and other contract requirements are relaxed...
Describe how common law rules for perfect tender, mirror image and other contract requirements are relaxed under the UCC to facilitate sales and commerce....
1 answers
Financial statements for Askew Industries for 2021 are shown below (in thousands): 2021 Income Statement Net...
Financial statements for Askew Industries for 2021 are shown below (in thousands): 2021 Income Statement Net sales $ 9,200 Cost of goods sold (6,350) Gross profit 2,850 Operating expenses (2,150) Interest expense (220) Income tax expense (192) Net income $ 288 Comparative Balance Sheets Dec. 31 2021...
1 answers
Answer is A !Note: Use the Agresti-Coull “plus four” method of forming a CI for p....
Answer is A !Note: Use the Agresti-Coull “plus four” method of forming a CI for p. Please show the work Traces of illegal drugs were found on 8 of a random sample of 60 bills. A 95% confidence interval for the population proportion of tainted bills is which one of the following? (A) 10/6...
1 answers
Problem A-5 A diet is being prepared for the University of Arizona dorms. The objective is to fee...
Problem A-5 A diet is being prepared for the University of Arizona dorms. The objective is to feed the students at the least cost, but the diet must have between 1,900 and 3,700 calories. No more than 1,500 calories can be starch, no fewer than 550 can be protein, and no more than 330 calories shoul...
1 answers
Q2.(A).Three identical coils, each of resistance 202 and inductance 55mH are connected in star to a...
Q2.(A).Three identical coils, each of resistance 202 and inductance 55mH are connected in star to a 420V. 56 Hz, 3-phase supply. Determine the total power dissipated in each case. What is the major beneficial for choosing the three phase system over single phase system? [5 Marks]...
1 answers
What is the role of the performance auditor?
What is the role of the performance auditor?...

-- 0.052565--