Learn smart - Learn online. Upto 88% off on courses for a limited time. View Courses
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 ...
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. 12 Chapter 1 Introduction to Business analytics 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 AB CD EF NCAA t-shirt sales Input egnaR selbairav names used Fixed order cost $750Demand ='Model 5'!$B$10 Variable cost$ 8Discount_price ='Model 5'!$B$7 Selling price$ 18Fixed_order_cost ='Model 5'!$B$4 Discount price$ 6Order ='Model 5'!$B$13 Selling_price ='Model 5'!$B$6 Uncertain variable Variable_cost ='Model 5'!$B$5 0051 dnameD Decision variable 0541 redrO Output variables Costs Fixed cost $750 Variable costs $11,600 Revenues Full-price shirts $26,100 Discount-price shirt s$0 $13,750 tiforP 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, th\�e 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 grap\�hically, how the costs of the book- shelves increase in the next few years. resource CherryOakLabor Required per bookshelf 3030 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 eChapte\ r(s). Editorial review has deemed that any suppressed content does not materia\ lly affect the overall learning experience. Cengage Learning reserves th\ e right to remove additional content at any time if subsequent rights re\ strictions require it. 1-3 Introduction to Spreadsheet Modeling 13 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 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 eChapte\ r(s). Editorial review has deemed that any suppressed content does not materia\ lly affect the overall learning experience. Cengage Learning reserves th\ e right to remove additional content at any time if subsequent rights re\ strictions require it. 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 y\�ears 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 s\�eries (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. 14 Chapter 1 Introduction to Business analytics 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 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 eChapte\ r(s). Editorial review has deemed that any suppressed content does not materia\ lly affect the overall learning experience. Cengage Learning reserves th\ e right to remove additional content at any time if subsequent rights re\ strictions require it. 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 upd\�ate automatically. 1-3 Introduction to Spreadsheet Modeling 15 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 comp\�any 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 cel\�l), 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 na\�mes. 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 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 eChapte\ r(s). Editorial review has deemed that any suppressed content does not materia\ lly affect the overall learning experience. Cengage Learning reserves th\ e right to remove additional content at any time if subsequent rights re\ strictions require it. Figure 1.16 Precedents of Total_revenue Cell 1 2 3 4 5 6 7 8 9 10 11 12 13 E D C B A Quality Sweaters direct mail model Catalog ledoM stupn io f responses Fixed cost of printing $20,000 Response %8 etar Variable cost of printing mailing $0.25 Number of 0008 sesnopser Decision ledoM elbaira vo f revenue, costs, and profit Number latoT 000001 deliam 000,023$ euneveR Fixed cost of printing $20,000 Order inputs Total variable cost of printing mailing $25,000 Average latoT 04$ 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 unc\�ertain. 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-clickin\�g 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 20 Chapter 1 Introduction to Business analytics 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 lates\�t 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 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 eChapte\ r(s). Editorial review has deemed that any suppressed content does not materia\ lly affect the overall learning experience. Cengage Learning reserves th\ e right to remove additional content at any time if subsequent rights re\ strictions require it. 1-3 Introduction to Spreadsheet Modeling 21 Objective To learn how to build in complex logic with IF formulas, to get help about Excel functions, to learn ho\�w 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 AB CDEFGH IJ K Ordering decisionw ith quantity egnaR stnuocsid namesused:2 3 4 5 6 7 8 9 10 11 12 !$ledoM= tsoC B$18 ytitnauQ stupn Id iscount structure 9$E$:5$D $!ledoM= pukooLtsoC Unit cost - seet abletot A thgi rl eastUnit 21$B$!ledoM= dnameD tsoc Regular 6$B$!ledoM= ecirp_revotfeL 00 .42$0 03$ ecirp Leftover 9$B$!ledoM= ytitnauq_redrO 00.32$0001 01$ ecirp 53$J$:53$B$!ledoM= seitilibaborP 52.22$0002 Decision variable 91$B$!ledoM= tiforP 57.12$0003 Order 5$B$!ledoM= ecirp_ralugeR 03.12$0004 0052 ytitnauq 71$B$!ledoM= euneveR Uncertain quantity Units_sold_at_leftover_price =Model!$B$16 Units_sold_at_regular_price=Model!$B$15 0002 dnameD 13 14 15 16 17 18 19Profit model Unit ssolda t regular price2 000 Unit ssoldatl eftover price5 00 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 th\�e 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: 5MAX(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 cl\�ick 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 f x 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 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 eChapte\ r(s). Editorial review has deemed that any suppressed content does not materia\ lly affect the overall learning experience. Cengage Learning reserves th\ e right to remove additional content at any time if subsequent rights re\ strictions require it. 22 Chapter 1 Introduction to Business analytics 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 f\�irst (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 c\�orresponding 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 5 Profit 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 AB CDEFGH IJ Data table of profit as a function of order quantity (along side) an ddemand( along top) 22 23 24 25 26 27 28 29 30 $9,375 5001000 1500 200 02500 3000 3500 400 04 500 500 $3,000 $3,000 $3,000 $3,00 0$3,000$ 3,000 $3,00 0$3,000 $3,000 1000 -$3,000 $7,000 $7,000 $7,000 $7,000 $7,00 0$7,000 $7,000 $7,000 1500 -$9,500 $500 $10,50 0$10,500 $10,50 0$10,500 $10,500 $10,500 $10,500 2000 -$14,500 -$4,500 $5,500 $15,500 $15,50 0$15,500 $15,500 $15,500 $15,500 2500 -$20,625 -$10,625 -$62 5$9,375 $19,375 $19,375 $19,37 5$19,375 $19,375 3000 -$25,250 -$15,250 -$5,250 $4,750 $14,750 $24,750 $24,75 0$24,750 $24,750 3500 -$31,125 -$21,125 -$11,125 -$1,125 $8,875 $18,87 5$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 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 eChapte\ r(s). Editorial review has deemed that any suppressed content does not materia\ lly affect the overall learning experience. Cengage Learning reserves th\ e right to remove additional content at any time if subsequent rights re\ strictions require it. 1-3 Introduction to Spreadsheet Modeling 23 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 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 eChapte\ r(s). Editorial review has deemed that any suppressed content does not materia\ lly affect the overall learning experience. Cengage Learning reserves th\ e right to remove additional content at any time if subsequent rights re\ strictions require it. 33 ABCDEFGH IJ K Model of expected demands34 35 36 37 38 39 40 41 42 43 44 00540004005300030052000200510001005 dnameD 510.040.070.051.052.052.051.050.0520.0 ytilibaborP Sum of probabilities -->1 Orderq uantityExpecte dprofit 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 th e expected pr ofit. 45 46 47 48 49 50 51 4000 $1,350 4500 -$4,150 Expected Profit 12345678 Expected Profit versus Order Qu antity Or der Quantity $6,000 $8,000 $10,00 0 $12,00 0 $14,00 0 -$6,00 0 -$4,00 0 -$2,00 0 $0$2,000 $4,000 9 Figure 1.19 Comparison of Expected Profits 24 Chapter 1 Introduction to Business analytics 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 State\�s. 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 c\�lubs? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 AB C Demand for golf clubs Month Price Demand1 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 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 eChapte\ r(s). Editorial review has deemed that any suppressed content does not materia\ lly affect the overall learning experience. Cengage Learning reserves th\ e right to remove additional content at any time if subsequent rights re\ strictions require it.
Enter the password to open this PDF file:
MyAssignmenthelp.com has proved to be the best cheap assignment help provider in USA. Despite keeping the prices to a minimum, we keep providing top-notch quality college paper writing services for all types of academic papers. Our wide range of services includes report writing help, help with essays, dissertation writing services, coursework help. Students from all corners of the USA prefer to pay our experts for writing top quality assignments. Moreover, it is guaranteed they won't regret for paying our experts to write assignment.
ONLINE TO HELP YOU 24X7
OR GET MONEY BACK!
OUT OF 38983 REVIEWS
Received my assignment before my deadline request, paper was well written. Highly recommend.