Fundamental of Finance: Course Outline
EXCEL IN FINANCIAL MODELLING
HOMEWORK
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)
Assumptions
Revenues t1
90.000
Revenues Growth Rate t1-t5
0%
Cost of Good Sold (% on revenues)
-45%
Services (% on revenues)
-25%
Labor Costs
Cost per employee
-40
Number of employees
250
General Expenses
-7000
D&A % (on CAPEX)
-10%
Cost of Debt
5%
Tax Rate
-30%
Days Sales Outstanding
60
Inventory Turnover Days (on COGS)
30
Days Payable Outstanding (on COGS)
90
CAPEX per Yea
-6000
Equity Investment in t1
-3000
Long Term Debt (@ the end t1)
-7500
Reimbursement period (years)
10
Income Statement
1
Revenues
90.000
growth
Raw Materials
(40.500)
Services
(22.500)
Gross Profit
27.000
Labor Costs
(10.000)
General Expenses
(7.000)
EBITDA
10.000
D&A
(300)
EBIT
9.700
Interests
(375)
EBT
9.325
Taxes
(2.798)
Net Profit/Loss
6.528
Balance Sheet
1
Receivables
14.795
Inventory
3.329
Payables
(9.986)
Net Working Capital
8.137
Fixed Assets
6.000
Cumulated D&A
(300)
Net Fixed Assets
5.700
NET INVESTED CAPITAL
13.837
Capital & Reserves
(3.000)
Net Profit / Loss
(6.528)
Equity
(9.528)
Cash / ST Debt
3.191
Long Term Debt
(7.500)
Net Debt
(4.309)