Description of Examples

  1. Example 1 Breakeven analysis at great threads

The Great Threads Company plans to launch a direct mail campaign to increase profit. The cost of printing catalog is $20,000 plus $0.1 per catalog. The cost of mailing each catalog is $0.15 and $0.2 in extra cost if the mail is responded. The average size of an odder is $40, and the cost per order is the average around 80% of the order’s value. The company plans to mail 100,000 catalogs.

Questions to be answered:

  1. How is profit affected by a change in the response rate?
  2. For what response rate does the company break even?
  3. Should it proceed with mailing catalogs if the response rate is expected to be 3%?
  4. What role does uncertainty play in the usefulness of the model?

Modeling Process

  • define appropriate range names to cells;
  • Model the responses ( variable of this model case) ; Enter any trial value, such as 8%;
  • Model the revenue, cost and profits. The range names make the formulas be more self-explanatory.
  • Forming a data table to answer question1: show how profit varies with the response rate. The data table clearly show that each 1% increase in response rate results in 100,000(0.01) =1000 more orders or $7800.

Using Goal Seek

Use goal seek to answer question2. Select Tool/Goal seek, define PROFIT as Set Cell with a value 0 and define Changing Cell as RESPONSE RATE. As a result, find the break even point’s respond rate is 5.77%.

Limitation of the Model

  • The model can’t calculate the profit if a customer responds with two or more orders.
  • The size may not always be $40.

2. Example 2: Estimating the Relationship between Price and Demand

This example illustrates a very important modeling concept: estimating the relationship between variables by curve fitting.

Links sells its Golf clubs throughout the United States throughout the United States. And the demand for its clubs varies considerably with price. The company wants to estimate the relationship between the demand and its price.

Questions to be answered:

  • Assuming the unit cost of producing a set of clubs is $250 and if the price is a multiple of $10. How much should the company charge to maximize its profit?
  • How does the optimal price depend on the unit cost of producing a set of clubs?
  • Is the model an accurate representation of reality?

Modeling Process

The example has been divided into two parts: estimating the relationship between price and demand, and creating the profit model.

  • Estimate relationship

Linear y=a+bx Power y=ax^b Exponential y=ae^bx

After calculating the average of APE (absolute percent error), the power curve y=ax^b has been found provides the best fit.

  • Creating the profit model

Input Unit Cost to produce, use formulas to calculate Predicted demand, Total revenue, Cost and Profit. Select Unit price as decision variable.

Then use data table to list profits under different unit prices. In order to find the maximum profit, two methods have been introduced. One of the methods is to use INDEX and MATCH functions of EXCEL.

Sensitivity to Variable Cost

Two-way data table is adopted to analyze the unit variable cost changes when the best price changes.

Limitation of the model

  • The golf club prices should not be necessarily restricted to the multiples of $10;
  • The assumption that price is the only factor that influences demand.
  • The assumption that demand equals sales.

3. Example3: Ordering with quantity discounts at Sam’s bookstore

Sam’s Bookstore places orders for all the latest books and then distributes them to its individual bookstores. But, Sam’s is uncertain about the demand for the books. The bookstore needs a model to help it with the optimum number of books to be ordered.

Modeling Process

  • Input cells: Unit price, Sale price for leftover

Unit cost as a function of quantity ordered. The detail is below

At least / Unit cost
0 / $24.00
1000 / $23.00
2000 / $22.25
3000 / $21.75
4000 / $21.30
  • Select Order quantity as decision variable.
  • Use formulas to calculate: Revenue, Total ordering cost and Profit. VLOOKUP function has been introduced.

Other modeling issues]

  • We should keep the layout of spreadsheet easy to read and logical. No certain format is required.

4. Example 4: Hedging risk with put options

This is an example of using spreadsheet to analysis the hedging risk with put options. Harry is in a long position of one share of Dell’s stock for $94.5. He is worried about the stock’s price going down. So he buys some European option to hedge the risk. The strike price of the put is $80 and each put is $5.25.

Question to be answered

  • As a function of the number of puts purchased, what is the best and worst scenario for the return the portfolio?
  • How does analysis change if Harry purchases 100 shares rather than 1 share?

Modeling process

Input cells

  • Current price of stock
  • price per put
  • Exercise price

Select Number of put purchased as decision variable; the external variable is the Future stock price at Nov. 22, 1998.

Use formulas to calculate: Amount invested, Amount received from stock, Amount received from puts, Total amount received and Return.

Use data table to analysis the return as a function of Future Price and Puts Purchased and show the changes of return when future price of stock and put number change by chart below

5. Example 5: Calculating NPV at Acron

Acron, a drug company needs to determine the annual drug production capacity. Acron has a patent on the drug for 20 years. Acron has made the following assumptions.

  • Demand will be 10,000 units in the first year
  • Demand will grow at an annual rate of 15% in the years 2 – 6.
  • Demand will grow at an annual rate of 5% in the years 7 – 20.
  • Each unit of annual production capacity costs $6 to build. This is payable at the end of the first year and is depreciated on a straight-line basis for 5 years.
  • In the first year the drug will sell for $8 per unit and will incur a variable production cost of $5.
  • The cost of maintaining a unit of capacity in the first year is $1.
  • The unit price, unit variable cost and unit capacity maintenance cost will increase by 5% every year.
  • Tax rate is 40%
  • The corporate discount rate is 40% and cash flows are assumed to occur at the end of the year.

Questions to be answered

  1. What should be the capacity level?
  2. In what way will the optimum capacity change with a change in the discount rate?
  3. How realistic is the model?

Modeling Process

Input: Year 1 demand, Annual demand growth, Unit cost of production capacity, Depreciation rate, Year1 monetary values, Inflation rate, Tax rate and Discount rate.

Select Capacity level as Decision Variable.

Use formulas to calculate: Building cost, Depreciation, Demand, Units sold, Unit Price, Unit variable cost, Unit maintenance cost, Revenue, Variable cost, Maintenance cost, NPV and so on.

Deciding on Capacity

Make a data table for NPV as a function of capacity to find the optimum capacity is 21000.

Sensitivity of Capacity to the discount rate

Make a data table for NPV as a function of capacity and discount rate.

Limitation of the model

The major flaw of the model is they have ignored uncertainty. It is clear that demand, future price and future cost are highly uncertain.