The Certified Financial Modeling Specialist (CFMS) Program requires participants to complete a Valuation Project in order to graduate from the program. This project is worth 100 points and accounts for 50% of the total grade within the program. Non-submission of this strategic plan will result in an automatic failing grade for the CFMS program. You will receive details for the Strategic Business Plan after registration. If you have any questions, please email us at Team@FinanceTraining.com.
Once you have completed this project, please send it and your request to take your desired exam date at least 2 weeks before the scheduled exam date. To schedule yourself for an exam date, this project must be received, completed, at least 2 weeks before the exam date. For example, to take the October 10th exam date, this project must be received (as well as a request to take the exam) no later than September 26th. When you’ve completed the project below, you can schedule your examination on the Examination Dates page here.
Putting your learning into practice. You will have access to the project template after registration is complete.
The course project involves the building of a simple financial model with the following step-by-step components:
- We will build financial projections and a valuation model for Cisco Systems, a publicly-traded supplier of networking solutions.
- 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.
- 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 Q3 2010 and Q4 2010, make sure the numbers for all of FY2010 match Cisco’s reported numbers. (Hint: Financial results can be found at http://investor.cisco.com/results.cfm)
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-S tab, 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.
- 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).
- If you fill out everything correctly, the Valuation tab automatically calculates the fair market value per share using the DCF method.
- 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.
Tips: Make sure you enter data for the relevant quarter only and not for nine-months or the full-year (Answer: You can do this by taking 9-month numbers and subtract the first two quarters to get numbers for just the third quarter. Similarly, you can get numbers for just the fourth quarter.) Remember, there are four quarters in a Fiscal Year and a Fiscal Year need not start on Jan 1 but can be any date of the company’s choosing. The Fiscal Year need not coincide with a Calendar Year. Make sure your numbers are consistent with Cisco’s reported numbers – check for typos and math errors.