BusM 401
Prof. Nadauld
Computer Lab #2
Financial Forecasting in Excel
Introduction:
This computer lab will be mostly selfguided.
You can work at your own pace, so
those who have a lot of experience with Excel might finish relatively quickly, and those who have
less experience might take longer.
By the end, everyone should know the basics of financial
forecasting in Excel.
If you need help, ask Prof. Nadauld, ask the TA, or consult the Excel help
function.
Getting Started:
Download today’s lab from Blackboard.
Under computer labs select Computer
lab #2.
Answers to the problems are given on the last sheet in the workbook, so you can check your
answers.
A completed model is also available on Blackboard, but I recommend not looking at it
until you’ve tried your best to do the work on your own.
Instructions:
Part A:
Create pro forma financial statements.
This spreadsheet is set up so that yellow cells contain numbers and white cells contain formulas.
Follow the steps below to prepare pro formas for 2007, assuming that New England Corp. will
make up any funding shortfall with longterm debt, and will use any funding surplus to pay down
longterm debt (i.e., let longterm debt be the plug figure).
1.
As a starting point, assume that sales growth in 2007 will be equal to the average sales growth
for 2005 and 2006.
Enter the formula for this assumption in the Key Assumptions section, and then
enter the formula for projected sales in 2007.
2.
For all financial statement items that would be expected to vary with sales, use the historical
average over the past three years of the ratio of that item to sales as a projection of the percentage of
sales for 2007.
I suggest that you enter these percentages in the column on the right “Assumed % of
Sales”.
(Hint:
If you enter your formula correctly in one cell, locking in the appropriate references,
then you can simply copy and paste the formula to other cells.)
Having this column allows you to
return and change the assumption for these items later on.
For now, assume that depreciation
expense, Gross PP&E, and shortterm debt all vary as a percentage of sales.
3.
Fill in the 2007 forecast for each item that would be expected to vary with sales.
(Again, if you
enter the formula correctly, you can copy and paste it to other cells.)
4.
Fill in all cells in the 2007 forecast that are just formulas (e.g., pretax income is just EBIT
interest expense).
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
This is the end of the preview.
Sign up
to
access the rest of the document.
 Winter '10
 ToddMitton
 Interest, Longterm Debt, Sales Growth, New England Corp.

Click to edit the document details