PROJECT DETAILS
Putting your learning into practice:
The Course Project involves the building of a simple financial model with the following step-by-step components:
i. We will build financial projections and a valuation model for Cisco Systems, a publicly-traded supplier of networking solutions.
ii. We have provided an Excel Template (available for download on the CFMS student page) that contains financial data - Income Statement, Balance Sheet and Cash Flow - for Cisco's Fiscal Years 2006, 2007, 2008, 2009 and the first two quarters of 2010.
iii. The Project Template does not contain data for the third and fourth quarters of FY2010. Your first task is to obtain quarterly financial data - Income Statement, Balance Sheet and Cash Flow - for the third and fourth quarters of Cisco's Fiscal Year 2010 from Cisco's website. Only use GAAP numbers.
Enter these results in the "yellow" highlighted areas on each relevant tab in the Project Template. Once you enter data for 3Q2010 and 4Q2010, make sure the numbers for all of FY2010 match Cisco's reported numbers.
iv. Next, move to cell A51 on the I-S tab (Income Statement) of the Project Template. You will see the "normalized" section where all Income Statement line-items are expressed as a % of Total Net Sales.
Once you enter I-S data for 3Q2010 and 4Q2010, you will see % values for those periods also.
Eyeball these numbers to get a sense of Cisco's revenue growth from year to year. This is an important parameter which we will use to project revenue growth. Based on these numbers,we have picked estimates to develop our projections. These estimates are in column M of the I-Stab, starting with cell M51.
Use these estimates to build out 5-year projections (FY2011 - FY2015) for the Income Statement (yellow highlighted area starting in cell M6 of the I-S tab). We have started you off with projections for Net Sales.
v. Once you have successfully completed I-S projections, move to the first tab - Valuation. Fill out the cells marked in yellow (Hint: link them to relevant cells in the I-S tab and the C-F tab. Depreciation and Amortization can best be obtained from the Cash Flow statement).
vi. If you fill out everything correctly, the Valuation tab automatically calculates the fair market value per share using the DCF method.
vii. Write a brief commentary (100 words) comparing Cisco's current share price to your projected Equity Value per share, and make a recommendation on buying or selling Cisco shares.