For example the screen shot below shows the Goal Seek inputs we used to set the

For example the screen shot below shows the goal seek

This preview shows page 27 - 32 out of 532 pages.

For example, the screen shot below shows the Goal Seek inputs we used to set the cell for NPV to a val changing the cell for the sales price. We repeated this for the other inputs. Data tables can easily be extended to show multiple outputs for a single input. Simply add an additional colu reference to the desired additional output. Highlight the specified values for the input and highlight all the the output as we show shaded in gray below (be sure to also highlight the cell references above the outputs) Data, Tables, and set "Column input" to the cell reference of the desired input. A B C D E F G 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316
Image of page 27
Two-Way Data Tables: Two Inputs and One Output Example: NPV for Changes in Sales Price and Units Sold % Deviation from Base Case -30% -15% 0% 15% Units Sold Base Case ### 7,000 8,500 10,000 11,500 -30% Sales Price $1.05 ### ### ### ### -15% $1.28 ### ### ### ### 0% $1.50 ### ### ### ### 15% $1.73 ### ### ### ### 30% $1.95 ### ### ### ### Data tables can also be extended to show the output given two inputs. Put one set of input variables in the le column of the data table (shown in a red font below) and the other set of inputs in the top row of the data ta white font); put the cell reference to the output you want (like NPV) in the intersection of the row and colum (we show this in a pale green font). Highlight the range that includes the specified values for the inputs, as s gray shaded region below (this will also highlight the cell reference for the output). Then use the Data, Tabl "Row input" to the cell reference for the inputs shown in the table's row E19 for units sold) and set "Column cell reference for the input shown in the table's column E21 for sales price). % Deviation from NPV cell reference A B C D E F G 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341
Image of page 28
1/25/2013 lars in Thousands) Key Results $1,048 13.79% 12.78% 1.10 3.39 yback 3.80 3 4 13,225 15,209 $1.62 $1.69 $1.14 $1.17 2249.108 $2,317 21456.24 $25,662 3849.249456 $0 14.81% 7.41% orksheet also illustrates ts, and two-way data tables. asures. The model assumes hows the results if the firm s project profitability. H I J K L M N 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
Image of page 29
1147.775 574.275 574.275 0 at End of Year 3 4 $21,456 $25,662 $15,013 $17,782 $2,249 $2,317 $1,148 $574 $3,047 $4,988 $1,219 $1,995 $1,828 $2,993 $1,148 $574 $639 −$256 −$631 $3,849 $0 $0 $0 $0 $2,345 $7,800 3 4 -3054.848661 $4,745 ### $5,328 ### $1,048 kipped if you already know H I J K L M N 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
Image of page 30
hown here. The column for the output range. Data Tables output based on the input. Be la. Every year we have ell and then copy it into the rices for which you want new range, but be sure that none of of sales in the input section of e results section which shows I$15 . Notice that the tan cell this is called the Data Table or the new inputs for price and le dialog box. In Excel 2010, . ell:" box. Here you have to tell t correct in our example--your in that box.
Image of page 31
Image of page 32

You've reached the end of your free preview.

Want to read all 532 pages?

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes