Question:
You are entering the widget business. It incurs a capacity cost of $900,000 in year 0 to develop the capacity. Each widget sells for $40 (unit price). Assume this price is fixed. You have many competitors in the market. During year 1, the whole market size is 100,000 units, and the market is growing at 10% per year. For example, in year 2, the market size will be 100,000×(1+10%) units. Though "units" as a measure is an integer, in the model there is no need to round your data.
The market is shared by you and your competitors. You think that your market share probably will be 30% each year from year 1 to year 6. For example, if your market share is 30% and the whole market size is 100,000 units in year 1, then in year 1 you will sell 30%×100,000=30,000 units. However, since you are not 100% sure about your market share, you want the market share to be a parameter you can easily change in the model. Put label "market size " in $A$1, and put a trial/initial value of 30% in $B$1. In the model, when market size is involved in calculation you need to refer to B1 or $B$1 (of course, you need to correctly identify the type of reference you need to use in formulas).
In year 1 each widget sold incurs a variable cost of 20 dollars. This unit variable cost is then reduced by 1.2 dollars each year. For example, in year 2, each unit incurs a variable cost of 18 dollars. However, "1.2" (dollars) here should be treated as a parameter. In other words, you need to enter 2 in a cell and use its reference when you calculate variable costs starting from year 2.
Pretax Profits (please read note at the bottom of the page for definition) are taxed at 40%, but there are no taxes on negative profits. For example, if pretax profit is $100, then tax will be $100×40%=$40, and net profit will be $100-$40=$60. On the other hand, if pretax profit is -$100, tax will be zero. Here you need to let In each year, net profit is pretax profit minus tax. On the other hand, total profit is the sum of net profits earned from year 0 (zero) to year 6.
1) Build a model to calculate your revenue, pretax profit, and net profit each year from year 0 to year 6. In the model, also calculate total profit which is the sum of net profits earned from year 0 to year 6.
2) Create a one-way data table which has two columns with column 1 be market share and column 2 be total profit. In column 1, let market share increase from 1% to 100% with an increment of 1% (i.e., 1%, 2%, 3%, ..., 99%, 100%). In column 2, calculate the corresponding total profit.
3) Create a two-way data table to show how market share and unit price affect total profit. Let you market share change from 20% to 50% with an increment of 1%, and unit price change from 30 dollars to 50 dollars with an increment of 1 dollar.
Terms/Variables used in the descriptions above:
Capacity cost: a fixed cost of $900,000, only incurred in year 0.
Variable cost: cost per unit, incurred from year 1 to year 6.
Revenue: number of units sold multiplied by unit price.
Pretax profit: revenue minus the sum of variable cost and capacity cost (if incurred). Note that in year 0, revenue is zero as no unit is sold.
Net profit: pretax profit minus tax.