FIN 4200: Financial Modelling
FIN 4200: Financial Modelling |
|||||||
|
|
|
|
|
|
|
|
Files from the 4th edition of Financial Analysis with Microsoft Excel.
My Excel Blog - I started a blog in the summer of 2007 where I ocassionally post advanced Excel tips.
Dr. Mayes' Excel Files - This is where I put generally useful Excel files that aren't for a specific class.
PwC EdgarScan - This is a better source of SEC filings than the no longer free FreeEdgar. Download Excel versions of company financial statements directly.
OzGrid.Com - This is a large site that has an Excel discussion forum, free tips, an e-mail newsletter, and hundreds of Excel add-ins.
Excel Tips - This site provides thousands of tips, an Excel tutorial, and even an e-mail newsletter.
Excel Experts E-Letter -- An electronic newsletter which explains advanced Excel formulas and programming.
FrontLine Systems - This is the company that makes Excel's Solver add-in. You can get a tutorial on how to use the solver at their Web site among other things.
The Association of Statistics Specialists Using Microsoft Excel
Spreadsheet Engineering Research Project - A project at the Tuck School at Dartmouth to improve spreadsheet design and develop best practices.
Spreadsheet Errors in the News - You folks always seem to think I'm crazy when I rant about testing the accuracy of your formulas and whole spreadsheets. Here's some recent news stories that show why.
Microsoft's Excel Template Gallery - This site contains a bunch of freely downloadable templates for both Word and Excel. This link is to the financial templates, but you can get others at the Template Gallery Home.
Recovery Tips for Excel Files - If you've got a corrupted Excel 2003 (or 2002) file, check this article from Microsoft's Knowledge Base.
Microsoft Office 2000/Visual Basic Programmer's Guide - An online book from Microsoft. Its a programmer's guide, not a "learn to program" kind of book, but if you already know how to program in VBA its a good reference.
Microsoft's Excel Viewer - This will be useful to those without a copy of Excel who wish to view the spreadsheet files here.
Note: to download any of these files you should click on the link and then choose to "Save to Disk" when you receive a message from your browser (make sure that you remember where you saved it). If you have a version of Excel on your PC, the file may open automatically.
If you need to use a prior version of Excel, open the spreadsheet in Excel (in the lab) and then choose File Save As and select the appropriate format.
A Note About Macros: All of the workbooks available here need to use macros to function properly. If you receive a message asking whether or not to enable macros, always choose "Yes" to enable them. If you choose "No" the worksheets will not function properly. Also note that if you have Excel's security set to high, the macros will not work. You will need to go to Tools Macros Security and set the security level to medium.
Excel 2003: Answers to Each Chapter's Homework for Spring 08 (files are password protected):
| Chapter 1 | Chapter 2 | Chapter 3 | Chapter 4 | Chapter 5 |
| Chapter 7 | Chapter 8 | Chapter 9 | Chapter 10 | Chapter 11 |
| Chapter 12 |
Excel 2007: Answers to Each Chapter's Homework for Spring 08 (files are password protected):
| Chapter 1 | Chapter 2 | Chapter 3 | Chapter 4 | Chapter 5 |
| Chapter 7 | Chapter 8 | Chapter 9 | Chapter 10 | Chapter 11 |
| Chapter 12 |
Questions and Answers to Supplemental Problems:
These are some very old problems that I have made available for you to practice with. They are not required to be turned in.
Array Formulas - This example shows how I would use an array formula to simplify a spreadsheet for calculating a weighted average of student grades. It takes you step by step through alternative ways of doing this so as to demonstrate the benefits of array formulas.
Array Formula Examples - This workbook shows several examples of the use of array formulas with complete examples of how they work. It is not as detailed as the above file, but it shows several additional interesting uses for array formulas.
Chart Tricks - This workbook shows a couple of charting tricks. Specifically, it shows how to use named ranges in a chart so that you can easily change the data series in a chart, also how to put multiple charts into one chart, and how to shade periods of recession when charting economic time series.
Portfolio Optimization - This is the workbook that I used in class to demonstrate how to optimize a portfolio and chart the efficient frontier. The last worksheet also shows how the capital market line appears when we add in a risk-free asset. Note: you will need a copy of FameFncs.xla to use this workbook. If your copy is located anywhere other than on your A:\ drive, you will have to go to Edit Links to change the reference.
Portfolio Optimization part two - This is the file that I created on 29 November 2001 in class. The above file is better, as it shows how to create an efficient frontier, but I was asked to post this one as well.
ExcelSim 2003 - This is the Excel add-in program I've written to perform Monte Carlo simulation. Download this file and then open it in Excel 97/2000/2002 (XP) to be able to run a simulation. You may also need the documentation file and the examples file. Note that you will need the Adobe Acrobat Reader to read the documentation. A truly excellent introduction to modeling uncertainty with Monte Carlo methods is available from PriceWaterhouseCoopers is available here in PDF format. That same document provides an excellent discussion of many probability distributions and when they should be used. A good list of available articles from a wide variety of fields is available from Decisioneering, Inc (makers of the Crystal Ball Excel add-in) here.
Roll the Dice! - This is a little file that does a graphical simulation of tossing a pair of dice. It isn't very useful, but its an interesting way to show how to use the IF function, conditional formatting, and concatenation in Excel.
FameFncs.xla (FameFncs.xlam for Excel 2007)- This is the set of macros that came with your book. Download this file to your hard drive and save it somewhere that you can find it. Now, open Excel and go to Tools --> Add-ins, click the Browse button, and then nagivate to the directory where you saved the file. Click on the file and then the OK button, and it will now open automatically every time you start Excel. Please see the FAMEFncs function dictionary for a description of each of the functions.
Oil Prices vs Rig Count - This example demonstrates regression analysis in Excel. I show how to use the regression tool in the Analysis ToolPak and how to use the Solver to do regression.
GPA and Other Regression Data - This file has several example data sets that we will use to demonstrate regression in class.