please solve using excel!

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 Lester-Smith now hold several of the top managerial positions. One

of these grandchildren 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. 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.

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.

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.