Producing Peakbabies
Inputs
Initial fixed cost
Year 1 sales
Unit price, years 1-5
$4,000,000
80000
$25
Costs, years 1-5
Variable cost (% of revenue)
SG&A cost (% of revenue)
Year 1
Year 2
Year 3
Year 4
Year 5
50%
40%
38%
36%
34%
32%
Model
Annual growth in
Dataware pricing
Inputs
Current sales
Current price
Unit variable cost
40000
$45
$5
Data on rebates
Amount of rebate
Pct taking advantage
Increase in sales
$10
50%
30%
Data on price cut
Amount of cut
Increase in sales
$6
20%
Profits
Current
With rebate
Wi
Microsoft sales
Year
0
1
2
3
4
5
6
7
8
9
Sales
98
140
198
346
591
804
1183
1843
2759
3753
Parameters of exponential fit
Constant
96.642
Exponent
0.4171
Part (b)
Pct increase
51.76%
Note: Excel won't provide the exponential
trend curve if the years are exp
Pricing and advertising
Inputs
Unit cost
Demand function is specified by:
Constant
Coefficient of price
Coefficient of square root of advertising
Profit model
Price (trial value)
Advertising (trial value)
Demand (units sold)
Profit
$5
100
-0.5
26
I cheate
Chemical reaction
Inputs
Temperature (degrees F)
Time (seconds)
100
300
Rescaled inputs
Temp (x1)
Time (x2)
-2.5
0
Output
Yield
I created two two-way data tables. The top one tries a rough
grid of times and temps. The best combination is Time 300
and Temp
Yield of bond
Bond data
Year
1
2
3
4
5
6
Payment
$100
$100
$100
$100
$100
$1,100
Current price
$1,040
Yield (trial value)
Use Goal Seek to set the NPV in
cell B16 to 0, using the yield in
cell B14 as the changing cell.
9.11%
NPV
$0.00
=
0
Demand for Wozac
Inputs
Current year demand
Annual growth in demand
Unit capacity cost (one-time cost)
Unit selling price
Unit variable cost
Unit operating cost (per year)
50000
5%
$16.00
$3.00
$0.20
$0.40
Decision variable
Units of capacity to build now
Project cash flows
Cash flows
Year
1
2
3
4
5
6
7
Discount rate
Cash flow
-$400
$200
$600
-$900
$1,000
$250
$230
15%
Part (a): cash flows at ends of years
NPV
$375.06
Part (b): cash flows at beginnings of years
NPV
$431.32
Part (c): cash flows at middles o
Software project
Inputs
Fixed cost of new version
Unit selling price
Unit variable cost
$6,000,000
$50
$10
Current market size
Growth of market
For each of first 5 years
For each of next 5 years
300000
10%
5%
English version market share
Market share with
Widget prototype
Inputs
Prototype cost (year 1)
Unit selling price
Unit variable cost
Market size, year 1
Market growth rate
Tax rate
Model
Our market share
$500,000
$40
$20
100000
10%
40%
Part (a): Use Goal Seek to set the sum of
FCF in cell J22 to 0, us
Monthly payments on loan
Inputs
Loan amount
Monthly interest rate
$25,000
1%
Monthly payment (trial value)
$556.11
Use Goal Seek to set the ending
balance in cell D70 to 0, using cell
B7 as the changing cell.
Amortization schedule
Month Beginning balance
Pricing a toaster
Demand function is specifed by:
Constant
100
Power of price
-2
Part (a)
Variable cost
Price (trial value)
Demand
Profit
$10
$15
444
$2,222
Data table for profit as a function of price
$2,222
$12
$1,389
$14
$2,041
$16
$2,344
$18
$2,469
$2
Buying put options for hedging
Inputs
Due in one year (yen)
Current exchange rate ($/yen)
Price per put ($)
Exercise price ($)
Model
Puts purchased (trial value)
Final exchange rate (trial value)
Value of payment due ($)
Price paid for puts ($)
Cash flow
Calculating car payments
Price
Down payment
Amount financed
Term in months
Annual interest rate
Monthly interest rate
Monthly payment
Total interest paid
$30,000
$5,000
$25,000
36
8%
0.67%
$783.41
$3,202.73
Payment schedule (works for any term up to 60 mo
Internal rate of return
Cash flows
Year Cash flow
1
-$400
2
$200
3
$600
4
-$900
5
$1,000
6
$250
7
$230
IRR (no guess)
These calculations assume all cash flows
occur at the ends of the respective years.
The IRR is "stable" for these cash flows,
that is, it
Internal rate of return
Cash flows
Year Cash flow
1
-$20
2
$82
3
-$60
4
$2
IRR, using a guess
Guess
-90%
IRR
-9.59%
Data table for IRR as a function of the guess
-9.59%
-90% -9.59%
-80% -9.59%
-70% -9.59%
-60% -9.59%
-50% -9.59%
-40% -9.59%
-30% -9.59%
-2
Unit prices of specific products (along top) at a chain's stores (along side)
32" TV
$518.99
$466.99
$508.99
$476.99
$495.99
$526.99
$452.99
$525.99
$511.99
Bloomington
Indianapolis
Columbus
Fort Wayne
Evansville
Muncie
Terre Haute
West Lafayette
Richmond