ltcorner
ovalaqua
topbannr

SPREADSHEET MODELING EXERCISES

How To Build Real-world Financial Models In Excel

Spreadsheet Modeling Exercises are step-by-step instructions on how to build real-world financial models in Excel. The idea is for you to start with a blank spreadsheet, follow complete user-friendly instructions, and build your own spreadsheet model in 30 to 60 minutes. This hands-on, active learning approach to building the entire model yourself is the best way to learn and remember financial models. You can see from the examples given below, these financial models are not toys, but real-world applications. The 19 exercises in Spreadsheet Modeling Exercises cover every important, quantitative model in Bodie and Merton. There is a tight connection between each financial concept and the corresponding spreadsheet model. If you already know how to enter a formula in Excel and how to copy the contents of one spreadsheet cell to another, then you are ready to use Spreadsheet Modeling Exercises. Everything else is fully explained! Each exercise follows the following format: (1) a problem is defined, (2) a solution strategy is sketched, (3) Excel screen shots show what the completed spreadsheet will look like, (4) step-by-step instructions explain exactly how to build the spreadsheet, and (5) key results are discussed. Each exercise starts with an easier, more basic financial model and then extends the model to a more advanced version to demonstrate the full power of your spreadsheet model.

 
How To Get Started

If you have Microsoft Word 97 or higher:

1. Click on: Spreadsheet Modeling Exercises

2. Select Open it and click on OK to launch

Microsoft Word with the opening screen

show on the left.

If you have Corel WordPerfect 8.0 or higher or Lotus WordPro Millennium Edition (Release 9) or higher:

1. Launch your word processor (either

WordPerfect or WordPro).

2. Open the following file on the Finance

Center CD-ROM:

\Spreadsheet Modeling Exercises\Bodie-Merton.doc

This is a Word 97 file, but your word processor knows how to open it.
What You Can Do

(1.) Corporate Financial Planning. Create a complete, linked system of historical and forecasted financial statements:

  • key assumptions
  • income statement
  • balance sheet
  • cash flow
  • percent of sales
  • financial ratios

Assess the sensitivity of your forecasted profits to changes in key assumptions!
(2.) Life-Cycle Financial Planning. Plan your personal savings and consumption over a lifetime. This detailed, realistic model includes:
  • Starting salary
  • Inflation rate
  • Real growth rate in salary
  • Real growth rate in consumption
  • Federal, state, and social security taxes
  • Number of working and retirement years

Explore your lifestyle over a lifetime and strategies to (legally) minimize taxes!
(3) Black-Scholes Option Pricing Model. Create an interactive model of the Merton dividends version of the Black-Scholes option pricing model
  • Click on up or down arrows to determine how an option price changes with:
  • higher or lower volatility
  • higher or lower time-to-maturity
  • higher or lower dividends
  • and more!
(4.) Portfolio Optimization. Determine the optimal portfolio to invest in.
  • Trades-off risk and return possibilities
  • Calc. the optimal combination of risky assets
  • Determine how aggressively to exploit underpriced or overpriced assets
  • Works with broad asset classes (e.g., domestic stocks, foreign stocks, bonds, real estate, etc.)
  • Can be extended to any number of risky assets

Directly illustrates the benefits of diversification.

And 15 more financial models!