.One month later, the preliminary estimates of the relevant financial figures come back. The cost of designing the grandfather 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.

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:

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