财务建模和商业计划代写 Financial Statement Analysis代写

Financial Modelling and Business Plan

Final Test/Exam

财务建模和商业计划代写 Case Piece of Pie, PLC Pro-forma Data is presented on the annexed pages, regarding an investment project that led to the constitution

Time Lenght: 120 (90+30) m

Case Piece of Pie, PLC

Pro-forma Data is presented on the annexed pages, regarding an investment project that led to the constitution of “Piece of Pie, PLC” in Year 0. On that same year there was no business activity. In the project’s analysis 3 Years are going to be considered (Years 1, 2 , and 3), therefore on the following year (Year 4) there will be the respective divesture, that should be made at book value, so no capital gains or losses are expected from that operation. 财务建模和商业计划代写

Capital Expenditures (CAPEX) are going to be depreciated in 5 years (i.e. at a 20% depreciation rate). That depreciation will only take place in the operating years (i.e. 1, 2 and 3) and during that period for the CAPEX that will occur within those years, even in the years when those expenses are incurred. However, you are only asked to present the accounts for the first two years of operation of the project i.e. Years 1 and 2).

In the annexed tables Year 0 CAPEX, as well as working capital needs are already determined, the impact of VAT is also included (average VAT rate = 23%). The income statements and the balance sheets presented are incomplete (you must

complete them), they were made using escalated prices (i.e. including inflation). The income tax rate is 25%, and income taxes are paid on the following year. The company is not expected to pay any dividends.

For the funding of the project a mix of debt and equity is going to be used. 财务建模和商业计划代写

The Bank loan contracted at year 0 and the Equity Capital (provided by the shareholders) are already reflected on that year balance sheet. Additional Equity Capital or Medium Term Debt is not expected for the following years. However, if the company has additional financial needs there is a banking credit short term line available with a 5% interest rate (stamp duty tax included), this loan must be repaid in the following year.

While possible excess of funds may be invested in short term instruments with a 1% rate of return.

The medium term bank loan amounts to 240.000€ and is going to be repaid by 3 equal principal repayment annuities, of 80.000€ each, in Years1, 2 and 3 of the project. The interest payments will be paid annually at a 4% rate, these payments are determined according to the usual rule of calculating interest on the amount of capital that is still owed at the beginning of each period. Therefore, this loan will be entirely repaid within the operation stage of the project.

a) 财务建模和商业计划代写

Please prepare a Repayment Schedule/Cash flow Table for the before mentioned Medium Term Bank Loan (not forgetting the tax shields from the interest payments) and determine the respective Present Value of the Loan, assuming that the 4% interest rate on this loan is not a normal on-going market rate, which is rather 5%. You may use the following table form for that purpose.

Years	1	2	3
Principal at BOY (Beginning of Year)
Interest Rate	4%	4%	4%
Interest Payments
Repayment of Principal
Installment = Interest + Repayment
Principal at EOY (End of Year)
Tax Shield from Interest Payments (t=25%)
Net Debt Service (after taxes)

(PV of Loan Calculation and other auxiliary calculations:)

b)

Please present the Financing Plan just for Years 1 and 2 of the Project. For that purpose you must use the information already presented in the projected proforma income statements and balance sheets. Liquidity (Cash and Banking Accounts balances) should correspond to 2% of Sales and 5% of Capex. You should present your answer in the annexed tables of the following pages. (Note: as you know you are only able to determine the financing plan for the Year 2 after having completed the Income Statement for Year 1 – please see next question).

You should answer this question using page 3 of the Annexes.

c) 财务建模和商业计划代写

Then you should complete the Income statements also just for years 1 and 2 of the project. You should use for that purpose the income statements provided on page 4, by completing them.

d)

Finally you should also complete the Annexed Balance Sheets just for year 1 and 2, using the table provided in page 5, by completing it.

Solution to b)

Years	1	2
Financial Needs:
CAPEX (VAT not included)
Investments in Working Capital
Principal Repayments of Medium Term Loans
Repayment of Short Term Loans
Interests of Medium Term Loans
Interests of Short Term Loans
Income Taxes Payments
Dividends Payments
Total Financial Needs =
Financial Sources:
EBITDA
Working Capital Divestures
New Equity Capital
New Medium Term Loans
Recovery of Financial Instruments
Income from Financial Instruments
Total Financial Sources =
Yearly Balance = Total Sources – Total Needs
Liquidity BOY (Cash and Banks)
Potencial Liquidity (Proforma)
Desired Liquidity (Cash and Banks)
Financing Need
Balancing Short Term Loan
Interest on Balancing Loan
Or Financial Surplus
Investment in Financial Instruments
Income from Financial Instruments
Liquidity EOY (Cash and Banks)

Solution to c) 财务建模和商业计划代写

Pro-Forma Income Statements

Years	1	2	3
Net Sales	600.000	700.000	600.000
COGS	180.000	210.000	180.000
Gross profit	420.000	490.000	420.000
Variable External Supplies and Services	120.000	140.000	120.000
Contribution Margin	300.000	350.000	300.000
Fixed External Supplies and Services	70.000	75.000	80.000
Staff Costs	70.000	75.000	80.000
Total Cash Fixed Charges	140.000	150.000	160.000
EBITDA	160.000	200.000	140.000
Depreciation and Amortization
Operating Profit = EBIT
Income from Financial Instruments
Interest on Medium Term Loans
Interest on Short Term (Balancing) loans
Financial Result
Earnings Before Taxes = EBT
Income taxes (t= 25%)
Net profit

Solution to d) 财务建模和商业计划代写

Pro-Forma Balance Sheets:

Years	0	1	2	3
Fixed Assets	400.000	450.000	450.000	450.000
Acumulated Depreciations and Amortizations	0
Net Fixed Assets (Non-Current Assets)	400.000
Inventories	0	15.000	17.500	15.000
Accounts Receivable (VAT included)	0	123.000	143.500	123.000
VAT to be received	92.000	54.050	0	0
Financial Instruments	0
Liquidity (Cash and Banks)	20.000
Current Assets	112.000
TOTAL ASSETS	512.000
Share Capital	272.000
Reserves and Retained Earnings	0
Net Profit (Current year)	0
Total Equity	272.000
Medium Term Loans	160.000
Short Term Loans	80.000
Accounts Payable	0	59.194	65.728	58.041
Other Accounts Payable (VAT, etc.)	0	2.042	3.050	10.192
Income Taxes to be paid next year	0
Current Liabilities	80.000
Total Liabilities	240.000
TOTAL SOURCES OF FUNDS	512.000
Control (Difference)	0

Notes/Remarks:

e) 财务建模和商业计划代写

Please present a brief Financial Statement Analysis just for Years 1 and 2, calculating the most relevant indicators for that purpose. You should assume that the shareholders require a 15% return on their investment. (this benchmark is already adjusted according to the debt/equity ratio of the company). And that the cost of capital is 11% (after taxes). (this question is only for the students that having done the mid-term are now taking the Final test)

f)

Please analyze the profitability of the whole project (for the whole 3 years, and considering the divesture in Year 4) assuming that the shareholders require a 10% return if there was no debt.. (Only for those students that have not done the mid-term or that have decided to give up of their mid-term grades and are now doing the final exam)