How to solve using Excel spreadsheet solver only I to J please help thank you!
Founded nearly 50 years ago by Alfred Lester-Smith, Beautiful Meredith wants all these numbers pinned down considerably Clocks specializes in developing and marketing a diverse line further. However, she feels that some analysis can be done now of large ornamental clocks for the finest homes. Tastes have to draw preliminary conclusions. changed over the years, but the company has prospered by con- tinually updating its product line to satisfy its affluent clientele. a. Assuming that all clocks produced are sold, develop a spread- The Lester-Smith family continues to own a majority share of sheet model for estimating the profit or loss from producing the company and the grandchildren of Alfred Lester-Smith now any particular number of clocks. hold several of the top managerial positions. One of these grand- b. Use this spreadsheet to find the break-even point by trial and children is Meredith Lester-Smith, the new CEO of the company. error. Meredith feels a great responsibility to maintain the family c. Develop the corresponding mathematical expression for the heritage with the company. She realizes that the company needs estimated profit in terms of the number of clocks produced. to continue to develop and market exciting new products. Since d. Use a graphical procedure to find the break-even point. the 50th anniversary of the founding of the company is rapidly e. Use the algebraic procedure to find the break-even point. approaching, she has decided to select a particularly special new product to launch with great fanfare on this anniversary. But A fairly reliable forecast now has been obtained indicating what should it be? As she ponders this crucial decision, Mer- that the company would be able to sell 300 of the limited-edition edith's thoughts go back to the magnificent grandfather clock grandfather clocks, which appears to be enough to justify intro- that her grandparents had in their home many years ago. She had ducing this new product. However, Meredith is concerned that admired the majesty of that clock as a child. How about launch- this conclusion might change if more accurate estimates were ing a modern version of this clock? 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 are largely out of style. However, if she is so if analysis by independently investigating each of the following nostalgic about the memory of the grandfather clock in her questions. grandparents' home, wouldn't there be a considerable number of other relatively wealthy couples with similar memories who f. How large can the cost of designing this product and setting would welcome the prestige of adding the grandeur of a beauti- up the production facilities be before the grandfather clocks fully designed limited-edition grandfather clock in their home? cease to be profitable? Maybe. This also would highlight the heritage and continuity of g. How large can the production cost for each additional clock the company. It all depends on whether there would be enough be before the grandfather clocks cease to be profitable? sales potential to make this a profitable product. h. If both of the costs identified in parts f and g were 50% larger Meredith had an excellent management science course as part than their initial estimates, would producing and selling the of her MBA program in college, so she realizes that break-even grandfather clocks still be profitable? analysis is needed to help make this decision. With this in mind, i. How small can the price for selling each clock be before the she instructs several staff members to investigate this prospective grandfather clocks cease to be profitable? product further, including developing estimates of the related costs and revenues as well as forecasting the potential sales. Now suppose that 300 grandfather clocks are produced but One month later, the preliminary estimates of the relevant only 200 are sold. financial figures come back. The cost of designing the grand- j. 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 Additional Case would be roughly $2,000. The marketing department estimates that their price for selling the clocks can be successfully set at An additional case for this chapter is also available at the Univer- about $4,500 apiece, but a firm forecast of how many clocks can sity of Western Ontario Ivey School of Business Website, cases. be sold at this price has not yet been obtained. However, it is ivey.uwo.ca/cases, in the segment of the CaseMate area design believed that the sales likely would reach into three digits. nated for this book
