Financial modelling - 5-years business plan based on assumptions + sensitivity analysis +...

Question

Financial modelling - 5-years business plan based on assumptions + sensitivity analysis + miscellaneous comments on the company performance (Excel Spreadsheets - 2)

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 20.000 K€ 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) b. 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 correct 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 Year -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)

financial-modellinghomework-ziy32c5v.docx homework-finance-vrikozn0.xlsx

Neenisha · Accepted Answer

Business Plan
	MOBRA SpA - Economic and Financial Plan
	Amount in €k, except %
			ACTUAL		FORECAST		FORECAST		FORECAST		FORECAST		FORECAST
		INCOME STATEMENT	t0	%	t+1	%	t+2	%	t+3	%	t+4	%	t+5	%
	ce1	Revenues	90000	100%	91800	100%	93636	100%	95508.72	100%	97418.8944	100%	99367.27229	100%
	ce2	Consumptions	-40500	-45%	-41310	-45%	-42136.2	-45%	-42978.924	-45%	-43838.50248	-45%	-44715.27253	-45%
	ce4	Direct Services	-22500	-25%	-22950	-25%	-23409	-25%	-23877.18	-25%	-24354.7236	-25%	-24841.81807	-25%
	ce13	Direct Labor	-10000	-11%	-10200	-11%	-10404	-11%	-10612.08	-11%	-10824.3216	-11%	-11040.80803	-11%
		Gross Profit	17000	19%	17340	19%	17686.8	19%	18040.536	19%	18401.34672	19%	18769.37365	19%
	ce3	Indirect Labor
	ce5	Leasing
	ce6	General Expenses	-7000	-8%	-7000	-8%	-7000	-7%	-7000	-7%	-7000	-7%	-7000	-7%
		EBITDA	10000	11%	10340	11%	10686.8	11%	11040.536	12%	11401.34672	12%	11769.37365	12%
	ce7	D&A - Tangible	-300	-0.3%	-270	-0.3%	-843	-0.9%	-1358.7	-1.4%	-1822.83	-1.9%	-2240.547	-2.3%
	ce8	D&A - Intangible
	ce9	Receivables - writeoffs
		EBIT	9700	11%	10070	11%	9843.8	11%	9681.836	10%	9578.51672	10%	9528.826654	10%
	ce10	Non Operating Income/Losses
	ce11	Interests	-375	-0.4%	-337.5	-0.4%	-300	-0.3%	-262.5	-0.3%	-225	-0.2%	-187.5	-0.2%
		EBT	9325	10%	9732.5	11%	9543.8	10%	9419.336	10%	9353.51672	10%	9341.326654	9%
	ce12	Taxes	-2797.5	-3%	-2919.75	-3%	-2863.14	-3%	-2825.8008	-3%	-2806.055016	-3%	-2802.397996	-3%
		Net Income	6527.5	7%	6812.75	7%	6680.66	7%	6593.5352	7%	6547.461704	7%	6538.928658	7%
		Check
			ACTUAL		FORECAST		FORECAST		FORECAST		FORECAST		FORECAST
		BALANCE SHEET	t0	%	t+1	%	t+2	%	t+3	%	t+4	%	t+5	%
	sp1	Receivables	14794.52055	16%	15090.41096	16%	15392.21918	16%	15700.06356	16%	16014.06483	16%	16334.34613	16%
	sp2	Inventory	3328.767123	4%	3395.342466	4%	3463.249315	4%	3532.514301	4%	3603.164587	4%	3675.227879	4%
	sp3	Other Current Assets
		Current Assets	18123.28767	20%	18485.75342	20%	18855.46849	20%	19232.57786	20%	19617.22942	20%	20009.57401	20%
	sp4	Payables	-9986.30137	-11%	-10186.0274	-11%	-10389.74795	-11%	-10597.5429	-11%	-10809.49376	-11%	-11025.68364	-11%
	sp6	Fiscal Liabilities
	sp7	Other Current Liabilities
		Current Liabilities	-9986.30137	-11%	-10186.0274	-11%	-10389.74795	-11%	-10597.5429	-11%	-10809.49376	-11%	-11025.68364	-11%
		NET WORKING CAPITAL	8136.986301	9%	8299.726027	9%	8465.720548	9%	8635.034959	9%	8807.735658	9%	8983.890371	9%
		Tangible Fixed Assets	5700	6%	11430	12%	16587	18%	21228.3	22%	25405.47	26%	29164.923	29%
	sp9.1	Gross				0%		0%		0%		0%		0%
	sp9.2	Funds				0%		0%		0%		0%		0%
		Gross	6000	7%	12000	13%	18000	19%	24000	25%	30000	31%	36000	36%
	sp10.1	Gross				0%		0%		0%		0%		0%
	sp10.2	Funds				0%		0%		0%		0%		0%
	sp11	Funds	-300	-0.3%	-570	-0.6%	-1413	-1.5%	-2771.7	-2.9%	-4594.53	-4.7%	-6835.077	-6.9%
		Net Fixed Assets	5700	6%	11430	12%	16587	18%	21228.3	22%	25405.47	26%	29164.923	29%
	sp12	Funds and Provisions
		NET INVESTED CAPITAL	13836.9863	15%	19729.72603	21%	25052.72055	27%	29863.33496	31%	34213.20566	35%	38148.81337	38%
	sp13	Share Capital
	sp14	Reserves	-3000	-3%	-9527.5	-10%	-16340.25	-17%	-23020.91	-24%	-29614.4452	-30%	-36161.9069	-36%
	sp15	Net Profit / Loss	-6527.5	-7%	-6812.75	-7%	-6680.66	-7%	-6593.5352	-7%	-6547.461704	-7%	-6538.928658	-7%
		EQUITY	-9527.5	-11%	-16340.

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...

Answer To: Fundamental of Finance: Course Outline EXCEL IN FINANCIAL MODELLING HOMEWORK Follow the assumptions...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment