Fundamental of Finance: Course Outline
EXCEL IN FINANCIAL MODELLING
Follow the assumptions to build a Business Plan on 5 years (year 2 to 6). Please provide the answers using a text box on the left of the model, indicating the specific question that you are answering.
1. Complete a forecast for the Income statement, Balance sheet and Cash flow statement. Attached the Financial Statement for year 1. If you are not able to close the balance sheet and the cash flow statement DO NOT WORRY, it will not be subtracted from the final mark.
2. Provide in 5 lines a comment on the company performance: would you buy it? Why?
3. Once completed the financial plan, perform a sensitivity analysis on Cumulated Free Cash Flow from year 2 to 6 changing the Revenues Growth Rate (from -2% to +2%; 1% step) and the Days Sales Outstanding (from 40 to 80 days; 10 days step).
4. Provide in 5 lines a comment on the Sensitivity Analysis. How does the 2 input variables impact the Free Cash Flow? Why?
5. Perform a “Goal Seek” sensitivity: what is the revenue growth rate needed to obtain a cumulated
free cash flow of XXXXXXXXXXK€ from Year 2 to year 6)?
6. Perform a “Scenario Manager” sensitivity. Create a Scenario Manager Report creating the following scenarios:
a. BASE: Revenue growth (0%), Cost of Good Sold (-45%), Days Sales Outstanding (60)
. BEST: Revenue growth (3%), Cost of Good Sold (-43%), Days Sales Outstanding (40)
c. WORST: create your own hypothesis
7. Provide in 5 lines a comment on the SCENARIO MANAGER Sensitivity Analysis.
If you are not able to perform the sensitivity on the model, you should prepare another data table on a separate sheet, creating a simple example to show that you understood the data table tool.
If you have some doubts on the Financial Plan, make your own EXPLICIT assumptions (insert a comment) into the file motivating your choice, then go on with the exercise.
The final mark will consider:
· The co
ect result of the model (excluding the Debt/Equity Part and the Cash Flow from financing Activities)
· Respect of the Basic rules of Financial Modelling
· Model Structure
· The relevancy of your comments
· Result of the sensitivity analyses.
Nb : Delete the unused rows in the attached excel. This is a template used by way of example (in class).
Data in €k (No VAT, Inflation 2%, Dividends 20% of the net profit)
Revenues Growth Rate t1-t5
Cost of Good Sold (% on revenues)
Services (% on revenues)
Cost per employee
Number of employees
D&A % (on CAPEX)
Cost of Debt
Days Sales Outstanding
Inventory Turnover Days (on COGS)
Days Payable Outstanding (on COGS)
CAPEX per Yea
Equity Investment in t1
Long Term Debt (@ the end t1)
Reimbursement period (years)
Net Working Capital
Net Fixed Assets
NET INVESTED CAPITAL
Capital & Reserves
Net Profit / Loss
Cash / ST Debt
Long Term Debt