1 / 16

Example 2.1

Example 2.1. Finding a Breakeven Point. Background Information. The Great Threads Company sells hand-knit sweaters. Great Threads is planning to print a brochure of its products and undertake a direct mail campaign.

callum
Download Presentation

Example 2.1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Example 2.1 Finding a Breakeven Point

  2. Background Information • The Great Threads Company sells hand-knit sweaters. Great Threads is planning to print a brochure of its products and undertake a direct mail campaign. • The cost of printing the brochure is $20,000 plus $0.10 a catalog. The cost of mailing each catalog is $0.15. In addition, the company will include direct reply envelopes in it’s mailings. It incurs $0.20 in extra cost for each direct mail envelope that is used by a respondent. • The average size of a customer order is $40, and the company’s variable cost per order averages around 80% of the order’s value.

  3. Background Information -- continued • The company plans to mail 100,000 catalogs. It wants to develop a spreadsheet model to answer the following questions: • How does a change in the response rate affect profit? • For what response rate does a company break even? • If the company estimates a response rate of 3%, should it proceed with the mailing? • How does the presence of uncertainty affect the usefulness of the model?

  4. GREATTHREADS.XLS • This file contains the completed model shown below.

  5. GREATTHREADS.XLS -- continued • Note the clear layout of the model • The input cells are outlined and shaded and separated from the outputs. • There are boldfaced headings, several headings are indented. • Numbers are formatted appropriately. • Text boxes to the right spell out all the range names used.

  6. Creating the Model • To create this model, proceed through the following steps. • Enter heading and range names. • Obviously we have a lot of cells, more than you might want to enter, but you will see their value when we start entering formulas. • Enter input values. • The values in the shaded cells are all given in the statement of the problem. Enter these values and format them appropriately.

  7. Creating the Model -- continued • Model the responses. • We have not specified the response rate of the mailing, so enter any reasonable values such as 8% in the ResponseRate cell – we will perform sensitivity on this value later on – and enter the formula =NumMailed*ResponseRate in the NumResponses cell. • Model the revenues, costs and profits. • Enter the formula =NumResponses*AvgOrder in the Revenues cell. • Enter the formula =FCostPrinting, =SUM(VCostMailing)*NumMailed and =NumResponses*(AvgOrder*VCostOrderPct+VCostEnvelopes) in the Cost cells (E10, E11, E12). • Enter the formula =SUM(Costs) in the TotalCost cell, and enter the formula =Revenue-TotalCost in the Profit cell.

  8. Answering the Questions • Now that a basic model has been created, we can answer the questions posed by the company. • For questions 1, we form a data table to show how profit varies with the response rate. The table is shown here.

  9. Creating a Data Table • First, enter a sequence of trial values of the response rate in column A, and enter a “link” to profit in cell B20 with the formula =Profit. • Finally, highlight the entire table range, A20:B30, and select the Data/Table menu item to bring up the dialog box shown here.

  10. Creating a Data Table -- continued • It should be filled in as shown to indicate that the only input ResponseRate, is listed along a column. • When you click OK, Excel substitutes each response rate value in column A in to the ResponseRate cell,, recalculates the profit, and reports it in the data table. • For a final touch, we have created a scatterplot(or in Excel’s terminology X-Y chart) of the values in the data table.

  11. Answering the Questions -- continued • Clearly, profit increases in a linear manner as response rate varies. More specifically, a 1% increase in the response rate always increased profit by $7800. • Here is the reasoning. Each 1% in response rate results in 100,000 x 0.01=1000 more orders. Each order yields an average revenue of $40 but incurs a variable cost of $40 x 80% = $32 and a $0.20 envelope cost. The net gain is $7.80 per order, or $7800 for 1000 orders.

  12. Answering the Questions -- continued • From the data table, we see that profit goes from negative to positive when the response rate is somewhere between 4% and 5%. • Question 2 asks for the exact breakeven point. This could be found with trail and error but is easy with Excel’s Goal Seek tool. Goal Seek is useful for solving a single equation in a single unknown. • Here the equation is Profit=0, and the single unknown is the response rate.

  13. Answering the Questions -- continued • In Excel terminology, the unknown is called the changing cell because we are allowed to change it to make the equation true. • To implement Goal Seek, select Tools/Goal Seek menu item and fill in the resulting dialog box as shown below.

  14. Answering the Questions -- continued • After clicking on OK, the ResponseRate and Profit cells have values 5.77% and $0. In words, if the response rate is 5.77% Great Threads breaks even. If the response rate is greater then 5.77%, the company makes money; otherwise, it loses money. • Question 3 asks if the company should proceed with the mailing if the response rate is only 3%. From the data table, the apparent answer is “no” because profit is negative, a loss. However, like many U.S. companies, we are taking the short term view with this reasoning.

  15. Answering the Questions --continued • We should realize that many customers who respond to direct mail will reorder in the future. The company makes $7.80 per order. If each of the respondents ordered two or more times, say, the company would earn 3000 x $7.80 X 2 = $46,800 more than appears in the model, and profit would then be positive. • The moral is that we must look at long-term impact of our decisions. However, if we want to incorporate the long term explicitly into the model, we must build a more complex model.

  16. Answering the Questions -- continued • Finally, question 4 asks about the impact of uncertainty in the model. We would be kidding ourselves to think that all model inputs are known with certainty. • For example, the size of an order is not always $40 – it might be, say, from $10 to $100. When there is a high degree of uncertainty about model inputs, it makes little sense to talk about the profit level or the breakeven response rate. • It makes more sense to talk about the probability that profit will have a certain value or the probability that the company will break even.

More Related