Question
Your model should be an annual model showing 10 calendar year time periods, plus the initial Day 0 Balance Sheet. The equity investment date (Day
Your model should be an annual model showing 10 calendar year time periods, plus the initial Day 0 Balance Sheet. The equity investment date (Day 0) is 31 December 2015. The first period (Year 1) covers 1 January 2016 to 31 December 2016, and the final model period (Year 10) covers 1 January 2025 to 31 December 2025. Any activity after year 10 is ignored for analysis purposes and is not modelled. Unless otherwise specified, assume all cashflows occur on the final day of the period.
Round 1 Secton 2 - Case Study Information Pack
Day 0 Transactions / Initial Investment $30,000,000 equity is invested on Day 0. $20,000,000 debt is borrowed on Day 0. The Balance Sheet Assets on Day 0 are: o $3,000,000 cash o $3,000,000 accounts receivable o $4,000,000 inventory o $40,000,000 Property Plant and Equipment (PPE) Revenues Year 1 Sales are $25,000,000. Sales grow annually by the minimum of either (i) $3,000,000 or (ii) 8% of the previous years sales. Cost of Goods Sold Cost of Goods Sold equals 60% of sales Expenses Year 1 Expenses are $3,000,000 Expenses grow annually by 7% of the previous years expenses. Capital Expenditure On the final day of Year 4, additional PPE is purchased for $10,000,000. This new purchase is 90% funded by an equity injection on the same day, with the remainder funded by cash on hand. Depreciation The original $40,000,000 PPE (purchased on Day 0) depreciates over 10 years in a straight line method ($4,000,000 per year). The new PPE (purchased at the end of year 4) depreciates over 5 years in a straight line method. Debt Interest is charged on the outstanding debt at a rate of 6.50% per annum. Interest is paid at the end of each year. Principal repayments (i.e. not counting interest) are $2,500,000 per year until the debt is repaid. Repayments are made at the end of each year, immediately after interest payments are made.
ModelOff 2015 - Round 1
Round 1 Section 2 - Case Study Information Pack
Payment Terms / Working Capital Inventory at the end of each year is equal to 30% of Cost of Goods Sold Accounts Receivable at the end of each year are 10% of Sales Accounts Payable at the end of each year are 12% of Cost of Goods Sold plus 15% of Expenses Distributions to Equity At the end of each of year, any amount above $4,000,000 in the cash account after all other considerations are dealt with is distributed to equity investors. If there is less than or equal to $4,000,000 in the cash account prior to distributions, no distribution is made that year. Sizing of distributions is only constrained by cash available - not by book profits. Other Considerations Assume that all taxes are zero. Assume that no interest is earned / paid on Macrohards positive / negative cash account balance.
Please help and check if the balance sheet is correct.
Period Start Period End Your Number unts Sur 1 Jan 16 31-Dec-16 1 1 1 31-Dec-15 1-Jan-17 31-Dec-17 2 1-an-18 31-Dec-18 3 1.Jer 16 31-Des-19 4 1 Jan 22 34 1 Jan 20 21-Dec-20 s 5 1 Jan 21 31-Dec-21 1 Jan 23 31-06-23 -22 7 1 Jan 24 31-Dec-24 2 1 an 26 31-Dec-25 13 0.01 Profit and Los Sacs $1 350 cas,358 29,180,000 31,492 800 34,012 224 35,723,202 SI 216 C51,995 25,000,000 27,000,000 15.000.000 *8.200.000 40.03 40.00 % 3,000,000 3,210,000 18.885 FAO Cos of Goods Sold Grosso Margin Expenses 20,407 334 17.96 000 40.00 2,434,700 COS 22038, 21 40.00 4,207,856 $1 41,443,44 2,675.129 3,932 3 EBITDA SI 102.584,019 7,000,000 7.290.000 82239 300 8,921991 9,672,502 10,485.626 50.000,000 50.000.000 5850,000 2000.00 190000 4,000,000 1,137.500 4,000,000 97500 4.0 CD R12 500 6.002 OCO A53000 Depredaton Ina FEPRISE Nel Pruit 6.000.000 497 500 39.671,858 42674,853 45,571,858 49,571,855 29.833 115 25,803 115 27.103.115 29 203.115 | 40.00 40.00% 40.00 40.00% 4.502,191 4817,344 5.134.550 5,515,278 11.366,55212.291,399 13,114,185 13.251,386 6000,000 5.000.000 51.000.000 4,000,000 995,000 162,500 5041,552 8.008.829 , 7.14185 8.253 388 1.041,552 2.082,899 3,14,185 5,953,365 4.000.000 2.000.000 4.000,003 4.000.000 si 1.700.000 2.452,503 3.251.300 11.12.191 3022502 3.888.126 48.734.219 12 207,492 Distributions $1 199 491 To Retired Earrings $ - 31.127427 1.700.030 2452,503 9.251 300 4.000.000 3,022 502 3.986.126 0.02 Balance Sheet Assets Cash Raria Inventory PPE Tool Aases 3.000.000 7,000,000 7,500,000 2,229,300 11,078009 9,672,502 3.000.000 2,500,000 2.700.000 2.016 CON 9.149 an 3.491 299 4.000.000Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started