EXERCISE D(what-if & goalseek Analysis + Generating Two Dimensional Table)

1) A special case of threshold analysis is the breakeven point, usually defined as the sales volume atwhich contribution to profit and overhead equals fixed cost. In the software model, the breakevenpoint is the value for Units Sold when Net Cash Flow is zero. Using Goal Seek (not shown here),the breakeven point is found to be 571.

Consider the following data stored in a worksheet:

(a) Calculate Break Even Point for the above data using Goal Seek data tool of the excel sheet(b) Check whether the Breakeven point calculated using the Goal Seek tool satisfies the given data using what-if analysis

2) Using Goal seek calculate IRR for the investment given below:

3) Table option of the Data Tools may be used to generate a two dimensional table (for ranges of values for two model inputs). Useful for examining possible relationships and threshold combinations.Consider the model generated in part (1) of this exercise. Generate a table using the breakeven relationship to examine how the cash flow changes as you vary the variables Unit Price & Unit Sold.

-------