ACFI3425 Advanced Business Intelligence Using Excel 2021/2022
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Coursework 1- Financial Modelling
|
· This coursework weighs 50% of total module grade
· Due Date: Monday 19-12-2022 by 12:00 PM
· Submission form: submit a soft copy via Assessment tab on blackboard
Guidelines:
Only 1 attempt is allowed. It is your responsibility to ensure that your file is working after submission.
1- Do not forget to save your work regularly.
2- Save your Excel file using your PNUMBER as your file name.
3- Copying someone’s else model and changing its formatting will be significantly penalised and reported to the academic practice officer as an academic offence.
Penalties will be imposed for unauthorised late hand-in of work, as follows:
· Up to 14 days late without permission Max. mark 40%
· More than 14 days late without permission 0%
Assessment Criteria:
|
Style and structure |
25 marks |
|
Links and automation |
25 marks |
|
Calculations |
25 marks |
|
Formatting and visualisation |
25 marks |
|
Case Study- “West Reservoir”
Berkley Inc. is considering investing in an apartment complex called “West Reservoir Compound” and needs to run an analysis of its projected financial statements and discounted cash flow valuation over 6 years.
Assets:
· Land and improvements $1,280,947
· Buildings and Improvements $1,965,046
· Furnishings and Equipment $103,429
- Furnishing and Equipment useful life 6 years
- Furnishing and equipment has no residual values
- Use straight line depreciation method to calculate annual depreciation expense.
- To calculate depreciation: divide the cost of the asset by the number of years. You need to enter this amount into the model to determine its depreciation.
- Straight line method assumes that depreciation is the same every month.
Current Liabilities
|
|
Y0 |
Y1 |
Y2 |
Y3 |
Y4 |
Y5 |
Y6 |
|
Tenant Deposit* |
1500000 |
2176182 |
1860138 |
1551187 |
1235846 |
916533 |
590010 |
|
Accounts payable |
|
299251.2 |
603268.4 |
912098.4658 |
1227439.466 |
1547968.747 |
1876809.583 |
** Tenant security deposits should appear under current liabilities in the balance sheet.
Equity shares and other funding resources
· Long term loan $1,000,000
Loan terms:
- Years: 20 years
- Annual interest rate: 4%
· Equity - Capital contributed by owners $2,000,000
- Return on Equity: 15%
- Outstanding shares: 25,000
- Perpetuity growth rate 2%
Revenues
West Reservoir Compound is a high-end market rate apartment complex featuring 280 apartment units:
· Two- Bedroom apartment: 50 units
· One-Bedroom apartment: 150 units
· Studio unit: 80 units Monthly rent:
· Two- Bedroom apartment: $1800
· One-Bedroom apartment: $945 units
· Studio unit: 80 units: $650
|
Increase in rents: |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
|
|
2.5% |
5.0% |
3.5% |
3.5% |
3.5% |
- |
- |
- |
- |
|
|
|
||||||||||
|
Estimated vacancy rates: (for each year) |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
2.00% |
1.00% |
0.50% |
1.50% |
3.00% |
2.50% |
- |
- |
- |
- |
Expenses
Management Fee (% of effective growth income (EGI) is 5% Estimated Operating Expenses:
|
Operating Expenses |
|
|
Management Fee (5% of EGI) |
|
|
Salary Expense |
45,000 |
|
Utilities |
25,000 |
|
Insurance |
9,500 |
|
Supplies |
10,500 |
|
Advertising |
15,000 |
|
Maintenance & Repairs |
90,000 |
|
Property Taxes |
150,000 |
· Operating expenses are expected to increase at an annual rate of 2% except for management expenses (which are 5% of EGI)
· Property taxes are expected to increase in Year 4 by 2.5%
· Income tax rate is 40%. The business does not pay taxes if it is making a loss.
Income Statement Structure
Potential Gross collected Rent
-Vacancy Allowance
=Effective Gross Income
-Total expenses including depreciation
= Net Operating Income
-Interest expense
= Income before tax
= (-) Income taxes
Net income
Cash flow statement structure
Opening cash balance Cash flow from operations:
Net income
+ Depreciation and Amortisation (non-cash)
+Changes in Net Working Capital
- increase in accounts receivable
+ increase in accounts payable Total change in net working capital Net cash from operations
Cash Flows from Investing Activities:
Purchase of fixed assets
Net Cash Used by Investing Activities Cash flow from financing activities:
Long term loan Owners’ funds
- Payment of debt principal
Net cash used by financing activities
Closing cash balance = net cash from operations net cash from investment net cash from financing
Balance Sheet Structure
Current assets
Cash
Tenant deposit Account receivables
Total current assets
Fixed assets
Land and improvements Buildings and Improvements Furnishings and Equipment
-Accumulated depreciation
Total fixed assets
Total assets
Liabilities and Equity Current liabilities Accounts payable Tenants deposits
Total current liabilities
Long term liabilities
Long term debt
Total long-term liabilities
Total liabilities
Equity:
Owners funds
Retained earnings (income after tax + any income from previous year)
Total Equity
Total Liabilities and Equity
Error check: check if total assets is equal to total liabilities and equity in the last row of the balance sheet.
Requirements:
1. Integrated financial statements and Discounted cash flow valuation.
a. Forecast annual Income statement
b. Forecast annual cash flow statement
c. Forecast annual balance sheet
d. Perform discounted cash flow valuation, given perpetuity growth rate 2%
i. Forecast the free cash flow
ii. Calculate the weighted average working capital
iii. Calculate the terminal value for the business
iv. Calculate the NPV
v. Calculate the fair value per share.
Notes:
· Create necessary tabs in your Excel file to help the user understand your model
· Ensure that your model does not show errors
2. Create a new tab named “Answers” and provide the answers for questions below: If the owners target $300000000 as terminal value, by changing the interest rate,
a) What is the new annual interest rate that would help them achieve the above terminal value?
b) What is the new NPV?
c) What is the new fair value per share?
3. Perform scenario analysis on the income statement and cash flow statement.
|
|
Estimated vacancy rates for each year |
|||||
|
Scenarios |
1 |
2 |
3 |
4 |
5 |
6 |
|
Base case |
2.00% |
1.00% |
0.50% |
1.50% |
3.00% |
2.50% |
|
Worst case |
8.00% |
5.00% |
2.50% |
5.00% |
8.00% |
6.50% |
|
Best Case |
0% |
0% |
0% |
0% |
0% |
0% |
4. Insert two charts to show the values under the three scenarios; one for annual net income and another for closing cash balance.
5. The business is considering charging tenants for parking on top of the rent. Parking cost/unit is $2000 per year.
a. What would be the effect on the net income?
b. Insert a suitable chart to show the two scenarios (income with parking and income without parking).
6. Ensure that chart titles are automated and updated when scenarios change. Ensure suitability of the chart type and double check if it makes sense.
7. After you finish your model, create a new tab named “Introduction” to give an overview for the user about the purpose of this model and its components. It will be an advantage to give the user instructions on how to navigate the model and which cells in which worksheet the user need to manipulate to update the model.
8. Formatting:
a. Ensure that the background in all sheets is white
b. Apply necessary formatting for tables
c. Maintain consistency in model design and theme.
ACFI3425 Coursework 1: Frequently Asked Questions (FAQ)
1- What is Return of Equity (ROE) for?
You need it in the discounted cash flows valuation. It represents “Cost of Capital” or the return investors expect from their investment.
2- For DCF valuation, are capital expenditures given? if yes, do I need to include it in my model?
No, there is no givens for capital expenditures. Follow the cash flow statement structure in the coursework brief.
3- The business does not have account receivables or inventory; do I need to include them in my model?
No.
4- I don’t understand where to include tenant deposit.
Tenant deposit represents prepaid expense. It should appear under current assets and current liability. Please revisit the coursework brief and check the balance sheet structure.
5- Do I need to enter the long-term loan value $1,000,000 every year?
No, you do not.
You get the loan in the beginning of the business operations, and you start paying instalments and interest every year.
The instalments are calculated on annual basis and should be included in the cash flow statement for Payment of debt principal.
Interest expense should appear in income statement.
6- What if my balance sheet does not balance? Will I lose many marks?
It depends on why it is not balanced:
· If the business is short in cash and it is going bankrupt - maybe, then this is not your fault.
· If it is unbalanced because of calculations, then you may lose marks.
· If it is unbalanced because of missing inputs, it will either link to automation or not following the givens in the brief and may make you lose few marks.
Ensure you include error checking in your model
7- Where do I get cash balance in the balance sheet?
Cash in the balance sheet should reflect the closing cash balance from cash flow statement.
8- Where to show the scenario analysis?
In a separate tab named “Scenario Analysis”.
9- Is it a mistake to include Scenario analysis in the assumptions tab?
It is not a good modelling practice. It is better to have an individual tab for each part of your model.
10- How significant is data entry to assessment grading?
Not significant to the total assessment grade.
Data entry should be enough to cover same statements’ structure provided in the brief to complete the calculations correctly.
Calculations are assessed based on formulas and functions you use, and they represent 25% of total assessment grade. If your inputs are significantly incomplete, it will reflect on the variety of functions you use in your model.
11- Can you just give me the ready model?
NO.
12- Can I show you my draft model while asking you for clarifications?
Yes, you can. This assessment is about designing a tailored model for a client. It can involve consultations and follow ups with tutors.
13- I am starting to panic and I am afraid I won’t be able to finish. What should I do?
The model should not take too long to complete. The best way to go around this is scheduling a day and time to sit and read the brief carefully and start designing your model.
It is highly recommended to complete everything in one shot. You may leave the formatting (highlight, font, number formatting, cell borders to the end, etc…). This way, the stream of your thoughts will not be interrupted, and you will have a solid draft to work on. Pass any questions to your tutor on regular basis and at an early stage to avoid stress.
14- In which week did we cover discounted cash flows valuation?
Please refer to week 7 PPT slides and excel file “Week 7 – Output”. You can use the table in DCF tab as a template and reference the relevant numbers to update the formulas and fill the tables.
15- Are the expenses given per month or per year?
Per year.
16- How to calculate EGI?
It is given in coursework brief page 4 under the income statement as follow: Potential Gross collected Rent
-Vacancy Allowance
=Effective Gross Income
17- Do we do annual or monthly calculations?
Everything in the model is calculated on annual basis. You need to provide the annual figures.
2022-11-22