DSS Lab Exercises

Using Excel

1.

GOAL SEEKING:

A. Target goal of $7 EPS, changing Theme park revenue.

B. Save the file as DSS1.xls

C. Add a new column between 1995 and 1994 -- % of sales-1995

D. Calculate the % for each item

E. Target Goal: Net Income of 15%, by reducing General & Administrative Expense

F. Save the file as DSS1.xls

2.

SCENARIOS:

A. Set up three scenarios, by naming them Int1, Int2, and Int3 *

            1. Int1: Investment and Interest Income is 0

            2. Int2: Investment and Interest Income is +$50

            3. Int3: Investment and Interest Income is -$50

B. SHOW each scenario and see the impact of the scenario on Net Income. Go back to the best scenario when you have reviewed each one.

C. Save the file as DSS2.xls

 

[*You have to assign a name to each scenario. Make sure the target to change is the value of Invest/Interest and not the % cell. When finished, review each scenario using SHOW.]

3.

AUDITING:

Trace precedents Net Income (both amount and percentage cells)

4.

SOLVER:

-----------------------------------------------------------------------------------------------------------------------------

NOTE: If SOLVER is not on TOOLS drop-down menu bar, run ADD INS from TOOLS menu option and add it.

-----------------------------------------------------------------------------------------------------------------------------

Click on TOOLS -> SOLVER

Click on HELP and read the basics

 

DECISION 1: TOTAL REVENUE

1. Change the current years Total Revenue to $7,000 by changing all of the components of Revenue.

[Equal to value of 7000, Guess, no constraints click SOLVE]

2. Check the Sensitivity Report.
[NOTE: Click the selection once, then double click]

 

DECISION 2: TOTAL COSTS & EXPENSES

3. Now set the total Costs and Expenses to a value of $5,000 but add the constraint that Consumer Products is => $500, by changing the components of Costs and Expenses. Solve.

4. Check the Answer Report.

[NOTE: Click the selection once, then double click]

5. What changed? There was a constraint so it couldnt go below 500.

 

DECISION 3: INCOME BEFORE TAXES

6. Now Solve for Income Before Income Taxes [EBIT] of $3,000. Change the 9 line items (components) that are NOT formulas. [Why is Operating Income NOT used?]

7. Check the Answer Report.

[NOTE: Click the selection once, then double click]

8. What changed?

9. Save the file as DSS4.xls

5. SENSITIVITY ANALYSIS:

 

Load: Class Lab -- DSS - BUDGET.XLS   {file from O:\cis492}

Go to EXPENSES tab-worksheet

NOTE: Do not use short-cut keys to process these steps. Only use menu options, click, etc.

 

1.      In A31, type in TOTAL and set up a row of totals for each month (column)

2.   Build formulas to calculate the totals for each month in row 31, C to H (Jan. to June).

3.   Add a column for July [I1]

4.   Management believes that Julys expenses will increase by 15% over Januarys for each expense item. In I2, set a percentage that can be used to make the calculation A KEY ASSUMPTION cell.

5.   Build formulas for each expense item for July. HINT: Build one for I4 using absolute reference for the percentage cell [I2], and copy it to the rest.

6.   Build a total cell for July [I31].

7.   Now what happens to total expenses for July if the percentage is actually 22% (answer in both direction of change and actual value of Total Expenses)? 12%? How easy is it to see the effect of this one key assumption change on the results for July?  Extremely easy. It only takes one click! What is the benefit of building formulas and key assumptions for functions such as budgets? Can minimize the budget easily.

8. Save the file as DSS5.xls