i will add few pictures, those are the examples of that assignment. Professor left has a reference, please follow those images as a reference and complete the assignment asap.
Instructions Complete the Module 5 Assignment using the included Excel Spreadsheet template. This assignment focuses on using data to create financial statement projections and analysis. All assignments must be submitted by 11:59 pm on Sunday. Late assignments will be graded per the Late Assignment Policy posted within the syllabus. The grading rubric is listed below. Prompt Study of Hard Times Turn Around a Toy Company published by Modeloff (Links to company website, for problem see below). INTRODUCTION Slick Micks Toys Inc, is the manufacturer of a type of action figure moderately popular with kids between the ages of 3 and 8 years. Slick Mick's not so slick accountants all have kids that love the figurines but have nonetheless identified that unless there is a swift improvement in cash flow, the business might not make it to the next season. You have been asked to help the team forecast cash flow for the coming three years with particular focus on the next twelve months. Use the following information to draft a three year monthly cash flow forecast with the first month being October 2013 and use it to answer the following key questions. The key assumptions are provided in the Excel file that accompanies this question and are also set out below. KEY ASSUMPTIONS Complete the calculations as indicated in the document entitled Required Calculations. These calculations should be done on the spreadsheet template provided in the appropriate section or on a clearly labeled tab within the spreadsheet. Using the calculated data, create a pro forma cash flow statement, income statement and balance sheet. HINT: If information is not supplied or calculable based on the data provided, do not include it in your model. Module 5 Required Calculations Module 5 Assignment Excel Template ModelOff 2013 Questions and Answers Required Calculations: 1. Calculate the Widget sales for CY2014, CY2015 and CY2016. NOTE: The percentage increase is based on the previous calendar year, not the original year. Example: CY17 sold 100 units, CY18 was an increase of 4%, and CY19 was an increase in sales of 6%. Sales in CY18 = CY17 sales of 100 units x 1.04 = 104 units sold Sales in CY19 = CY18 sales of 104 units x 1.06 = 110 units sold 2. Using the data under current sales price and margin, determine the contribution margin and the variable costs per unit. Formula is Contribution Margin = Sales price - Variable costs Variable costs = Sales price - contribution margin Variable costs = sales price - (contribution margin % x Sales price per unit) 3. Calculate the indirect costs for each calendar year using the same process as with the calculation for Widget Sales. 4. Calculate the cash receipts using the percentage breakdown provided. Example: X Company had $100,000 of sales in January. In the month sold, 10% of revenues are collected; 50% of revenues are collected in the second month, and 40% of sales are collected in the third month. How much is collected from revenues in each month? Month 1 (aka month of sale) = $100,000 x 10% = $10,000 Month 2 = $100,000 x 50% = $50,000 Month 3 = $100,000 x 40% = $40,000 With each new month of sales, the breakdown will be the same percentage for months 1, 2 and 3. Add that breakdown to the previous revenue collection cycles to get the total revenue for that month. 5. Calculate the Cash Receipts on Opening Receivables using the method shown in #4. 6. Calculate the Cash on Payments on Purchases using the method shown in #4. 7. Calculate the Cash Payments on Opening Payables using the method shown in #4. 8. Calculate interest payments using simple interest, as outlined below. Formula for Simple Interest - Principle x rate x time Simple Interest = 2,000,000 x 7% x # of years = total interest for life of the loan So you can assume that the interest per year is 2,000,000 x 7% Annual interest will then = 2,000,000 x 7% = 140,000 per year $140,000 per year / 12 months = $11,666.67 per month interest 9. To calculate any monthly costs from an annual cost divide the annual costs by 12 months, UNLESS a percentage breakdown is provided within the data. Then use the percentage breakdown provided. 10. Using the collected data, complete a cash flow statement broken out by month for each quarter, for the period of CY2013 Q4 through CY2016 - Q3. 11. Using the collected data, complete an income statement broken out by month for each quarter, for the period of CY2013 - Q4 through CY2016 -Q3. 12. Using the collected data, complete a balance sheet broken out by each year for September 30th. MODELOFF Model Off 2013 Round 2 - Hard Times All values in $ unless specified * Note that in this case, no marks will be awarded nor deducted should entrants choose to complete this exercise below or on another tab of this workbook. Assumptions Quarterly seasonality of sales CY Q1 CY Q2 CY Q3 CY Q4 20% 15% 30% 35% assume months within each quarter have equal sales irrespective of the number of days in each month Total Annual Sal CY Q1 CY Q2 CY Q3 CY Q4 Total (Check) Quarter Sales for CY Quarterly Sales for CY 2013 Quarterly Sales for CY 2014 Quarterly Sales for CY 2015 Quarterly Sales for CY 2016 Widget Sales Units Sold Sales Growth CY 2013 CY 2014 CY 2015 CY 2016 175,000 4% 12% 12% assume escalation in sales takes effect instantly from 1 January Sales Per Year in Units Sold Current Sales Price and Margin: Sale Price per unit Contribution Margin 30-Sep.-13 29.99 25% Per Unit CM = Sale Price - Variable Costs Variable Costs = Sale Price - CM Indirect Costs Indirect Costs Indirect Cost Growth CY 2013 CY 2014 CY 2015 CY 2016 $ 1,200,000 4% 4% 4% *Indirect Costs are fixed and occur monthy irrespective of sales. Indirect Costs per Year Indirect Costs per Month Debt amortisation schedule Date 31-Dec.-13$ 28-Feb.-14/$ 30-Apr-14 $ 30-Jun.-14 $ 31-Aug.-14 $ 30-Sep-14$ 30-Nov.-14 $ 31-Dec.-14|$ 31-Mar-15 $ 30-Jun.-15$ 30-Sep.-15 $ 31-Dec.-15$ 50,000 50,000 60,000 60,000 60,000 90,000 60,000 150,000 60,000 150,000 60,000 150,000 Cash Receipts Timing Sale Month +2 Month +1 60% Month +3 15% Cash Receipts on Sales 25% Oct-13 30% Nov-13 60% Dec-13 10% Cash Receipts on Opening Receivables Cash Payments Timing Purchase Month +1 85% Month +2 10% Month +3 5% Cash Payments on Purchases Oct-13 30% Nov-13 60% Dec-13 10% Cash Payments on Opening Payables The above schedule is applicable to both direct and indirect costs Assets and Liabilities = = Assets Cash Accounts Receivable Property Plant and Equipment Total Assets Opening $ 18,000 $ 600,000 $ 500,000 $ 1,118,000 Formula for Simple Interest = Principle x rate x time Simple Interest = 2,000,000 x 7% x # of years = total interest for life of the loan So you can assume that the interest per year is 2,000,000 x 7% Annual interest will then = 2,000,000 x 7% = 140,000 per year $140,000 per year / 12 months = $11,666.67 per month interest = - Interest Liabilities Accounts Payable Debt Facility A Total Liabilities Opening $ 350,000 $ 2,000,000 $ 2,350,000 7.0% *Simple interest p.a with interest paid at month end Depreciation and Capex Depreciation $ 10,000 per month *No capital expenditure is forecast over the next 3 years Taxation Slick Micks has substantial historic tax losses. Assume that no tax will be payable for the period of analysis. Forecast CYFA1 LY.2 LYE 10.14 CYCLE 11 101 14 14:14 11141 E5110 LY3111622 ial 22 18 31-OCHE LYE11 131 10141 1914 31-101 31 1. Na 18 TNT 15 1. Feb 11 1.2.18 31-01 1 A 14 35111 1.Fb 10 FR10 12 1.4p 10 E1 Ny 14 EN Nar10 14 1 141 BL141 1 1.D 14 ES111101 1-101 31 El TA 1.15 31 Y 1 EL 31H11 1.151 11 You Foto Baby Muh Lourde Braxtol Feries FREE in US EDET. Le Verne AXA boas BV TW ETIR 1 w pewn Dewan w TH Porecend Your Chatboy Courier Start al Forted Free Blend What's F1-41 DYE CYCLE 121 14 1 14 31.10 3-10 DYET 121 1.15 1 Feb 151 31.151 5 Feb 15 1 131 val 14015 118 2 Ny 14 101 1 N 30NT13 DYE-41 10 101 114 14 14 10 $1.2010 12 1514 1. Feb 14 20 Fr 101 1 Hy 1411 E1 Ny 14 Ny 101 10141 11 111 3- 151 1. In 14 BLO 14 1or 14 11 31. 121 tv 16 Tweedwh WAR 1 Bukan serta Worth 1 orth Text He Wand 2003 X PICS Prawa Talpern Escudry Dewan ( AT TER Ten Day Reposer theatre Tech Facharta MACHEN CY 2015-02 19 20 Apr 15 1-May-151 Apr 15 31-May-15 21 1-Jun-15 30-Jun-15 CY 2015-03 22 23 1-Jul-15 1 1-Aug-15 31-Jul-15 31-Aug-15 24 1-Sep-15 30-Sep-151 CY 2015-04 25 26 1-Oct-15 1-Nov.15 31-Oct-15 30-Nov-15 27 1-Dec-15 31-Dec-15 CY 2016.01 28 29 1-Jan-18 1-Feb-16 31-Jan-16 29-Feb-10 30 1-Mar-16 31-Mar-16 CY 2018-02 31 32| 1-Apr-16 1-May-16 30-Apr-16 31-May-16 33 1-Jun-16 30-Jun-16 CY 2016.03 35 1-Jul-16 1-Aug-16 31-Jul-16 31-Aug-16 36 3 Year 1-Sep-16 TOTALS 30-Sep-16 CY 2015 - 2 19 20 -Apr-15 1-May-15 Apr 15 31-May-16 21 1-Jun-150 30-Jun-16 CY 2015 - Q3 221 23 1-Jul-15 1-Aug-15 31-Jul-15 31-Aug-151 24 1-Sep-15 30-Sep-15 CY 2015-04 255 26 1-Oct-15 1-Nov-15 31-Oct-15 30-Nov-15 27 1-Dec-15 31-Dec-10 CY 2016 - 1 28 29 1-Jan-16 1-Feb-16 31-Jan-10 29-Feb-16 30 1-Mar-16 31-Mar-16 CY 2016-02 111 32 1-Apr-16 1 1-May-16 und 30-Apr-16 31-May-16 33 1-Jun-16 30-Jun-16 CY 2016 - 23 34 35 1-Jul-16 al 1-Aug-16 31-Jul- 31-Aug-16 36 3 Year 1-Sep-16 TOTALS 30-Sep-16 Balance Sheet Projections by Year Opening Balance Sheet As of September 30, 2013 Year 1 Balance Sheet As of September 30, 2014 Year 2 Balance Sheet As of September 30, 2015 Year 3 Balance Sheet As of September 30, 2016 Assets Cash Accounts Recievable Inventory Property, Plant & Equipment Less: Depreciation Total Assets Liabilities Accounts Payable Debt Facility A Interest Payable Total Liabilities Equity Company Equity Total Liabilities + Equity Based on the data provided from the Hard Times for a Toy Company provided by ModelOff and used on the Week 5 Assignment You have been asked to help the team forecast cash flow for the coming three years with particular focus on the next twelve months. Use the following information to draft a three year monthly cash flow forecast with the first month being October 2013 and use it to answer the following key questions. The key assumptions are provided in the Excel file that accompanies this question and are also set out below. Questions: Q1: What is the forecast Sales Revenue in January 2014? Q2: What is the forecast closing accounts payable in December 2015? Q3: What is the total Indirect Costs expected for the three years forecast? Q4: What is the total interest expense for Calendar Year 2014? Additional Requirements: 1. Create an income statement projection for the outlined period 2. Create a statement of cash flows projection for the outlined period 3. Create a balance sheet projection for the outlined period Assumptions Quarterly seasonality of sales CY Q1 CY 02 CY Q3 CY 04 25% 35% 28% assume months within each quarter have equal sales irrespective of the number of days in each month 12% Quarter Sales for CY Quarterly Sales for CY 2013 Quarterly Sales for CY 2014 Quarterly Sales for CY 2015 Quarterly Sales for CY 2016 Total Annual Sale 200,000 206,000 222,480 240,278 CY Q1 50,000 51,500 55,620 60,070 CY Q2 24,000 24,720 26,698 28,833 CY Q3 70,000 72,100 77,868 84,097 CY Q4 56,000 57,680 62,294 67,278 Total (Check) 200.000 206,000 222,480 240,278 Widget Sales Units sold for CY Q4 of 2013 = 200,000 x 28% Quarter Sales for CY Q4 of 2013 = sales/3 mc 56,000 18,667 Units Sold Sales Growth CY 2013 CY 2014 CY 2015 CY 2016 200,000 3% % 8% 8% % * assume escalation in sales takes effect instantly from 1 January Sales Per Year in Units Sold 200,000 206,000 222,480 240.278 Current Sales Price and Margin: S Sale Price per unit Contribution Margin 30-Sep-13 13.75 20% Per Unit CM = Sale Price - Variable Costs Variable Costs = Sale Price - CM Variable Costs = $13.75 - (20% x $13.75) Variable Costs = $13.75 -2.75 Variable Costs = $11.00 Indirect Costs Indirect Costs Indirect Cost Growth CY 2013 CY 2014 CY 2015 CY 2016 S 975,000 3% 3 3% 3% *Indirect Costs are fixed and occur monthy irrespective of sales. Indirect Costs per Year 975,000 1,004,250 1,034,378 1,065,409 Indirect Costs per Month 81,250 83,688 86,198 88,784 Debt amortisation schedule Date 31-Dec-135 50,000 Debt paid yr 1 Debt paid Y2 Debt Paid Yr 3 28-Feb-145 50,000 S 50.000$ 60,000 S 150,000 30-Apr-145 60,000 S 50,000 $ 150,000 30-Jun-145 60,000 S 60.000$ 60,000 31. Aug.145 60,000 S 80.000 150,000 30-Sep-145 90,000 S 80.000 $ -- 30-Nov-145 60,000 S 90.000 $ 480,000 31-Dec-145 150,000 S 370.000 31-Mar-1515 60,000 30-Jun-15$ 150,000 30-Sep-15 60,000 31-Dec-151 -$ 150,000 $ 1,000,000 60.000 Cash Receipts Timing Sale Cash Receipts on Sales Month +1 Month +2 60% 25% Month +3 15% Oct-13 30% Nov-13 60% Cash Receipts on Opening Receivables Dec-13 10% Cash Payments Timing Purchase Cash Payments on Purchases Month +1 Month +2 Month 3 % 85% 10% 5% Cash Payments on Opening Payables Oct-13 30% % Nov-13 60% Dec-13 10% The above schedule is applicable to both direct and indirect costs Assets and Liabilities Assets Cash Accounts Receivable Property Plant and Equipment Total Assets Opening $ 15.000 $ 360.000 $ 450.000 $ 825.000 Formula for Simple Interest - Principle x rate x time Simple Interest = 2,000,000 x 7% x # of years = total interest for life of the loan So you can assume that the interest per year is 2,000,000 x 7% Annual interest will then - 2,000,000 x 7% - 140,000 per year S140,000 per year / 12 months = $11,666 67 per month interest Liabilities Accounts Payable Debt Facility A Total Liabilities Opening Interest ALTERNATIVE INTEREST: 275,000 Calcuate the amount paid each year, multiply by 7% and use this as the annual interest payment, following the amortization schedule. This gives an answer to #4 of B 2,000,000 7.0%Simple interest p.a with interest paid at month end $ 2.275.000 Depreciation and Capex Depreciation $ 10.000 per month "No capital expenditure is forecast over the next 3 years Taxation Slick Micks has substantial historic tax losses. Assume that no tax will be payable for the period of analysis. Forecast 3 Year Forecasted Income Statement by Month Counter Start of Period End of Period CY 2013-04 21 1-Nov-13 30-Nov-13 3 1-Dec-13 31-Dec-13 CY 2014 - Q1 5 1-Feb-14 28-Feb-14 4 1-Jan-14 31-Jan-14 6 1-Mar-14 31-Mar-14 CY 2014-02 8 1-Apr-14 1-May-14 30-Apr-14 31-May-14 CY 2014 - Q3 10 11 1-Jul-14 1-Aug-14 31-Jul-14 31-Aug-14 1-Oct-13 31-Oct-13 9 1-Jun-14 30-Jun-14 12 1-Sep-14 30-Sep-14 CY 2014 - Q4 14 1-Nov-14 30-Nov-14 13 1-Oct-14 31-Oct-14 15 1-Dec-14 31-Dec-14 CY 2015 - Q1 17 1-Feb-15 28-Feb-15 16 1-Jan-15 31-Jan-15 18 1-Mar-15 31-Mar-15 CY 2015 - Q2 191 201 1-Apr-15 1-May-15 30-Apr-15 31-May-15 21 1-Jun-151 30-Jun-151 30-Sep-13 Sales in Units Sales in Dollars Less Variable Expenses (AKA COGS) Contribution Margin 18,667 18,667 18,667 17,167 17.167 17,167 8,240 8,240 8,240 24,033 24,033 24,033 19,227 19,227 19,227 18,540 18,540 18,540 8,899 8,899 8,899 $ 256,666.67 S 256,666.67 $ 256,666.67 $ 236,041.67 $ 236,041.67 $ 236,041.67 $ 113,300.00 $ 113,300.00 $ 113,300.00 $ 330,458.33 $ 330,458.33 $ 330,458.33 $ 264,366.67 $ 264,366.67 S 264,366.67 $ 254,925.00 S 254,925.00 $ 254,925.00 $ 122,364.00 S 122,364.00 $ 122,364.00 $ 205,333.33 $ 205,333.33 $ 205,333.33 $ 188,833.33 $ 188,833.33 $ 188,833.33 $ 90,640.00 $ 90,640.00 $ 90,640.00 $ 264,366.67$ 264,366.67 $ 264,366.67 $ 211,493.33 $ 211,493.33 $ 211,493.33 $ 203,940.00 $ 203,940.00 $ 203,940.00 $ 97,891.20 S 97,891.20 $ 97,891.20 $ 51,333.33 $ 51,333.33 $ 51,333.33 $ 47,208.33 $ 47,208.33 $ 47,208.33 $ 22,660.00 $ 22,660.00 $ 22,660.00 $ 66,091.67$ 66,091.67$ 66,091.67$ 52,873.33 $ 52,873.33 $ 52,873.33 $ 50,985.00 $ 50,985.00 $ 50,985.00 $ 24,472.80 $ 24,472.80 $ 24,472.80 Indirect Costs (AKA Fixed Expenses) Long Term Debt Repayment Interest Expense Depreciation $ 81,250.00 $ 81,250.00 $ 81,250.00 $ 83,687.50 S $ 50,000.00 S $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 83,687.50 $ 50,000.00 11,666.67 $ 10,000.00 $ 83,687.50 $ 83,687.50 $ 83,687.50 $ 83,687.50 $ 83,687.50 $ 83,687.50 $ $ 60,000.00 $ 60,000.00 $ 60,000.00 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67$ 11,666.67 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 83,687.50 $ 83,687.50 S 83,687.50 S 83,687.50 $ 86,198.13 S 90,000.00 S 60,000.00 $ 150,000.00 11,666.67 $ 11,666.67$ 11,666.67$ 11,666.67 $ 11,666.67 S 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 86,198.13 $ $ 11,666.67$ 10,000.00 $ 86,198.13 $ 86,198.13 $ 86,198.13 $ 86,198.13 60,000.00 $ 150,000.00 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 Total Expenses (Excluding Depreciation) $ 275,000.00 $ 298,250.00 $ 298,250.00 $ 348,250.00 $ 284,187.50 $ 334,187.50 S 284,187.50 $ 245,994.17 S 185,994.17 $ 245,994.17 S 359,720.84 S 419,720.84 $ 449,720.84 $ 306,847.50 S 366,847.50 S 456,847.50 $ 301,804.80 S 301,804.80 $ 361,804.80 S 195,756.00S 195,756.00 $ 345,756.00 Net Income/Loss S (51,583.34) S (51,583.34) $ (101,583.34) $ (58,145.84) S (108,145.84) S (58,145.84) S (142,694.17) S (82,694.17) S (142,694.17) $ (39,262.50) S (99,262.50) $ (129,262.50) $ (52,480.84) S (112,480.84) S (202,480.84) $ (56,879.79) S (56,879.79) S (116,879.80) S (83,392.00) S (83,392.00) $ (233,392.00) Forecasted 3 Year Statement of Cash Flows by Month Total Revenue Collected (Cash Inflows) Sales in Dollars (Revenue) $ 360,000.00 $ 256,666.67 $ 256,666.67 $ 256,666.67 $ 236,041.67 $ 236,041.67 $ 236,041.67 $ 113,300.00 $ 113,300.00 $ 113,300.00 $ 330,458.33 $ 330,458.33 $ 330,458.33 $ 264,366.67 $ 264,366.67 $ 264,366.67 $ 254,925.00 $ 254,925.00 $ 254,925.00 $ 122,364.00 $ 122,364.00 $ 122,364.00 Opening A/R Month 1 Month 2 Month 3 60% of Revenue 25% of Revenue 15% of Revenue $ 108,000.00 $ 216,000.00 $ 36,000.00 $ 154,000.00 $ 154,000.00 $ 154,000.00 $ 141,625.00 $ 141,625.00 $ 141,625.00 $ 67,980.00 S 67,980.00 $ 67,980.00 $ 198,275.00 $ 198,275.00 $ 198,275.00 $ 158,620.00 $ 158,620.00 $ 158,620.00 $ 152,955.00 $ 152,955.00 $ 152,955.00 $ 73,418.40 $ 73,418.40 $ $ $ 64,166.67$ 64,166.67 $ 64,166.67$ 59,010.42 $ 59,010.42 $ 59,010.42 $ 28,325.00 $ 28,325.00 $ 28,325.00$ 82,614.58 $ 82,614.58 $ 82,614.58 $ 66,091.67 $ 66,091.67 $ 66,091.67 $ 63,731.25 $ 63,731.25 S 63,731.25 $ 30,591.00 $ $ $ 38,500.00 $ 38,500.00 $ 38,500.00 $ 35,406.25 $ 35,406.25 $ 35,406.25 $ 16,995.00 $ 16,995.00 S 16,995.00 $ 49,568.75 $ 49,568.75 S 49,568.75 S 39,655.00 $ 39,655.00 $ 39,655.00 38,238.75 $ 38,238.75 S 38,238.75 $ 73,418.40 30,591.00 18,354.60 Total Revenue Collected (Cash Inflows) $ 262,000.00 $ 434,166.67 $ 292,666.67 $ 244,291.67$ 239,135.42 $ 236,041.67 $ 162,396.67 $ 131,711.25 $ 113,300.00 $ 243,595.00 $ 297,884.58 $ 330,458.33 $ 290,803.33 $ 274,280.42 $ 264,366.67$ 258,701.67 $ 256,341.25 $ 254,925.00 $ 175,388.40 $ 142,248.15 $ 122,364.00 Schedule of Accounts Payable - Cash Payments (Outflows) Total Expenses (Excluding Depreciation) $ 275,000.00 $ 286,583.33 $ 286,583.33 $ 286,583.33 $ 272,520.83 $ 272,520.83 $ 272,520.83 $ 174,327.50 $ 174,327.50 $ 174,327.50 $ 348,054.17 S 348,054.17 $ 348,054.17 $ 295,180.83 $ 295,180.83 $ 295,180.83 $ 290,138.13 $ 290,138.13 $ 290,138.13 $ 184,089.33 $ 184,089.33 $ 184,089.33 Opening A/P Month 1 85% of Expenses Month 2 10% of Expenses Month 3 5% of Expenses Long Term Debt Repayment Interest Expense $ 82,500.00 $ 165,000.00 $ 27,500.00 $ 243,595.83 S 243,595.83 $ 243,595.83 $ 231,642.71 $ 231,642.71 S 231,642.71 $ 148,178.38 $ 148,178.38 $ 148, 178.38 $ 295,846.04 $ 295,846.04 $ 295,846.04 $ 250,903.71 $ 250,903.71 $ 250,903.71 $ 246,617.41 S 246,617.41 $ 246,617.41 $ 156,475.93 $ 156,475.93 $ 156,475.93 $ $ 28,658.33 $ 28,658.33 $ 28,658.33 $ 27,252.08 $ 27,252.08 $ 27,252.08 $ 17,432.75 $ 17,432.75 $ 17,432.75 $ 34,805.42 $ 34,805.42 $ 34,805.42 $ 29,518.08 S 29,518.08 $ 29,518.08 29,013.81 $ 29,013.81 $ 29,013.81 $ 18,408.93 $ 18,408.93 $ $ $ 14,329.17 $ 14,329.17 $ 14.329.17 $ 13,626.04 $ 13,626.04 $ 13,626.04 $ 8,716.38 $ 8,716.38 $ 8,716.38 $ 17,402.71 $ 17,402.71 $ 17,402.71 $ 14,759.04 $ 14,759.04 $ 14,759.04 $ 14,506.91 $ 14,506.91 $ 14,506.91 $ 9,204.47 $ 50,000.00 $ 50,000.00 $ 60,000.00 $ 60,000.00 $ 60,000.00 $ 90,000.00 $ 60,000.00 $ 150,000.00 $ 60,000.00 $ 150,000.00 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 Total Cash Paid Each Month (Outflows) $ 337,762.50 S 448,920.84 $ 375,750.00 $ 286,296.88 $ 334,890.63 $ 284,187.50 $ 260,723.17 $ 190,903.84 $ 245,994.17 $ 333,661.84 $ 411,034.50 $ 449,720.84 $ 314,778.50 $ 369,491.17 $ 456,847.50 $ 302,561.20 S 302,056.93 $ 361,804.80 $ 211,663.32 $ 201,058.44 $ 345,756.00 Net Cash Flow $ (75,762.50) $ (14,754.17) $ (83,083.34) $ (42,005.21) $ (95,755.21) $ (48,145.84) $ (98,326.50) $ (59,192.59) $ (132,694.17) $ (90,066.84) $ (113,149.92) $ (119,262.50) $ (23,975.17) $ (95,210.75) $ (192,480.84) $ (43,859.53) $ (45,715.68) $ (106,879.80) $ (36,274.92) $ (58,810.29) $ (223,392.00) 1 1 36 3 Your 1-Sep-16 TOTALS 30-Sep-16 - CY 2015 - az CY 2015 - Q3 CY 2015-04 CY 2016-1 CY 2016 - az CY 2016 - 20 21 22 23 24 25 26 27 281 29 30 31 32 331 34 35 1-May-15 1-Jun-15 1-Jul-15 1-Aug-15 1-Sep-15 1-Oct-15 1-Nov-15 1-Dec-15 1-Jan-16 1-Feb-16 1-Mar-161 1-Apr 16 1-May-16 1-Jun-16 1-Jul-16 1. Aug.16 31-May-15 30-Jun-15 31-Jul-15 31-Aug-15 30-Sep-15 31-Oct-110 - 30-Nov-101 31-Dec-15 31-Jan-16 29-Feb-101 31-Mar-16 30-Apr-161 31-May-16 30-Jun-16| - 31-Jul-16 31-Aug-161 8,899 8,899 25,958 25,956 25,956 28,833 20.765 20.765 20.023 20.0231 20.0231 9,6111 9,611 9,6111 28,032 28,032 1$ 122,364,00 S 122,364,00 S 356,895.00 S 356,895.00 S 356,895.00 S 396,459.36 $ 285,516.00 $ 285,516.00 $ 275,319.00 $ 275,319.00 $ 275,319.00 $ 132,153.125 132.153.12 $ 132.153.12 $ 385,446.605 385 446.605 5 $5 S 97,891.20 S 97,891.20 S 285,516.00 S 285,516.00 $ 285,516.00 S 317,167.49 $ 228,412.80 $ 228,412.80 $ 220,255,20 5 220.255.20 $ 220,255.20 $ 105,722.50 S 105,722.50 S 106.722.50 S 308,357.28 S 308 357.285 S 24,472.80 S 24,472.80 S 71,379,00 S 71,379.00 S 71,379.00 S 79,291.87 $ 57,103.20 $ 57,103.20 $ 55,063.80 $ 55,063.80 $ 55,063.80 5 26,430.62 S 26,430.62 5 26,430.62 5 77,089.32 S 77.089.32S S 86,198.13S 86,198.13 $ 86,198.13 $ 85,198.135 86,198.135 86,198.13 $ 86,198.13 86,198.135 88,784.07 S 88,784.075 88,784.075 88,784.07 5 88.784.07 S 88.784.07 S 88.784.07 S 88.784.07 S 1S 150,000.00 IS 90,000.00 1$ 150,000.00 1$ 11,666.67$ 11,666.67$ 11,656.67 $ 11,666.6711,666.67$ 11,666.67 $ 11,666.67 $ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.67 11,666.675 IS 10,000.00 S 10,000.00 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 10.000.00 $ 10.000.00 $ 10,000.00 $ 10,000.00 S 10.000.00 $ 10.000.00 S 10.000.00 28,032 $ 665,549.06 385,446,60 % 9,151,299.52 308,357 28 $ 7321,030,62 77,089.32 S 1.830,259.90 S $ 88,784,07 S 3,081,434.12 S 1,030,000.00 S 11,666.67$ 11,666.67 10,000.00 S 360,000.00 S 408,808.02 $12.127,473.85 $ (33,361.42573,061,174.33) 1$ 195, 756.00 S 345, 756.00 $ 383,380 80 $ 383,380.80 $ 473,380.80 $ 415,032.28 $ 326,277.60 $ 476,277.60$ 320,705.94 $ 320,705.94 $ 320,705.94 $206,173.23 206.173.23 206.173.23 $ 408,806.02 408 808.02 15 (33,392.00) 5233,392.00) 5 (36,485.80) (36,485.80) 51126,435.80 128,572.92) 5 (50,761.59) $200,761.60) S (55,386.94) (55,386.94 $ 55,386.94) $ (84,020.11) S (84,020.11) $ (84,020.11) 5 (33,361.42) S (33,361.42) IS 122,364.00 S 122,364.00 S 356,895.00 $ 358,895.00 S 356,895.00$ 396,459.36 $ 285,516.00 $285,516.00 $ 275,319.00 $ 275,319.00 $ 275,219.00 $ 132,153.12$ 132,153.12$ 132,153.12 $ 385,446.80 S 385 446.80S 385,446.60 $ 73,478.40 S 73,416.40 $ 214,137.00 $ 214,137.00 $ 214,137.00 $ 237,875.62 $ 171,309.60 $ 171,309.60 $ 185,191.40 $ 185,191.40 $ 165,191.40 $ 79,291.67 $ 79,291.67 $ 79,291.67 $ 231,287.96 $ 231 267.96 S $ $ 30,591.00 S 30,691,00 $ 30,591.00 S 89,223.75 $ 89,223.75 $ 89,223.75 $ 99,114.84 $ 71,379.00 $ 71,379.00 $ 68,829.75 $ 68,429.75 $ 68,929.75 S 33,038.28 $ 33,038.28 $ 33,038.28 $ 96 361.66 $ S $ 38,238.75 S 18,354,60 S 18,354,60 S 18,354.60 $ 53,534.25 $ 53,534.25 $53,534.25 $ 59,468.905 42,827.405 42,827,40 $ 41,297.85$ 41.297.855 41.297.85 $ 19,822.97$ 19,822.975 19,822.97 S 360,000.00 231,287 965 6.490,779.71 $ 96,361.65 $ 2,191,463.23 57,816.99 S 1,257,060.95 Ts $ 142,248.15 S 122,364,00 S 263,092.60 S 321,715 35 S 356,895.00$ 380,633.62 $ 323,958.69 $ 302,157.50 $ 279,397.80 $ 276,848.55 $ 275,319.00 $ 189,419.47 $ 153,628.00 $ 132,153.12$ 284,129.21 $ 347 452.58S $ , $ $$ 385,446.60 S 9,299,303.89 S S S 184,089.33 S 184,089.33 $ 371,714.13 $ 371,714.13 S 371,714.13 $ 403,365.61 $ 314,610,93 $ 314,610.93 $ 309,039.27 $ 309,039.27 $ 309,039.27 $ 194,506.56 $ 194,506.56 $ 194,506.56 $ 397,141.35 S 397 141.355 397,141.35 510,677,473.73 S 156,475.33 S 156,475,93 $ 315,957 01 $ 315,957.01 315,957.01 $ 342,860.77 $ 267,419.29 $ 267,419.29 $ 262,683.38 5 262.683.38 $ 262,683.38 $ 165,330.585 165,330.58 S 165,330.58 337,570.15 5 337 570.15 S IS 18,408.93 S 18,408.93S 18,408.93 S 37,171.41 S 37.171.41 $ 37,171.41 $ 40,336.56 S 31,461.095 31,461.095 30,903.935 30,903.83 5 30,903.83 5 19.450.66 S 19.450.66 S 19.450.66 S 39.714.135 S 14,506.91 S 9,204.47 $ 9,204.47 $ 9,204.475 18,585.715 18,585.71 $ 18,585.71 $ 20,168.28 S 15,730.55 S 15,730.55 $ 15,451.95 5 15,451.96 5 15,451.96 5 9.725.33 9.725.33 S 9.725.33 IS S S $ 150,000.00 $ 90,000.00 $ 150,000.00 S 11,666.67 S 11,666.6711,656.67 $ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.675 11,666.675 11.666.675 11.666.675 11,666.675 11,666.6711,666.67 $ 11,666.67 $ 11,666.675 S 275,000.00 337,570.15 S 8,842,102.67 39.714.13 S 1.000,533.24 19,857.07 $ 480,409.55 $ 1,030,000.00 11,666.67$ 11,666.67 S $ 201,058.44 S 345 756,00 S 355,237 08 $ 373,599.56 473,380.80 $ 410,284,56 338,008.22$ 480,715.33321,541.69$ 320,984,52 320.705.94 $223,353.14 $ 211,899.87 $ 206.173.23 $ 378,412.80 S 398 676.28 408,808.02 $11,639,712.14 1558,810.29) 51223,392.00) 192,154.48) S (52,284.215116,485.80) 129,650.94)) (14,049.53) 1178,557.83) (42,143.89) (44,135.97) (45,386.94) $ (3,933.67) S (58,271.87) $ 74,020.11) $ (94,283.59) S (51.223.70) S (23,361.42]$ 12,340,408.25) Balance Sheet Projections by Year Opening Balance Sheet As of September 30, 2013 Year 1 Balance Sheet As of September 30, 2014 Year 2 Balance Sheet As of September 30, 2015 Year 3 Balance Sheet As of September 30, 2016 Assets 15,000 $ 360,000 $ (119,263) $ 181,752 $ (116,486) $ 196,292 $ (23,361) 289,085 $ Cash Accounts Recievable Inventory Property, Plant & Equipment Less: Depreciation $ $ 450,000 $ $ 450,000 $ 120,000 $ 450,000 $ 120,000 $ 450,000 120,000 Total Assets $ 825,000 $ 632,490 $ 649,806 $ 835,724 Liabilities Accounts Payable Debt Facility A Interest Payable $ $ $ 275,000 $ 2,000,000 $ 140,000 $ 91,198 $ 1,630,000 $ 140,000 $ 114,679 $ 1,150,000 $ 140,000 $ 109,011 1,000,000 140,000 Total Liabilities $ 2,415,000 $ 1,861,198 $ 1,404,679 $ 1,249,011 Equity Company Equity $ (1,590,000) $ (1,228,708) $ (754,873) $ (413,287) Total Liabilities + Equity $ 825,000 $ 632,490 $ 649,806 $ 835,724 Instructions Complete the Module 5 Assignment using the included Excel Spreadsheet template. This assignment focuses on using data to create financial statement projections and analysis. All assignments must be submitted by 11:59 pm on Sunday. Late assignments will be graded per the Late Assignment Policy posted within the syllabus. The grading rubric is listed below. Prompt Study of Hard Times Turn Around a Toy Company published by Modeloff (Links to company website, for problem see below). INTRODUCTION Slick Micks Toys Inc, is the manufacturer of a type of action figure moderately popular with kids between the ages of 3 and 8 years. Slick Mick's not so slick accountants all have kids that love the figurines but have nonetheless identified that unless there is a swift improvement in cash flow, the business might not make it to the next season. You have been asked to help the team forecast cash flow for the coming three years with particular focus on the next twelve months. Use the following information to draft a three year monthly cash flow forecast with the first month being October 2013 and use it to answer the following key questions. The key assumptions are provided in the Excel file that accompanies this question and are also set out below. KEY ASSUMPTIONS Complete the calculations as indicated in the document entitled Required Calculations. These calculations should be done on the spreadsheet template provided in the appropriate section or on a clearly labeled tab within the spreadsheet. Using the calculated data, create a pro forma cash flow statement, income statement and balance sheet. HINT: If information is not supplied or calculable based on the data provided, do not include it in your model. Module 5 Required Calculations Module 5 Assignment Excel Template ModelOff 2013 Questions and Answers Required Calculations: 1. Calculate the Widget sales for CY2014, CY2015 and CY2016. NOTE: The percentage increase is based on the previous calendar year, not the original year. Example: CY17 sold 100 units, CY18 was an increase of 4%, and CY19 was an increase in sales of 6%. Sales in CY18 = CY17 sales of 100 units x 1.04 = 104 units sold Sales in CY19 = CY18 sales of 104 units x 1.06 = 110 units sold 2. Using the data under current sales price and margin, determine the contribution margin and the variable costs per unit. Formula is Contribution Margin = Sales price - Variable costs Variable costs = Sales price - contribution margin Variable costs = sales price - (contribution margin % x Sales price per unit) 3. Calculate the indirect costs for each calendar year using the same process as with the calculation for Widget Sales. 4. Calculate the cash receipts using the percentage breakdown provided. Example: X Company had $100,000 of sales in January. In the month sold, 10% of revenues are collected; 50% of revenues are collected in the second month, and 40% of sales are collected in the third month. How much is collected from revenues in each month? Month 1 (aka month of sale) = $100,000 x 10% = $10,000 Month 2 = $100,000 x 50% = $50,000 Month 3 = $100,000 x 40% = $40,000 With each new month of sales, the breakdown will be the same percentage for months 1, 2 and 3. Add that breakdown to the previous revenue collection cycles to get the total revenue for that month. 5. Calculate the Cash Receipts on Opening Receivables using the method shown in #4. 6. Calculate the Cash on Payments on Purchases using the method shown in #4. 7. Calculate the Cash Payments on Opening Payables using the method shown in #4. 8. Calculate interest payments using simple interest, as outlined below. Formula for Simple Interest - Principle x rate x time Simple Interest = 2,000,000 x 7% x # of years = total interest for life of the loan So you can assume that the interest per year is 2,000,000 x 7% Annual interest will then = 2,000,000 x 7% = 140,000 per year $140,000 per year / 12 months = $11,666.67 per month interest 9. To calculate any monthly costs from an annual cost divide the annual costs by 12 months, UNLESS a percentage breakdown is provided within the data. Then use the percentage breakdown provided. 10. Using the collected data, complete a cash flow statement broken out by month for each quarter, for the period of CY2013 Q4 through CY2016 - Q3. 11. Using the collected data, complete an income statement broken out by month for each quarter, for the period of CY2013 - Q4 through CY2016 -Q3. 12. Using the collected data, complete a balance sheet broken out by each year for September 30th. MODELOFF Model Off 2013 Round 2 - Hard Times All values in $ unless specified * Note that in this case, no marks will be awarded nor deducted should entrants choose to complete this exercise below or on another tab of this workbook. Assumptions Quarterly seasonality of sales CY Q1 CY Q2 CY Q3 CY Q4 20% 15% 30% 35% assume months within each quarter have equal sales irrespective of the number of days in each month Total Annual Sal CY Q1 CY Q2 CY Q3 CY Q4 Total (Check) Quarter Sales for CY Quarterly Sales for CY 2013 Quarterly Sales for CY 2014 Quarterly Sales for CY 2015 Quarterly Sales for CY 2016 Widget Sales Units Sold Sales Growth CY 2013 CY 2014 CY 2015 CY 2016 175,000 4% 12% 12% assume escalation in sales takes effect instantly from 1 January Sales Per Year in Units Sold Current Sales Price and Margin: Sale Price per unit Contribution Margin 30-Sep.-13 29.99 25% Per Unit CM = Sale Price - Variable Costs Variable Costs = Sale Price - CM Indirect Costs Indirect Costs Indirect Cost Growth CY 2013 CY 2014 CY 2015 CY 2016 $ 1,200,000 4% 4% 4% *Indirect Costs are fixed and occur monthy irrespective of sales. Indirect Costs per Year Indirect Costs per Month Debt amortisation schedule Date 31-Dec.-13$ 28-Feb.-14/$ 30-Apr-14 $ 30-Jun.-14 $ 31-Aug.-14 $ 30-Sep-14$ 30-Nov.-14 $ 31-Dec.-14|$ 31-Mar-15 $ 30-Jun.-15$ 30-Sep.-15 $ 31-Dec.-15$ 50,000 50,000 60,000 60,000 60,000 90,000 60,000 150,000 60,000 150,000 60,000 150,000 Cash Receipts Timing Sale Month +2 Month +1 60% Month +3 15% Cash Receipts on Sales 25% Oct-13 30% Nov-13 60% Dec-13 10% Cash Receipts on Opening Receivables Cash Payments Timing Purchase Month +1 85% Month +2 10% Month +3 5% Cash Payments on Purchases Oct-13 30% Nov-13 60% Dec-13 10% Cash Payments on Opening Payables The above schedule is applicable to both direct and indirect costs Assets and Liabilities = = Assets Cash Accounts Receivable Property Plant and Equipment Total Assets Opening $ 18,000 $ 600,000 $ 500,000 $ 1,118,000 Formula for Simple Interest = Principle x rate x time Simple Interest = 2,000,000 x 7% x # of years = total interest for life of the loan So you can assume that the interest per year is 2,000,000 x 7% Annual interest will then = 2,000,000 x 7% = 140,000 per year $140,000 per year / 12 months = $11,666.67 per month interest = - Interest Liabilities Accounts Payable Debt Facility A Total Liabilities Opening $ 350,000 $ 2,000,000 $ 2,350,000 7.0% *Simple interest p.a with interest paid at month end Depreciation and Capex Depreciation $ 10,000 per month *No capital expenditure is forecast over the next 3 years Taxation Slick Micks has substantial historic tax losses. Assume that no tax will be payable for the period of analysis. Forecast CYFA1 LY.2 LYE 10.14 CYCLE 11 101 14 14:14 11141 E5110 LY3111622 ial 22 18 31-OCHE LYE11 131 10141 1914 31-101 31 1. Na 18 TNT 15 1. Feb 11 1.2.18 31-01 1 A 14 35111 1.Fb 10 FR10 12 1.4p 10 E1 Ny 14 EN Nar10 14 1 141 BL141 1 1.D 14 ES111101 1-101 31 El TA 1.15 31 Y 1 EL 31H11 1.151 11 You Foto Baby Muh Lourde Braxtol Feries FREE in US EDET. Le Verne AXA boas BV TW ETIR 1 w pewn Dewan w TH Porecend Your Chatboy Courier Start al Forted Free Blend What's F1-41 DYE CYCLE 121 14 1 14 31.10 3-10 DYET 121 1.15 1 Feb 151 31.151 5 Feb 15 1 131 val 14015 118 2 Ny 14 101 1 N 30NT13 DYE-41 10 101 114 14 14 10 $1.2010 12 1514 1. Feb 14 20 Fr 101 1 Hy 1411 E1 Ny 14 Ny 101 10141 11 111 3- 151 1. In 14 BLO 14 1or 14 11 31. 121 tv 16 Tweedwh WAR 1 Bukan serta Worth 1 orth Text He Wand 2003 X PICS Prawa Talpern Escudry Dewan ( AT TER Ten Day Reposer theatre Tech Facharta MACHEN CY 2015-02 19 20 Apr 15 1-May-151 Apr 15 31-May-15 21 1-Jun-15 30-Jun-15 CY 2015-03 22 23 1-Jul-15 1 1-Aug-15 31-Jul-15 31-Aug-15 24 1-Sep-15 30-Sep-151 CY 2015-04 25 26 1-Oct-15 1-Nov.15 31-Oct-15 30-Nov-15 27 1-Dec-15 31-Dec-15 CY 2016.01 28 29 1-Jan-18 1-Feb-16 31-Jan-16 29-Feb-10 30 1-Mar-16 31-Mar-16 CY 2018-02 31 32| 1-Apr-16 1-May-16 30-Apr-16 31-May-16 33 1-Jun-16 30-Jun-16 CY 2016.03 35 1-Jul-16 1-Aug-16 31-Jul-16 31-Aug-16 36 3 Year 1-Sep-16 TOTALS 30-Sep-16 CY 2015 - 2 19 20 -Apr-15 1-May-15 Apr 15 31-May-16 21 1-Jun-150 30-Jun-16 CY 2015 - Q3 221 23 1-Jul-15 1-Aug-15 31-Jul-15 31-Aug-151 24 1-Sep-15 30-Sep-15 CY 2015-04 255 26 1-Oct-15 1-Nov-15 31-Oct-15 30-Nov-15 27 1-Dec-15 31-Dec-10 CY 2016 - 1 28 29 1-Jan-16 1-Feb-16 31-Jan-10 29-Feb-16 30 1-Mar-16 31-Mar-16 CY 2016-02 111 32 1-Apr-16 1 1-May-16 und 30-Apr-16 31-May-16 33 1-Jun-16 30-Jun-16 CY 2016 - 23 34 35 1-Jul-16 al 1-Aug-16 31-Jul- 31-Aug-16 36 3 Year 1-Sep-16 TOTALS 30-Sep-16 Balance Sheet Projections by Year Opening Balance Sheet As of September 30, 2013 Year 1 Balance Sheet As of September 30, 2014 Year 2 Balance Sheet As of September 30, 2015 Year 3 Balance Sheet As of September 30, 2016 Assets Cash Accounts Recievable Inventory Property, Plant & Equipment Less: Depreciation Total Assets Liabilities Accounts Payable Debt Facility A Interest Payable Total Liabilities Equity Company Equity Total Liabilities + Equity Based on the data provided from the Hard Times for a Toy Company provided by ModelOff and used on the Week 5 Assignment You have been asked to help the team forecast cash flow for the coming three years with particular focus on the next twelve months. Use the following information to draft a three year monthly cash flow forecast with the first month being October 2013 and use it to answer the following key questions. The key assumptions are provided in the Excel file that accompanies this question and are also set out below. Questions: Q1: What is the forecast Sales Revenue in January 2014? Q2: What is the forecast closing accounts payable in December 2015? Q3: What is the total Indirect Costs expected for the three years forecast? Q4: What is the total interest expense for Calendar Year 2014? Additional Requirements: 1. Create an income statement projection for the outlined period 2. Create a statement of cash flows projection for the outlined period 3. Create a balance sheet projection for the outlined period Assumptions Quarterly seasonality of sales CY Q1 CY 02 CY Q3 CY 04 25% 35% 28% assume months within each quarter have equal sales irrespective of the number of days in each month 12% Quarter Sales for CY Quarterly Sales for CY 2013 Quarterly Sales for CY 2014 Quarterly Sales for CY 2015 Quarterly Sales for CY 2016 Total Annual Sale 200,000 206,000 222,480 240,278 CY Q1 50,000 51,500 55,620 60,070 CY Q2 24,000 24,720 26,698 28,833 CY Q3 70,000 72,100 77,868 84,097 CY Q4 56,000 57,680 62,294 67,278 Total (Check) 200.000 206,000 222,480 240,278 Widget Sales Units sold for CY Q4 of 2013 = 200,000 x 28% Quarter Sales for CY Q4 of 2013 = sales/3 mc 56,000 18,667 Units Sold Sales Growth CY 2013 CY 2014 CY 2015 CY 2016 200,000 3% % 8% 8% % * assume escalation in sales takes effect instantly from 1 January Sales Per Year in Units Sold 200,000 206,000 222,480 240.278 Current Sales Price and Margin: S Sale Price per unit Contribution Margin 30-Sep-13 13.75 20% Per Unit CM = Sale Price - Variable Costs Variable Costs = Sale Price - CM Variable Costs = $13.75 - (20% x $13.75) Variable Costs = $13.75 -2.75 Variable Costs = $11.00 Indirect Costs Indirect Costs Indirect Cost Growth CY 2013 CY 2014 CY 2015 CY 2016 S 975,000 3% 3 3% 3% *Indirect Costs are fixed and occur monthy irrespective of sales. Indirect Costs per Year 975,000 1,004,250 1,034,378 1,065,409 Indirect Costs per Month 81,250 83,688 86,198 88,784 Debt amortisation schedule Date 31-Dec-135 50,000 Debt paid yr 1 Debt paid Y2 Debt Paid Yr 3 28-Feb-145 50,000 S 50.000$ 60,000 S 150,000 30-Apr-145 60,000 S 50,000 $ 150,000 30-Jun-145 60,000 S 60.000$ 60,000 31. Aug.145 60,000 S 80.000 150,000 30-Sep-145 90,000 S 80.000 $ -- 30-Nov-145 60,000 S 90.000 $ 480,000 31-Dec-145 150,000 S 370.000 31-Mar-1515 60,000 30-Jun-15$ 150,000 30-Sep-15 60,000 31-Dec-151 -$ 150,000 $ 1,000,000 60.000 Cash Receipts Timing Sale Cash Receipts on Sales Month +1 Month +2 60% 25% Month +3 15% Oct-13 30% Nov-13 60% Cash Receipts on Opening Receivables Dec-13 10% Cash Payments Timing Purchase Cash Payments on Purchases Month +1 Month +2 Month 3 % 85% 10% 5% Cash Payments on Opening Payables Oct-13 30% % Nov-13 60% Dec-13 10% The above schedule is applicable to both direct and indirect costs Assets and Liabilities Assets Cash Accounts Receivable Property Plant and Equipment Total Assets Opening $ 15.000 $ 360.000 $ 450.000 $ 825.000 Formula for Simple Interest - Principle x rate x time Simple Interest = 2,000,000 x 7% x # of years = total interest for life of the loan So you can assume that the interest per year is 2,000,000 x 7% Annual interest will then - 2,000,000 x 7% - 140,000 per year S140,000 per year / 12 months = $11,666 67 per month interest Liabilities Accounts Payable Debt Facility A Total Liabilities Opening Interest ALTERNATIVE INTEREST: 275,000 Calcuate the amount paid each year, multiply by 7% and use this as the annual interest payment, following the amortization schedule. This gives an answer to #4 of B 2,000,000 7.0%Simple interest p.a with interest paid at month end $ 2.275.000 Depreciation and Capex Depreciation $ 10.000 per month "No capital expenditure is forecast over the next 3 years Taxation Slick Micks has substantial historic tax losses. Assume that no tax will be payable for the period of analysis. Forecast 3 Year Forecasted Income Statement by Month Counter Start of Period End of Period CY 2013-04 21 1-Nov-13 30-Nov-13 3 1-Dec-13 31-Dec-13 CY 2014 - Q1 5 1-Feb-14 28-Feb-14 4 1-Jan-14 31-Jan-14 6 1-Mar-14 31-Mar-14 CY 2014-02 8 1-Apr-14 1-May-14 30-Apr-14 31-May-14 CY 2014 - Q3 10 11 1-Jul-14 1-Aug-14 31-Jul-14 31-Aug-14 1-Oct-13 31-Oct-13 9 1-Jun-14 30-Jun-14 12 1-Sep-14 30-Sep-14 CY 2014 - Q4 14 1-Nov-14 30-Nov-14 13 1-Oct-14 31-Oct-14 15 1-Dec-14 31-Dec-14 CY 2015 - Q1 17 1-Feb-15 28-Feb-15 16 1-Jan-15 31-Jan-15 18 1-Mar-15 31-Mar-15 CY 2015 - Q2 191 201 1-Apr-15 1-May-15 30-Apr-15 31-May-15 21 1-Jun-151 30-Jun-151 30-Sep-13 Sales in Units Sales in Dollars Less Variable Expenses (AKA COGS) Contribution Margin 18,667 18,667 18,667 17,167 17.167 17,167 8,240 8,240 8,240 24,033 24,033 24,033 19,227 19,227 19,227 18,540 18,540 18,540 8,899 8,899 8,899 $ 256,666.67 S 256,666.67 $ 256,666.67 $ 236,041.67 $ 236,041.67 $ 236,041.67 $ 113,300.00 $ 113,300.00 $ 113,300.00 $ 330,458.33 $ 330,458.33 $ 330,458.33 $ 264,366.67 $ 264,366.67 S 264,366.67 $ 254,925.00 S 254,925.00 $ 254,925.00 $ 122,364.00 S 122,364.00 $ 122,364.00 $ 205,333.33 $ 205,333.33 $ 205,333.33 $ 188,833.33 $ 188,833.33 $ 188,833.33 $ 90,640.00 $ 90,640.00 $ 90,640.00 $ 264,366.67$ 264,366.67 $ 264,366.67 $ 211,493.33 $ 211,493.33 $ 211,493.33 $ 203,940.00 $ 203,940.00 $ 203,940.00 $ 97,891.20 S 97,891.20 $ 97,891.20 $ 51,333.33 $ 51,333.33 $ 51,333.33 $ 47,208.33 $ 47,208.33 $ 47,208.33 $ 22,660.00 $ 22,660.00 $ 22,660.00 $ 66,091.67$ 66,091.67$ 66,091.67$ 52,873.33 $ 52,873.33 $ 52,873.33 $ 50,985.00 $ 50,985.00 $ 50,985.00 $ 24,472.80 $ 24,472.80 $ 24,472.80 Indirect Costs (AKA Fixed Expenses) Long Term Debt Repayment Interest Expense Depreciation $ 81,250.00 $ 81,250.00 $ 81,250.00 $ 83,687.50 S $ 50,000.00 S $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 83,687.50 $ 50,000.00 11,666.67 $ 10,000.00 $ 83,687.50 $ 83,687.50 $ 83,687.50 $ 83,687.50 $ 83,687.50 $ 83,687.50 $ $ 60,000.00 $ 60,000.00 $ 60,000.00 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67$ 11,666.67 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 83,687.50 $ 83,687.50 S 83,687.50 S 83,687.50 $ 86,198.13 S 90,000.00 S 60,000.00 $ 150,000.00 11,666.67 $ 11,666.67$ 11,666.67$ 11,666.67 $ 11,666.67 S 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 86,198.13 $ $ 11,666.67$ 10,000.00 $ 86,198.13 $ 86,198.13 $ 86,198.13 $ 86,198.13 60,000.00 $ 150,000.00 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 Total Expenses (Excluding Depreciation) $ 275,000.00 $ 298,250.00 $ 298,250.00 $ 348,250.00 $ 284,187.50 $ 334,187.50 S 284,187.50 $ 245,994.17 S 185,994.17 $ 245,994.17 S 359,720.84 S 419,720.84 $ 449,720.84 $ 306,847.50 S 366,847.50 S 456,847.50 $ 301,804.80 S 301,804.80 $ 361,804.80 S 195,756.00S 195,756.00 $ 345,756.00 Net Income/Loss S (51,583.34) S (51,583.34) $ (101,583.34) $ (58,145.84) S (108,145.84) S (58,145.84) S (142,694.17) S (82,694.17) S (142,694.17) $ (39,262.50) S (99,262.50) $ (129,262.50) $ (52,480.84) S (112,480.84) S (202,480.84) $ (56,879.79) S (56,879.79) S (116,879.80) S (83,392.00) S (83,392.00) $ (233,392.00) Forecasted 3 Year Statement of Cash Flows by Month Total Revenue Collected (Cash Inflows) Sales in Dollars (Revenue) $ 360,000.00 $ 256,666.67 $ 256,666.67 $ 256,666.67 $ 236,041.67 $ 236,041.67 $ 236,041.67 $ 113,300.00 $ 113,300.00 $ 113,300.00 $ 330,458.33 $ 330,458.33 $ 330,458.33 $ 264,366.67 $ 264,366.67 $ 264,366.67 $ 254,925.00 $ 254,925.00 $ 254,925.00 $ 122,364.00 $ 122,364.00 $ 122,364.00 Opening A/R Month 1 Month 2 Month 3 60% of Revenue 25% of Revenue 15% of Revenue $ 108,000.00 $ 216,000.00 $ 36,000.00 $ 154,000.00 $ 154,000.00 $ 154,000.00 $ 141,625.00 $ 141,625.00 $ 141,625.00 $ 67,980.00 S 67,980.00 $ 67,980.00 $ 198,275.00 $ 198,275.00 $ 198,275.00 $ 158,620.00 $ 158,620.00 $ 158,620.00 $ 152,955.00 $ 152,955.00 $ 152,955.00 $ 73,418.40 $ 73,418.40 $ $ $ 64,166.67$ 64,166.67 $ 64,166.67$ 59,010.42 $ 59,010.42 $ 59,010.42 $ 28,325.00 $ 28,325.00 $ 28,325.00$ 82,614.58 $ 82,614.58 $ 82,614.58 $ 66,091.67 $ 66,091.67 $ 66,091.67 $ 63,731.25 $ 63,731.25 S 63,731.25 $ 30,591.00 $ $ $ 38,500.00 $ 38,500.00 $ 38,500.00 $ 35,406.25 $ 35,406.25 $ 35,406.25 $ 16,995.00 $ 16,995.00 S 16,995.00 $ 49,568.75 $ 49,568.75 S 49,568.75 S 39,655.00 $ 39,655.00 $ 39,655.00 38,238.75 $ 38,238.75 S 38,238.75 $ 73,418.40 30,591.00 18,354.60 Total Revenue Collected (Cash Inflows) $ 262,000.00 $ 434,166.67 $ 292,666.67 $ 244,291.67$ 239,135.42 $ 236,041.67 $ 162,396.67 $ 131,711.25 $ 113,300.00 $ 243,595.00 $ 297,884.58 $ 330,458.33 $ 290,803.33 $ 274,280.42 $ 264,366.67$ 258,701.67 $ 256,341.25 $ 254,925.00 $ 175,388.40 $ 142,248.15 $ 122,364.00 Schedule of Accounts Payable - Cash Payments (Outflows) Total Expenses (Excluding Depreciation) $ 275,000.00 $ 286,583.33 $ 286,583.33 $ 286,583.33 $ 272,520.83 $ 272,520.83 $ 272,520.83 $ 174,327.50 $ 174,327.50 $ 174,327.50 $ 348,054.17 S 348,054.17 $ 348,054.17 $ 295,180.83 $ 295,180.83 $ 295,180.83 $ 290,138.13 $ 290,138.13 $ 290,138.13 $ 184,089.33 $ 184,089.33 $ 184,089.33 Opening A/P Month 1 85% of Expenses Month 2 10% of Expenses Month 3 5% of Expenses Long Term Debt Repayment Interest Expense $ 82,500.00 $ 165,000.00 $ 27,500.00 $ 243,595.83 S 243,595.83 $ 243,595.83 $ 231,642.71 $ 231,642.71 S 231,642.71 $ 148,178.38 $ 148,178.38 $ 148, 178.38 $ 295,846.04 $ 295,846.04 $ 295,846.04 $ 250,903.71 $ 250,903.71 $ 250,903.71 $ 246,617.41 S 246,617.41 $ 246,617.41 $ 156,475.93 $ 156,475.93 $ 156,475.93 $ $ 28,658.33 $ 28,658.33 $ 28,658.33 $ 27,252.08 $ 27,252.08 $ 27,252.08 $ 17,432.75 $ 17,432.75 $ 17,432.75 $ 34,805.42 $ 34,805.42 $ 34,805.42 $ 29,518.08 S 29,518.08 $ 29,518.08 29,013.81 $ 29,013.81 $ 29,013.81 $ 18,408.93 $ 18,408.93 $ $ $ 14,329.17 $ 14,329.17 $ 14.329.17 $ 13,626.04 $ 13,626.04 $ 13,626.04 $ 8,716.38 $ 8,716.38 $ 8,716.38 $ 17,402.71 $ 17,402.71 $ 17,402.71 $ 14,759.04 $ 14,759.04 $ 14,759.04 $ 14,506.91 $ 14,506.91 $ 14,506.91 $ 9,204.47 $ 50,000.00 $ 50,000.00 $ 60,000.00 $ 60,000.00 $ 60,000.00 $ 90,000.00 $ 60,000.00 $ 150,000.00 $ 60,000.00 $ 150,000.00 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 $ 11,666.67 Total Cash Paid Each Month (Outflows) $ 337,762.50 S 448,920.84 $ 375,750.00 $ 286,296.88 $ 334,890.63 $ 284,187.50 $ 260,723.17 $ 190,903.84 $ 245,994.17 $ 333,661.84 $ 411,034.50 $ 449,720.84 $ 314,778.50 $ 369,491.17 $ 456,847.50 $ 302,561.20 S 302,056.93 $ 361,804.80 $ 211,663.32 $ 201,058.44 $ 345,756.00 Net Cash Flow $ (75,762.50) $ (14,754.17) $ (83,083.34) $ (42,005.21) $ (95,755.21) $ (48,145.84) $ (98,326.50) $ (59,192.59) $ (132,694.17) $ (90,066.84) $ (113,149.92) $ (119,262.50) $ (23,975.17) $ (95,210.75) $ (192,480.84) $ (43,859.53) $ (45,715.68) $ (106,879.80) $ (36,274.92) $ (58,810.29) $ (223,392.00) 1 1 36 3 Your 1-Sep-16 TOTALS 30-Sep-16 - CY 2015 - az CY 2015 - Q3 CY 2015-04 CY 2016-1 CY 2016 - az CY 2016 - 20 21 22 23 24 25 26 27 281 29 30 31 32 331 34 35 1-May-15 1-Jun-15 1-Jul-15 1-Aug-15 1-Sep-15 1-Oct-15 1-Nov-15 1-Dec-15 1-Jan-16 1-Feb-16 1-Mar-161 1-Apr 16 1-May-16 1-Jun-16 1-Jul-16 1. Aug.16 31-May-15 30-Jun-15 31-Jul-15 31-Aug-15 30-Sep-15 31-Oct-110 - 30-Nov-101 31-Dec-15 31-Jan-16 29-Feb-101 31-Mar-16 30-Apr-161 31-May-16 30-Jun-16| - 31-Jul-16 31-Aug-161 8,899 8,899 25,958 25,956 25,956 28,833 20.765 20.765 20.023 20.0231 20.0231 9,6111 9,611 9,6111 28,032 28,032 1$ 122,364,00 S 122,364,00 S 356,895.00 S 356,895.00 S 356,895.00 S 396,459.36 $ 285,516.00 $ 285,516.00 $ 275,319.00 $ 275,319.00 $ 275,319.00 $ 132,153.125 132.153.12 $ 132.153.12 $ 385,446.605 385 446.605 5 $5 S 97,891.20 S 97,891.20 S 285,516.00 S 285,516.00 $ 285,516.00 S 317,167.49 $ 228,412.80 $ 228,412.80 $ 220,255,20 5 220.255.20 $ 220,255.20 $ 105,722.50 S 105,722.50 S 106.722.50 S 308,357.28 S 308 357.285 S 24,472.80 S 24,472.80 S 71,379,00 S 71,379.00 S 71,379.00 S 79,291.87 $ 57,103.20 $ 57,103.20 $ 55,063.80 $ 55,063.80 $ 55,063.80 5 26,430.62 S 26,430.62 5 26,430.62 5 77,089.32 S 77.089.32S S 86,198.13S 86,198.13 $ 86,198.13 $ 85,198.135 86,198.135 86,198.13 $ 86,198.13 86,198.135 88,784.07 S 88,784.075 88,784.075 88,784.07 5 88.784.07 S 88.784.07 S 88.784.07 S 88.784.07 S 1S 150,000.00 IS 90,000.00 1$ 150,000.00 1$ 11,666.67$ 11,666.67$ 11,656.67 $ 11,666.6711,666.67$ 11,666.67 $ 11,666.67 $ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.67 11,666.675 IS 10,000.00 S 10,000.00 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 10.000.00 $ 10.000.00 $ 10,000.00 $ 10,000.00 S 10.000.00 $ 10.000.00 S 10.000.00 28,032 $ 665,549.06 385,446,60 % 9,151,299.52 308,357 28 $ 7321,030,62 77,089.32 S 1.830,259.90 S $ 88,784,07 S 3,081,434.12 S 1,030,000.00 S 11,666.67$ 11,666.67 10,000.00 S 360,000.00 S 408,808.02 $12.127,473.85 $ (33,361.42573,061,174.33) 1$ 195, 756.00 S 345, 756.00 $ 383,380 80 $ 383,380.80 $ 473,380.80 $ 415,032.28 $ 326,277.60 $ 476,277.60$ 320,705.94 $ 320,705.94 $ 320,705.94 $206,173.23 206.173.23 206.173.23 $ 408,806.02 408 808.02 15 (33,392.00) 5233,392.00) 5 (36,485.80) (36,485.80) 51126,435.80 128,572.92) 5 (50,761.59) $200,761.60) S (55,386.94) (55,386.94 $ 55,386.94) $ (84,020.11) S (84,020.11) $ (84,020.11) 5 (33,361.42) S (33,361.42) IS 122,364.00 S 122,364.00 S 356,895.00 $ 358,895.00 S 356,895.00$ 396,459.36 $ 285,516.00 $285,516.00 $ 275,319.00 $ 275,319.00 $ 275,219.00 $ 132,153.12$ 132,153.12$ 132,153.12 $ 385,446.80 S 385 446.80S 385,446.60 $ 73,478.40 S 73,416.40 $ 214,137.00 $ 214,137.00 $ 214,137.00 $ 237,875.62 $ 171,309.60 $ 171,309.60 $ 185,191.40 $ 185,191.40 $ 165,191.40 $ 79,291.67 $ 79,291.67 $ 79,291.67 $ 231,287.96 $ 231 267.96 S $ $ 30,591.00 S 30,691,00 $ 30,591.00 S 89,223.75 $ 89,223.75 $ 89,223.75 $ 99,114.84 $ 71,379.00 $ 71,379.00 $ 68,829.75 $ 68,429.75 $ 68,929.75 S 33,038.28 $ 33,038.28 $ 33,038.28 $ 96 361.66 $ S $ 38,238.75 S 18,354,60 S 18,354,60 S 18,354.60 $ 53,534.25 $ 53,534.25 $53,534.25 $ 59,468.905 42,827.405 42,827,40 $ 41,297.85$ 41.297.855 41.297.85 $ 19,822.97$ 19,822.975 19,822.97 S 360,000.00 231,287 965 6.490,779.71 $ 96,361.65 $ 2,191,463.23 57,816.99 S 1,257,060.95 Ts $ 142,248.15 S 122,364,00 S 263,092.60 S 321,715 35 S 356,895.00$ 380,633.62 $ 323,958.69 $ 302,157.50 $ 279,397.80 $ 276,848.55 $ 275,319.00 $ 189,419.47 $ 153,628.00 $ 132,153.12$ 284,129.21 $ 347 452.58S $ , $ $$ 385,446.60 S 9,299,303.89 S S S 184,089.33 S 184,089.33 $ 371,714.13 $ 371,714.13 S 371,714.13 $ 403,365.61 $ 314,610,93 $ 314,610.93 $ 309,039.27 $ 309,039.27 $ 309,039.27 $ 194,506.56 $ 194,506.56 $ 194,506.56 $ 397,141.35 S 397 141.355 397,141.35 510,677,473.73 S 156,475.33 S 156,475,93 $ 315,957 01 $ 315,957.01 315,957.01 $ 342,860.77 $ 267,419.29 $ 267,419.29 $ 262,683.38 5 262.683.38 $ 262,683.38 $ 165,330.585 165,330.58 S 165,330.58 337,570.15 5 337 570.15 S IS 18,408.93 S 18,408.93S 18,408.93 S 37,171.41 S 37.171.41 $ 37,171.41 $ 40,336.56 S 31,461.095 31,461.095 30,903.935 30,903.83 5 30,903.83 5 19.450.66 S 19.450.66 S 19.450.66 S 39.714.135 S 14,506.91 S 9,204.47 $ 9,204.47 $ 9,204.475 18,585.715 18,585.71 $ 18,585.71 $ 20,168.28 S 15,730.55 S 15,730.55 $ 15,451.95 5 15,451.96 5 15,451.96 5 9.725.33 9.725.33 S 9.725.33 IS S S $ 150,000.00 $ 90,000.00 $ 150,000.00 S 11,666.67 S 11,666.6711,656.67 $ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.67$ 11,666.675 11,666.675 11.666.675 11.666.675 11,666.675 11,666.6711,666.67 $ 11,666.67 $ 11,666.675 S 275,000.00 337,570.15 S 8,842,102.67 39.714.13 S 1.000,533.24 19,857.07 $ 480,409.55 $ 1,030,000.00 11,666.67$ 11,666.67 S $ 201,058.44 S 345 756,00 S 355,237 08 $ 373,599.56 473,380.80 $ 410,284,56 338,008.22$ 480,715.33321,541.69$ 320,984,52 320.705.94 $223,353.14 $ 211,899.87 $ 206.173.23 $ 378,412.80 S 398 676.28 408,808.02 $11,639,712.14 1558,810.29) 51223,392.00) 192,154.48) S (52,284.215116,485.80) 129,650.94)) (14,049.53) 1178,557.83) (42,143.89) (44,135.97) (45,386.94) $ (3,933.67) S (58,271.87) $ 74,020.11) $ (94,283.59) S (51.223.70) S (23,361.42]$ 12,340,408.25) Balance Sheet Projections by Year Opening Balance Sheet As of September 30, 2013 Year 1 Balance Sheet As of September 30, 2014 Year 2 Balance Sheet As of September 30, 2015 Year 3 Balance Sheet As of September 30, 2016 Assets 15,000 $ 360,000 $ (119,263) $ 181,752 $ (116,486) $ 196,292 $ (23,361) 289,085 $ Cash Accounts Recievable Inventory Property, Plant & Equipment Less: Depreciation $ $ 450,000 $ $ 450,000 $ 120,000 $ 450,000 $ 120,000 $ 450,000 120,000 Total Assets $ 825,000 $ 632,490 $ 649,806 $ 835,724 Liabilities Accounts Payable Debt Facility A Interest Payable $ $ $ 275,000 $ 2,000,000 $ 140,000 $ 91,198 $ 1,630,000 $ 140,000 $ 114,679 $ 1,150,000 $ 140,000 $ 109,011 1,000,000 140,000 Total Liabilities $ 2,415,000 $ 1,861,198 $ 1,404,679 $ 1,249,011 Equity Company Equity $ (1,590,000) $ (1,228,708) $ (754,873) $ (413,287) Total Liabilities + Equity $ 825,000 $ 632,490 $ 649,806 $ 835,724
