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, Meredith’s
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, wouldn’t 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 Excel’s 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 Excel’s 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
company’s 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 Excel’s 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?
....................................
