At a publishing company, you have been assigned to analyze the profitability of a contract for a new autobiography. The following assumptions have been made:

? There is a one-time royalty payment of $12 million

? The fixed cost of producing the hardcover version of $1 million

? The variable cost of printing each hardcover is $4 per book

? The sales revenue from each book sold is $25

? The forecasted sales in year 1 is 1 million hardcover copies, in year 2 is 400,000, and in year 3 is 200,000

? The fixed cost of producing a paperback version is $100,000

? The paperback variable cost is $1 per book

? The sales revenue from each paperback unit is $10

? Paperback sales are expected to be double that of the hardcover copies

Use the above information to set up the inputs and formulas for creating two Excel data tables to answer the following questions:

1. How will profits vary as hardcover sales vary incrementally from 100,000 units to 1 million units? [use increments of 100,000] 2. How will profits vary as hardcover sales vary from 100,000 to 1 million units AND the ratio of paperback sales to hardcover sales varies from 1 to 2.4.

PLEASE DO NOT SEND ME THIS PICTURE OF THE ANSWER AND SAY THAT IT IS RIGHT! READ THE QUESTION THOROUGHLY PLEASE!

Hard Cover Copies Sold Particulars Per Unit 100,000 200,000 300,000 400,000 500,000 600,000 700,000 800,000 900,000 1,000,D00 Sales S 15.00 $ 1,500,000 $ 3,000,000 $ 4,500,000 $ 6,000,000 $ 7,500,000 $ 9,000,000 $ 10,500,000 $ 12,000,000 $ 13,500,000 $ 15,000,000 Less : Variable Cost S 4.00 400,000 S 200,0DO $ 1,200,000 $ 1,600,000 $ 2,000,000 $ 2,400,000 $ 2,800,000 $ 3,200,000 $ 3,600,000 $ 4,000,000 Contribution S 11.00 $ 1,100,000 $ 2,200,000 $ 3,300,000 $ 4,400,000 $ 5,500,000 $ 6,600,000 $ 7,700,000 $ 8,800,000 $ 9,900,000 $ 11,000,000 Less : Fixed Cost $ 1,000,000 $ 1,000,000 $ 1,000,000 $ 1,000,000 $ 1,000,000 $ 1,000,090 $ 1,000,090 $ 1,000,000 $ 1,000,000 $ 1,000,000 $ 1,000,000 Operating Profit (A) 100,000 $ 1,200,000 $ 2,300,000 $ 3,400,000 $ 4,500,000 $ 5,600,000 $ 6,700,000 $ 7,800,000 $ 8,900,000 $ 10,000,000 Paper Copies Sold (2 times of hard cover sold) Particulars Per Unit 200,000 400,000 600,000 800,000 1,000,00 1,200,000 1,400,000 1,600,000 1,800,000 2,000,000 Sales 4.00 S 800,000 $ 1,600,000 $ 2,400,000 $ 3,200,000 $ 4,000,000 $ 4,800,000 $ 5,600,000 $ 6,400,000 $ 7,200,000 $ 8,000,000 ess : Variable Cost S 1.00 200,000 S 400,000 S 600,000 800,000 $ 1,000,000 $ 1,200,000 $ 1,400,000 $ 1,600,000 $ 1,800,000 $ 2,000,000 Contribution 3.00 600,000 $ 1,200,000 $ 1,800,000 $ 2,400,000 $ 3,000,000 $ 3,600,000 $ 4,200,000 $ 4,800,000 $ 5,400,000 $ 6,000,000 Less : Fixed Cost $ 100,000 S 100,000 100,000 $ 100,000 S 100,000 100,000 S 100,000 100,000 $ 100,000 100,000 $ 100,000 Operating Profit (B) 500,000 $ 1,100,000 $ 1,700,000 $ 2,300,000 $ 2,900,000 $ 3,500,000 $ 4,100,000 $ 4,700,000 $ 5,300,000 $ 5,900,000 One Time Royalty Paid (C) $ 12,000,000 $ 12,000,000 $ 12,000,000 $ 12,000,000 $ 12,000,000 $ 12,000,000 $ 12,000,000 $ 12,000,000 $ 12,000,000 $ 12,000,000 Net Profit Before Tax (A+8-C) $-11,400,000 $ -9,700,000 $ -8,000,000 $ -6,300,000 $ -4,600,000 $ -2,900,000 $ -1,200,000 | $ 500,000 | $ 2,200,000 $ 3,900,000