Decision Analysis

38-4146-50_BUSINESSANALYTICSDATAANALYSIS_DECMAKING.pdf

Home >Mathematics homework help >Statistics homework help >Decision Analysis

1-3b Cost Projections In the following example, a company wants to project its costs of producing products, given that material and labor costs are likely to increase through time. We build a simple model and then use Excel’s charting capabilities to obtain a graphical image of projected costs.

The model in Figure 1.6 is still not the last word on this example. As shown in later examples, you can create data tables to see how sensitive profit is to the inputs, the demand, and the order quantity. You can also create charts to show results graph- ically. But this is enough for now. You can see that the model in Figure 1.6 is now much more readable and flexible than the original model in Figure 1.2.

1 2 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17 18 19 20 21 22

A B C D E F NCAA t-shirt sales

Input egnaRselbairav names used Fixed order cost $750 Demand ='Model 5'!$B$10 Variable cost $8 Discount_price ='Model 5'!$B$7 Selling price $18 Fixed_order_cost ='Model 5'!$B$4 Discount price $6 Order ='Model 5'!$B$13

Selling_price ='Model 5'!$B$6 Uncertain variable Variable_cost ='Model 5'!$B$5

0051dnameD

Decision variable 0541redrO

Output variables Costs

Fixed cost $750 Variable costs $11,600

Revenues Full-price shirts $26,100 Discount-price shirts $0

$13,750tiforP

Figure 1.6 Model with Category Labels and Color Coding

EXAMPLE

1.2 PROJECTING THE COSTS OF BOOKSHELVES AT WOODWORKS The Woodworks Company produces a variety of custom-designed wood furniture for its customers. One favorite item is a bookshelf, made from either cherry or oak. The company knows that wood prices and labor costs are likely to increase in the future. Table 1.1 shows the number of board-feet and labor hours required for a bookshelf, the current costs per board-foot and labor hour, and the anticipated annual increases in these costs. (The top row indicates that either type of bookshelf requires 30 board-feet of wood and 16 hours of labor.) Build a spreadsheet model that enables the company to experiment with the growth rates in wood and labor costs so that a manager can see, both numerically and graphically, how the costs of the book- shelves increase in the next few years.

resource Cherry Oak Labor

Required per bookshelf 30 30 16

Current unit cost $5.50 $4.30 $18.50

Anticipated annual cost increase

2.4% 1.7% 1.5%

Table 1.1 Input Data for Manufacturing a Bookshelf

Objective To learn good spreadsheet practices, to create copyable formulas with the careful use of relative and absolute addresses, and to create line charts from multiple series of data.

09953_ch01_ptg01_001-036.indd 12 04/03/19 10:51 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

1-3 Introduction to Spreadsheet Modeling 1 3

Solution The completed spreadsheet model appears in Figure 1.7 and in the file Bookshelf Costs Finished.xlsx. You can develop it with the following steps.

1. Inputs. You should usually enter the inputs for a model in the upper-left corner of a work- sheet as you can see in the shaded ranges in Figure 1.7. We have used our standard conven- tion of coloring inputs—the numbers from the statement of the problem—blue. You can develop your own convention, but the input cells should be distinguished in some way. Note that the inputs are grouped logically and are explained with appropriate labels. You should always document your spreadsheet model with descriptive labels. Also, note that by entering inputs explicitly in input cells, you can refer to them later in Excel formulas.

Always enter input values in input cells and then refer to them in Excel formulas. Do not bury input values in formulas.

Figure 1.7 Bookshelf Cost Model

Relative and Absolute Addresses in Formulas

Relative and absolute addresses are used in Excel formulas to facilitate copying. A dollar sign next to a column or row address indicates that the address is absolute and will not change when copying. The lack of a dollar sign indi- cates that the address is relative and will change when copying. After you select a cell in a formula, you can press the F4 key repeatedly to cycle through the relative/absolute possibilities: =B4 (both column and row relative); =$B$4 (both column and row absolute); =B$4 (column relative, row absolute); and =$B4 (column absolute, row relative).

Excel Tip

Always try to organize your spreadsheet model so that you can copy formulas across multiple cells.

2. Design output table. Plan ahead for how you want to structure your outputs. We created a table where there is a row for every year in the future (year 0 corresponds to the current year), there are three columns for projected unit costs (columns B–D), and there are two columns for projected total bookshelf costs (columns E and F). The headings reflect this design. This isn’t the only possible design, but it works well. The important point is that you should have some logical design in mind before you dive in.

3. Projected unit costs of wood. The dollar values in the range B19:F25 are calculated from Excel formulas. Although the logic in this example is straightforward, it is still important to have a strategy in mind before you enter formulas. In particular, you should always try to design your spreadsheet so that you can enter a single formula and then copy it. This saves work and avoids errors. For the costs per board-foot in columns B and C, enter the formula

=B9

in cell B19 and copy it to cell C19. Then enter the general formula

5B19*(11B$10)

in cell B20 and copy it to the range B20:C25. We assume you know the rules for absolute and relative addresses (dollar sign for absolute, no dollar sign for relative), but it takes some planning to use these so that copying is possible. Make sure you understand why we made row 10 absolute but column B relative.

Typing dollar signs in formulas for absolute references is inefficient. Press the F4 key instead.

Press the Fn key and the F4 key (together) on Mac keyboards.

09953_ch01_ptg01_001-036.indd 13 04/03/19 10:51 PM

4 Projected unit labor costs. To calculate projected hourly labor costs, enter the formula

=B13

in cell D19. Then enter the formula

5D19*(11B$14)

in cell D20 and copy it down column D.

5 Projected bookshelf costs. Each bookshelf cost is the sum of its wood and labor costs. By a careful use of absolute and relative addresses, you can enter a single formula for these costs—for all years and for both types of wood. To do this, enter the formula

5B$5*B191B$6*$D19

in cell E19 and copy it to the range E19:F25. The idea here is that the units of wood and labor per bookshelf are always in rows 5 and 6, and the projected unit labor cost is always in column D, but all other references are relative to allow copying.

6 Chart. A chart is a valuable addition to any table of data, especially in the busi- ness world, so charting in Excel is a skill worth mastering. We illustrate some of the possibilities here, but we urge you to experiment with other possibilities on your own. Start by selecting the range E18:F25—yes, including the labels in row 18. Next, click the Line dropdown list on the Insert ribbon and select the Line with Markers type. You instantly get the line chart you want, with one series for Cherry and another for Oak. Also, when the chart is selected (that is, it has a border around it), you see two Chart Tools tabs, Design and Format. There are also three useful buttons to the right of the chart. (These three buttons were intro- duced in Excel 2013, and the two tabs condense the tools in the three tabs from Excel 2007 and 2010.) The most important button is the Select Data button on the Design ribbon. It lets you choose the ranges of the data for charting in case Excel’s default choices aren’t what you want. (The default choices are based on the selected range when you create the chart.)

Click Select Data now to obtain the dialog box in Figure 1.8. On the left, you control the series (one series or multiple series) being charted; on the right, you control the data used for the horizontal axis. By selecting E18:F25, you have the series on the left correct, including the names of these series (Cherry and Oak), but if you didn’t, you could select one of the series and click the Edit button to change it. The data on the horizontal axis are currently the default 1, 2, and so on. To use the data in column A, click the Edit button on the right and select the range A19:A25. Then you can experiment with various formatting options to make the chart even better. For example, we rescaled the vertical axis to start at $300 rather than $0 (right-click any of the numbers on the vertical axis and select Format Axis), and we added a chart title at the top and a title for the horizontal axis at the bottom.

1 4 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

The many chart options are easily accessible from the Chart Tools tabs that are visible when a chart is selected. Don’t be afraid to experiment with them to produce professional-looking charts.

Figure 1.8 Select Data Source Dialog Box

The Select Data Source dialog box in Excel for Mac has a different layout, but

the options are basically the same.

The three buttons to the right of the chart don’t appear in Excel for Mac.

09953_ch01_ptg01_001-036.indd 14 04/03/19 10:51 PM

Using the Model for What-If Questions The model in Figure 1.7 can now be used to answer many what-if questions. In fact, many models are built for the purpose of permitting experimentation with various scenarios. The important point is that the model has been built in such a way that a manager can enter any desired values in the input cells, and all outputs, including the chart, will update automatically.

1-3 Introduction to Spreadsheet Modeling 1 5

1-3c Breakeven Analysis Many business problems require you to find the appropriate level of some activity. This might be the level that maximizes profit (or minimizes cost), or it might be the level that allows a company to break even—no profit, no loss. The following example illustrates a typical breakeven analysis.

EXAMPLE

1.3 BREAKEVEN ANALYSIS AT QUALITY SWEATERS The Quality Sweaters Company sells hand-knitted sweaters. The company is planning to print a catalog of its products and undertake a direct mail campaign. The cost of printing the catalog is $20,000 plus $0.10 per catalog. The cost of mailing each catalog (including postage, order forms, and buying names from a mail-order database) is $0.15. In addition, the company plans to include direct reply envelopes in its mailings and incurs $0.20 in extra costs for each direct mail envelope used by a respondent. The average size of a customer order is $40, and the company’s variable cost per order (primarily due to labor and material costs) averages about 80% of the order’s value—that is, $32. The company plans to mail 100,000 catalogs. It wants to develop a spreadsheet model to answer the following questions:

1. How does a change in the response rate affect profit? 2. For what response rate does the company break even?

Objective To learn how to work with range names, to learn how to answer what-if questions with one-way data tables, to introduce Excel’s Goal Seek tool, and to learn how to document and audit Excel models with cell comments and Excel’s formula auditing tools.

Solution The completed spreadsheet model appears in Figure 1.9. (See the file Breakeven Analysis Finished.xlsx.) First, note the clear layout of the model. The input cells are colored blue, they are separated from the outputs, headings are boldfaced, several headings are indented, numbers are formatted appropriately, and a list to the right spells out all range names we have used. (See the next Excel Tip on how to create this list.) Also, following the convention we use throughout the book, the decision variable (number mailed) is colored red, and the bottom-line output (profit) is colored gray.

Creating Range Names

To create a range name for a range of cells (which could be a single cell), highlight the cell(s), click in the Name Box just to the left of the Formula Bar, and type a range name. Alternatively, if a column (or row) of labels appears next to the cells to be range-named, you can use these labels as the range names. To do this, highlight the labels and the cells to be named (for example, A4:B5 in Figure 1.9), click Create from Selection on the Formulas ribbon, and make sure the appropriate box in the resulting dialog box is checked. The labels in our example are to the left of the cells to be named, so the Left column box should be checked. This is a quick way to create range names, and we did it for all range names in the example. Note that if a label contains any “illegal” range-name characters, such as a space, the illegal characters are converted to underscores.

Excel Tip

Adopt some layout and formatting conventions, even if they differ from ours, to make your spreadsheets readable and easy to follow.

09953_ch01_ptg01_001-036.indd 15 04/03/19 10:51 PM

Figure 1.16 Precedents of Total_revenue Cell 1

2 3 4 5 6 7 8 9

10 11 12 13

EDCBA Quality Sweaters direct mail model

Catalog ledoMstupni of responses Fixed cost of printing $20,000 Response %8etar Variable cost of printing mailing $0.25 Number of 0008sesnopser

Decision ledoMelbairav of revenue, costs, and profit Number latoT000001deliam 000,023$euneveR

Fixed cost of printing $20,000 Order inputs Total variable cost of printing mailing $25,000 Average latoT04$redro variable cost of orders $257,600 Variable cost per order $32.20 Total 006,203$tsoc

004,71$tiforP

1-3d Ordering with Quantity Discounts and Demand Uncertainty In the following example, we again attempt to find the appropriate level of some activity: how much of a product to order when customer demand for the product is uncertain. Two important features of this example are the presence of quantity discounts and the explicit use of probabilities to model uncertain demand.

Formula Auditing Toolbar

The formula auditing toolbar allows you to see dependents of a selected cell (which cells have formulas that reference this cell) or precedents of a given cell (which cells are referenced in this cell’s formula). You can even see dependents or precedents that reside on a different worksheet. In this case, the auditing arrows appear as dashed lines and point to a small spreadsheet icon. By double-clicking the dashed line, you can see a list of dependents or precedents on other worksheets. These tools are especially useful for understanding how someone else’s spreadsheet works.

Excel Tool

2 0 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

EXAMPLE

1.4 ORDERING WITH QUANTITY DISCOUNTS AT SAM’S BOOKSTORE Sam’s Bookstore, with many locations across the United States, places orders for all of the latest books and then distributes them to its individual bookstores. Sam’s needs a model to help it order the appropriate number of any title. For example, Sam’s plans to order a popular new hardback novel, which it will sell for $30. It can purchase any number of this book from the publisher, but due to quantity discounts, the unit cost for all books it orders depends on the number ordered. If the number ordered is less than 1000, the unit cost is $24. After each 1000, the unit cost drops: to $23 for at least 1000 copies; to $22.25 for at least 2000; to $21.75 for at least 3000; and to $21.30 (the lowest possible unit cost) for at least 4000. For example, if Sam’s orders 2500 books, its total cost is $22.25(2500) 5 $55,625. Sam’s is uncertain about the demand for this book—it estimates that demand could be anywhere from 500 to 4500. Also, as with most hardback novels, this one will eventually come out in paperback. Therefore, if Sam’s has any hardbacks left when the paperback comes out, it will put them on sale for $10, at which price it believes all leftovers will be sold. How many copies of this hardback novel should Sam’s order from the publisher?

09953_ch01_ptg01_001-036.indd 20 04/03/19 10:51 PM

1-3 Introduction to Spreadsheet Modeling 2 1

Objective To learn how to build in complex logic with IF formulas, to get help about Excel functions, to learn how to use lookup functions, to see how two-way data tables provide answers to more extensive what-if questions, and to learn about Excel’s SUMPRODUCT function.

Solution The profit model appears in Figure 1.17. (See the file Quantity Discounts Finished.xlsx.) The order quantity and demand in the Order_quantity and Demand cells are trial values. (Comments in these cells are a reminder of this.) You can enter any values in these cells to test the logic of the model. The Order_quantity cell is colored red because the company can choose its value. In contrast, the Demand cell is colored green to indicate that this input value is uncertain and is being treated explicitly as such. Also, a table is used to indicate the quantity discounts cost structure. You can use the following steps to build the model.

1 A B C D E F G H I J K

Ordering decision with quantity egnaRstnuocsid names used: 2 3 4 5 6 7 8 9

10 11 12

!$ledoM=tsoC B$18 ytitnauQstupnI discount structure 9$E$:5$D$!ledoM=pukooLtsoC

Unit cost - see table to tAthgir least Unit 21$B$!ledoM=dnameDtsoc Regular 6$B$!ledoM=ecirp_revotfeL00.42$003$ecirp Leftover 9$B$!ledoM=ytitnauq_redrO00.32$000101$ecirp

53$J$:53$B$!ledoM=seitilibaborP52.22$0002 Decision variable 91$B$!ledoM=tiforP57.12$0003 Order 5$B$!ledoM=ecirp_ralugeR03.12$00040052ytitnauq

71$B$!ledoM=euneveR Uncertain quantity Units_sold_at_leftover_price =Model!$B$16

Units_sold_at_regular_price =Model!$B$150002dnameD 13 14 15 16 17 18 19

Profit model Units sold at regular price 2000 Units sold at leftover price 500

000,56$euneveR 526,55$tsoC 573,9$tiforP

Figure 1.17 Sam’s Profit Model

1. Inputs and range names. Enter all inputs and name the ranges as indicated. The Create from Selection shortcut was used to name all ranges except for CostLookup and Probabilities. For these latter two, you can select the ranges and enter the names in the Name Box—the “manual” method.

2. Revenues. The company can sell only what it has, and it sells any leftovers at the discounted sale price. Therefore, enter the following formulas in cells B15, B16, and B17:

5MIN(Order_quantity,Demand) 5IF(Order_quantity>Demand, Order_quantity-Demand,0) 5Units_sold_at_regular_price*Regular_price1Units_sold_at_leftover_price*Leftover_price

The logic in the first two of these cells is necessary to account correctly for the cases when the order quantity is greater than demand and when it is less than or equal to demand. You could use the following equivalent alternative to the IF function in cell B16:

5 MAX(Order_quantity-Demand,0)

fx Button and Function Library Group

To learn more about an Excel function, click the fx button next to the Formula bar. This is called the Insert Function button, although some people call it the Function Wizard. If there is already a function, such as an IF function, in a cell and you then click the fx button, you will get help on this function. If you select an empty cell and then click the fx button, you can choose a function to get help on. (The same help is available from the Function Library group on the Formulas ribbon.)

Excel Tool The fx button in Excel for Mac opens a Formula Builder pane to the right, but the functionality is essentially the same as in Excel for Windows.

09953_ch01_ptg01_001-036.indd 21 04/03/19 10:51 PM

2 2 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

3. Total ordering cost. Depending on the order quantity, you can find the appropriate unit cost from the unit cost table and multiply it by the order quantity to obtain the total ordering cost. This can be accomplished with a complex nested IF for- mula, but a better way is to use the VLOOKUP function. Specifically, enter the formula

5VLOOKUP(Order_quantity,CostLookup,2)*Order_quantity

in cell B18. The VLOOKUP part of this formula says to compare the order quantity to the first (leftmost) column of the table in the CostLookup range and return the corresponding value in the second column (because the third argument is 2).

VLOOKUP

The VLOOKUP function is one of Excel’s most useful functions. To use it, first create a vertical lookup table, with values to use for comparison listed in the left column of the table and corresponding output values in as many columns to the right as you like. (See the CostLookup range in Figure 1.17 for an example.) Then the VLOOKUP function takes three or four arguments: (1) the value you want to compare to the values in the left column of the table; (2) the lookup table range; (3) the index of the column you want the returned value to come from, where the index of the left column is 1, the index of the next column is 2, and so on; and optionally (4) TRUE (for an approximate match, the default) or FALSE (for an exact match). If you omit the last argument, the values in the left column of the table must be entered in ascending order. (See online help for more details.) If the last argument is TRUE or is omitted, Excel scans down the leftmost column of the table and finds the last entry less than or equal to the first argument. (In this sense, it finds an approximate match.) There is also an HLOOKUP function that works exactly the same way, except that the lookup table is arranged in rows, not columns.

Excel Function

4. Profit. Calculate the profit with the formula

5Revenue-Cost

Two-Way Data Table The next step is to create a two-way data table for profit as a function of the order quantity and demand (see Figure 1.18). To create this table, first enter a link to the profit with the formula 5Profit in cell A22, and enter possible order quantities and possible demands in column A and row 22, respectively. (We used the same values for both order quantity and demand, from 500 to 4500 in increments of 500. This is not necessary—the demand could change in increments of 100 or even 1—but it is reasonable. Perhaps Sam’s is required by the publisher to order in multiples of 500.) Then select Data Table from the What-If Analysis dropdown list on the Data ribbon, and enter the Demand cell as the Row Input cell and the Order_quantity cell as the Column Input cell.

A two-way data table allows you to see how a single output varies as two inputs vary simultaneously.

21 A B C D E F G H I J

Data table of profit as a function of order quantity (along side) and demand (along top) 22 23 24 25 26 27 28 29 30

$9,375 500 1000 1500 2000 2500 3000 3500 4000 4500 500 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000

1000 -$3,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 1500 -$9,500 $500 $10,500 $10,500 $10,500 $10,500 $10,500 $10,500 $10,500 2000 -$14,500 -$4,500 $5,500 $15,500 $15,500 $15,500 $15,500 $15,500 $15,500 2500 -$20,625 -$10,625 -$625 $9,375 $19,375 $19,375 $19,375 $19,375 $19,375 3000 -$25,250 -$15,250 -$5,250 $4,750 $14,750 $24,750 $24,750 $24,750 $24,750 3500 -$31,125 -$21,125 -$11,125 -$1,125 $8,875 $18,875 $28,875 $28,875 $28,875 4000 -$35,200 -$25,200 -$15,200 -$5,200 $4,800 $14,800 $24,800 $34,800 $34,800

31 4500 -$40,850 -$30,850 -$20,850 -$10,850 -$850 $9,150 $19,150 $29,150 $39,150

Figure 1.18 Profit as a Function of Order Quantity and Demand

09953_ch01_ptg01_001-036.indd 22 04/03/19 10:51 PM

1-3 Introduction to Spreadsheet Modeling 2 3

Two-Way Data Table

A two-way data table allows you to see how a single output cell varies as you vary two input cells. Unlike a one- way data table, only a single output cell can be used. To create this type of table, enter a reference to the output cell in the top-left corner of the table, enter possible values of the two inputs below and to the right of this corner cell, and select the entire table. Then select Data Table from the What-If Analysis dropdown on the Data ribbon, and enter references to the cells where the original two input variables live. The Row Input cell corresponds to the values along the top row of the table, and the Column Input cell corresponds to the values along the left-most column of the table. When you click OK, Excel substitutes each pair of input values into these two input cells, recalculates the spreadsheet, and enters the corresponding output value in the table.

Excel Tool

SUMPRODUCT

The SUMPRODUCT function takes two range arguments, which must be exactly the same size and shape, and it sums the products of the corresponding values in these two ranges. For example, the formula 5SUMPRODUCT(A10:B11,E12:F13) is a shortcut for a formula involving the sum of four products: 5A10*E12 1A11*E13 1B10*F12 1B11*F13. This is an extremely useful function, especially when the rang- es involved are large, and it is used repeatedly throughout the book. (Actually, the SUMPRODUCT function can have more than two range arguments, all the same size and shape, but the most common use of SUMPRODUCT is when only two ranges are involved.)

Excel Function

The resulting data table shows that profit depends heavily on both order quantity and demand and (by scanning across rows) how higher demands lead to larger profits. But it is still unclear which order quantity Sam’s should select. Remember that Sam’s can choose the order quantity (the row of the data table), but it has no direct control over demand (the column of the table).

The ordering decision depends not only on which demands are possible but also on which demands are likely to occur. The usual way to express this information is with a set of probabilities that sum to 1. Suppose Sam’s estimates these as the values in row 35 of Figure 1.19. These estimates are probably based on other similar books it has sold in the past. The most likely demands are 2000 and 2500, with other values on both sides less likely. You can use these probabilities to find an expected profit for each order quantity. This expected profit is a weighted average of the profits in any row in the data table, using the probabilities as the weights. The easiest way to do this is to enter the formula

5SUMPRODUCT(B23:J23,Probabilities)

in cell B38 and copy it down to cell B46. You can also create a chart of these expected profits, as shown in Figure 1.19. (Excel refers to these as column charts. The height of each bar is the expected profit for that particular order quantity.)

This is actually a preview of decision making under uncertainty. To calculate an expected profit, you multiply each profit by its probability and add the products.

The largest of the expected profits, $12,250, corresponds to an order quantity of 2000, so we would recommend that Sam’s order 2000 copies of the book. This does not guarantee that Sam’s will make a profit of $12,250—the actual profit depends on the eventual demand—but it represents a reasonable way to proceed in the face of uncertain demand.

09953_ch01_ptg01_001-036.indd 23 04/03/19 10:51 PM

33 A B C D E F G H I J K

Model of expected demands 34 35 36 37 38 39 40 41 42 43 44

00540004005300030052000200510001005dnameD 510.040.070.051.052.052.051.050.0520.0ytilibaborP

Sum of probabilities --> 1 Order quantity Expected profit

500 $3,000 1000 $6,750 1500 $9,500 2000 $12,250 2500 $11,375 3000 $9,500 3500 $4 875

Order 2000 to maximize the expected profit.

45 46 47 48 49 50 51

4000 $1,350 4500 -$4,150 Ex

pe ct

ed P

ro fit

1 2 3 4 5 6 7 8

Expected Profit versus Order Quantity

Order Quantity

$6,000 $8,000

$10,000 $12,000 $14,000

-$6,000 -$4,000 -$2,000

$0 $2,000 $4,000

Figure 1.19 Comparison of Expected Profits

2 4 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

1-3e Estimating the Relationship Between Price and Demand The following example illustrates a very important modeling concept: estimating relation- ships between variables by curve fitting. The ideas can be illustrated at a relatively low level by taking advantage of some useful Excel tools.

EXAMPLE

1.5 ESTIMATING SENSITIVITY OF DEMAND TO PRICE AT THE LINKS COMPANY The Links Company sells its golf clubs at golf outlet stores throughout the United States. The company knows that demand for its clubs varies considerably with price. In fact, the price has varied over the past 12 months, and the demand at each price level has been observed. The data are in the data sheet of the file Golf Club Demand.xlsx (see Figure 1.20). For example, during month 12, when the price was $390, 6800 sets of clubs were sold. (The demands in column C are in hundreds of units. The cell comment in cell C3 is a reminder of this.) The company wants to estimate the relationship between demand and price and then use this estimated relationship to answer the following questions:

1. Assuming the unit cost of producing a set of clubs is $250 and the price must be a multiple of $10, what price should Links charge to maximize its profit?

2. How does the optimal price depend on the unit cost of producing a set of clubs?

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15

A B C Demand for golf clubs

Month Price Demand 1 450 45 2 300 103 3 440 49 4 360 86 5 290 125 6 450 52 7 340 87 8 370 68 9 500 45

10 490 44 11 430 58 12 390 68

Figure 1.20 Demand and Price Data for Golf Clubs

09953_ch01_ptg01_001-036.indd 24 04/03/19 10:51 PM