How to solve with excel using the solver button

Keeping Time

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 hove changed over the years, but the company has prospered by con tinually updating its product line to satisfy its affhent chentele. The Lenter-Smith family continues to own a majority share of the company and the grandchildren of Alfred Lester Smith now. bold several of the top managerial positions. One of these grand

children is Meredith Lester-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. Rut what should it be? As she ponders this crucial decision, Mer- edith'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 launch- ing a modern version of this clock

Meredith wants all these numbers pinned down considerably further. However, she feels that some analysis can be done now to draw preliminary conclusions

a. Assuming that all clocks produced are sold, develop a spad sheet model for estimating the profit or loss from producing any particular number of clicks Use this spreadsheet to find the broak-even point by trial and

c. Develop the corresponding mathematical expression for the estimated profit in terms of the number of clocks produced. d. Use a graphical procedure to find the beak-even point

e. Use the algebraic procedum to find the break-even point 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 intro ducing this new product. However, Meredith is concerned that this conclusion might change if more accurate estimates went available for the various costs and revenues. Therefore, she

This is a difficult decision. Meredith realizes that grandfa wants what if analysis done on these estimates. Perform what ther clocks now une largely out of style. However, if she is so nostalgic about the memory of the grandfather clock in her grandparents' home, wouldn't them he a considerable number of other relatively wealthy couples with similar memories who would welcome the prestige of adding the grandeur of a beauti fully 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 them would be enough sales potential to make this a profitable product

if analysis by independently investigating each of the following question f. How large can the cost of designing this product and setting up the production facilities be before the grandfather clocks cease to be profitable?

g. How large can the production cost for each additional clock

be before the grandfather clocks cease to be profitable?

k If both of the costs identified in parts fand g were 50% langer than their initial estimates, would producing and selling the grandfather clocks still be profitable?

4. How small can the price for selling each clock be before the

grandfather clocks cease to be profitable?

Now suppose that 300 grandfather clocks are produced but

only 200 are sold

Meredith had an excellent management science coume a part of her MBA program in college, so she realizes that break-even 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- Would it still be profitable to produce and sell the grandfather father clock and then setting up the production facilities to clocks under this circumstance? 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,XXX). The marketing department estimates that their price for selling the clocks can be successfully set at about $4.500 apieve, but a firm forecast of how many docks can rice har believed that the sales likely would reach into the digita