lab07_excel_1

Isproblemyou

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: ny. Inputs The decision variable in this worksheet – numbers that you are allowed to change – is the advertising budget of Year1 in Cell C9; and some other decision variables, for examples, vehicle insurance and driver pay. Other inputs variables – which you cannot control – include courier delivery charge and interest rate. Relationship The relationships can be found in formula, for example, Row 10 that relates sales to advertising cost. Outputs The outputs are total expense, total income, and NPV etc… For different amounts of inputs, Excel calculates outputs such as NPV. In this exercise, you have one decision to make: How much should be spent on advertising in Year1 (the number in Cell C9) in order to maximize the profit? Some constrains are applied here: 1. The total advertising budget for all five years is $2000 (Cell H9). 2. The residual money will be allocated equally to the remaining years, years 2 to 5. One Dimensional What‐If Table 1. Year 1 advertising (Cell C9) is initially $200 and NPV (Cell I28) is $16,541.96. In Cell C35, enter the “16541.96”. 2. Replace the values 200 and 500 in Cell C9 and extract the NPVs from Cell I28. And fill in the values in Cells C36 and C37. 3. Select Cell F34, enter “=I28” in the formula box. We want to find the NPV by using a list of different possible choices for Year 1 Advertising. 3 Introduction to Information Systems ISOM2010 4. Select the entire table, i.e. Cells E34 to F37. 5. Select Data What‐If Analysis Data Table… Then, you will see a Data Table dialog pops up. 6. Leave “Row input cell” blank. In “Column input cell”, type “C9”. It means that Excel tries the numbers in the first column of the table (i.e., 200, 500 and 700) in Cell C9 one by one in order to generate NPVs for each number. 7. Click OK button. The table will automatically be filled in the values like what you obtained in Step 2. 4 Introduction to Information Systems ISOM2010 Two Dimensional What‐If Table The table in the last section showed NPV for different choices of Year 1 advertising. But all the numbers in the table were assume as a discount rate of 5%, and a tax rate of 10%. What does happen to the result if these rates changes? Discount rates might be changing because of the inflation. In this section, we will build a two dimensional What‐If table that shows the NPV for different levels of Year 1 Advertising by considering different discount rates assumptions. 1. Select Cell H34, the upper left corner of the two dimensional table, enter “=I128” in the formula box. It means that we want to show the NPV result for every possible combination of discount rate and advertising expense. 2. Select the entire table, i.e. Cells H34 to M37. 3. Select Data What‐If Analysis Data Table… to open the Data Table dialog. For “Row input cell”, type “C15”. It means that Excel tries the numbers in Row 34, i.e. Cells I34 to M34, to Cell C15 one by one. For “Column input cell”, type “C9”. It means that Excel tries the numbers in Column H, i.e. Cells H35 to H37, to Cell C9 one by one. 4. Click OK button. The table should automatically be filled in the NPV values for every possible combination. How will your decision change according to different interest rates? 5...
View Full Document

Ask a homework question - tutors are online