Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

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

1 answer below »
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)
Answered Same Day Jul 07, 2021

Solution

Neenisha answered on Jul 10 2021
147 Votes
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 Labo
    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 Cu
ent Assets
        Cu
ent 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 Cu
ent Liabilities
        Cu
ent 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 /...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here