{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}


Computer_lab_2_instructions - BusM 401 Prof Nadauld...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
BusM 401 Prof. Nadauld Computer Lab #2 Financial Forecasting in Excel Introduction: This computer lab will be mostly self-guided. 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 long-term debt, and will use any funding surplus to pay down long-term debt (i.e., let long-term 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 short-term 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., pre-tax income is just EBIT- interest expense).
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}