Please solve it in excel and show formulas. It is Business Analytics.

Founded nearly 50 years ago by Alfred Lester-Smith, Beautiful Clocks specializes in developing and marketing a diverse line of large ornamental clocks for the finest homes. Tastes have changed over the years, but the company has prospered by continually updating its product line to satisfy its affluent clientele. The Lester-Smith family continues to own a majority share of the company and the grandchildren of Alfred Lestef-Smith now hold several of the top managerial positions. One of these grandchildren is Meredith Lestef-Smith, the new CEO of the company.

Meredith feels a great responsibility to maintain the family heritage with the company. She realizes that the company needs to continue to develop and market exciting new products. Since the 50th anniversary of the founding of the company is rapidly approaching, she has decided to select a particularly special new product to launch with great fanfare on this anniversary. But what should it be? As she ponders this crucial decision, Merediths thoughts go back to the magnificent grandfather clock that her grandparents had in their home many years ago. She had admired the majesty of that clock as a child. How about launching a modern version of this clock?

This is a difficult decision. Meredith realizes that grandfather clocks now are largely out of style. However, if she is so nostalgic about the memory of the grandfather clock in her grandparents home, wouldnt there be a considerable number of other relatively wealthy couples with similar memories who would welcome the prestige of adding the grandeur of a beautifully designed limited-edition grandfather clock in their home? Maybe. This also would highlight the heritage and continuity of the company. It all depends on whether there would be enough sales potential to make this a profitable product.

Meredith had an excellent Business Analytics course at JMU, so she realizes that breakeven analysis is needed to help make this decision. With this in mind, she instructs several staff members to investigate this prospective product further, including developing estimates of the related costs and revenues as well as forecasting the potential sales.

One month later, the preliminary estimates of the relevant financial figures come back. The cost of designing the grand- father clock and then setting up the production facilities to produce this product would be approximately $250,000. There would be only one production run for this limited-edition grandfather clock. The additional cost for each clock produced would be roughly $2,000. The marketing department estimates that their price for selling the clocks can be successfully set at about $4,500 apiece, but a firm forecast of how many clocks can be sold at this price has not yet been obtained. However, it is believed that the sales likely would reach into three digits. The production floor chief has estimated a maximum operating capacity of 500 clocks.

1. Develop a spreadsheet model for the situation described above, assuming production is set at full capacity:

Be sure to put all numerical values in separate cells from formulas.

Format all dollar amounts with $ signs and 0 decimals for all values. Format all other numerical amounts as

Number with thousands separator.

Name this sheet Beautiful Clocks Model.

2. Write a formula to calculate the breakeven number of clocks on your spreadsheet. Show the procedure to get to the formula in your written report. Format this cell as a number with a comma and 0 decimals. In a separate cell compute the breakeven number clocks as a percentage of the maximum capacity. Format this cell as a percentage with 0 decimals.

A fairly reliable forecast now has been obtained indicating that the company would be able to sell 300 of the limited-edition grandfather clocks, which appears to be enough to justify introducing this new product. However Meredith is concerned that this conclusion might change if more accurate estimates were available for the various costs and revenues. Therefore she wants what-if analysis done on these estimates:

3. Construct a data table based on your spreadsheet model using Excels Data Table command to show the breakeven number of clocks and percentage of maximum capacity with a price ranging from $2,500 to $5,000 per clock (in increments of $500).

Put a border around the contents of the table.

Format the amount inside the table as a Number with 0 decimals.

Format percent values inside the table as Percentage with 0 decimals.

4. Construct a data table based on your spreadsheet model using Excels Data Table command to show the net profit associated with the selling price ranging from $2,500 to $5,000 (in increments of $500) as the variable cost per clock varies from $1,000 to $4,500 (in increments of $500 across the top of the table and assuming the production volume remains at 300 clocks).

Put a border around the contents of the table.

Format all dollar amounts as Currency with 0 decimals.

Apply conditional formatting to the cells in the table that exceed $400,000.

5. Create a line chart showing the net profit for per-clock prices of $2,500, $3,500, $4,500 using the corresponding 8 columns of your data table:

Move this chart to a New Sheet.

Use appropriate axis titles.

Name each data series with the corresponding price in a chart legend on the right.

6. Answer the questions at the end of this document for the companys management.

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

Cover sheet prepared as described in the instructions.

Mathematical procedure to find formula for calculating the breakeven quantity. Model worksheet with data tables

Format Landscape to fit 1 page

Print with Excels gridline and row/column headings.

Formulas for the Model worksheet

Chart sheet, formatted Landscape

Neatly handwritten or typed answers to the questions below.

Place your pages in order listed above and staple them together. Also, submit your Excel file in the Assignment on Canvas.

Questions

1. If the company changes the selling price per clock from $4,500 to $5,000, this will .................. (increase/de- crease) the breakeven volume from .................. to .................. clocks.

2. How small can the selling price be before the grandfather clocks cease to be profitable? ..................

3. Therelationshipbetweenpriceandbreakevenpercentageofmaximumproductioncapacityis..................(lin-

ear/nonlinear), because ..........................................................................................

4. Raisingthesellingpriceto$5,000perclockwill..................(increase/decrease)thenetprofitfrom...............

to .................., assuming the variable cost remains $2,000 and the sales volume is 300 clocks.

5. How large can the setting-up costs be before the grandfather clocks cease to be profitable? ..................

6. How large can the cost per unit be before the grandfather clocks cease to be profitable? ..................

7. Now suppose that 300 grandfather clocks are produced but only 200 are sold. Would it still be profitable to produce andsellthegrandfatherclocksunderthiscircumstance? ..................(Yes/No)because........................... ....................................

8. If 300 grandfather clocks are produced, what would be the minimum amount to be sold in order to break even?

....................................