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.