Extra Credit Assignment - Case Study: Forecasting Financial Statements & Ratios

W N. FORECASTED BALANCE SHEET Assumptions Assumption Explanations Should agree to the Statement of Cash Flows YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 Assets 5 Cash $70,328.77 $45,336.96 $35,051.99 $30,602.63 $28,003.83 6 Accounts Receivable $8,219.18 $9,383.56 $8,048.22 $8,853.04 $9,295.69 7 Prepaid Insurance 8 Current Assets 78,548 54,721 43,100 39,456 37,300 9 Furniture & Fixtures (net) $7,466.67 $6,933.34 $6,400.01 $5,866.68 $5,333.35 10 Equipment (net) $8,000 $6,000 $4,000 $2,000 50 11 Total Assets 94,015 67,654 53,500 47,322 42,633 12 13 Liabilities 14 Accounts payable $5,547.95 $7,042.19 $5,070.38 $5,577.42 $5,856.29 15 Interest payable $1,500 $1,200 $900 $600 $300 16 Taxes payable/(receivable ) $491.67 50 $1,029.54 $2,411.16 $3,185.70 17 Current Liabilities 7,540 8,242 7,000 8,589 9,342 18 Bank loan $75,000 $60,000 $45,000 $30,000 $15,000 19 Total Liabilities 82,540 68,242 52,000 38,589 24,342 20 21 Stockholder's Equity 22 Capital 23 Retained Earnings 24 Total Stockholder's Equity 25 Total Liabilities & Stockholder's Equity 82,540 68,242 52,000 38,589 24,342 26CASHFLOW STATEMENT Assumptions Assumption Explanations YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 Enter all decreases Net Income $1,475 ($13,063.33) $3,088.63 $7.233.48 $$9557.10 as negative numbers Add back depreciation $2.533 $2,533 $2.533 $2,533 $2,533 7 Changes in working capital 8 Accounts Receivable Enter decreases as negative bombers 9 Prepaid Expenses 10 Accounts Payable 11 Interest payable $0 $ 12 Takes Payable 13 14 Cash flows from operating activi 4.008 (10.530) 5,622 9,767 2.533 15 16 17 Amount paid for equipment ($10,000) $0 $0 $0 $0 18 Amount paid for furniture & fixtures ($8,000.00) $0 $0 $0 $0 19 Cash flow from investing activiti, [18,000) 20 21 22 Proceeds from bank loan $75,000 $0 $0 $0 $0 23 Proceeds from issuing capital $0 $0 $0 $0 $0 24 Repayment of bank loan $0 ($15,000) ($15,000) ($15,000) ($15,000] 25 Cash flow from financing activiti 75,000 [15,000) [15,000) [15,000) (15,000) 26 27 Net increase (decrease) in cash 61,008 (25.530) (9.378) (5.233) (12.467) 28 Add: Beginning cash balance $10,000 $70,328.77 $45,336.36 $35,051.99 $30,602.63 29 Ending Cash balance 71,008 44,799 35,959 29,819 18,1364 Bank Loan $ 75,000 Term 5 yrs 7 rate 2% 8 installments $ 15,000 9 10 accrued interest S 1,500 11 12 13 Furniture & 14 Fitxtures 8000 15 life 15 yrs 16 Depreciation S 533 17 18 Computers 10000 19 life 5 yrs 20 Depreciation 2000 21RATIOS YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 4 *ROUND TO TWO DECIMAL PLACES 5 CURRENT RATIO 6 TOTAL ASSET TURNOVER 7 DEBT RATIO 8 TIMES INTEREST EARNED 0.00 0.00 9 GROSS PROFIT MARGIN 10 NET PROFIT MARGIN (%) 11 RETURN ON EQUITY (%) 12 13 14 15 Comment on the projected health of the company (Years 1-5) in terms of liquidity, activity , and profitability. As a financial 16 analyst, what suggestions would you make to Janelle to improve certain ratios. If you were the bank would you give Janelle 17 the business loan? 18 19 20 21 22 23FORECASTED INCOME STATEMENT Assumptions Assumption Explanations YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 Revenue $100,000 $102,000 $97,920 $107,712 $113,097.60 5 Cost of Sales $45,000 $57,120 $41,126 $45,239 $47,500.99 6 Gross Margin 55,000 44,880 56,794 62,473 65,597 7 Operating Expenses 8 Advertising $3,000 $3,060 $2,937.60 $3,231.36 $3,392.93 Website/Content Creation $5,000 $5,150 $5,304.50 $5,463.64 $5,627.54 10 Wages $20,000 $20,000 $20,000 $20,000 $20,000 11 Office Rent $18,000 $18,000 $18,000 $18,000 $18,000 12 Utilities $3,000 $3,000 $3,000 $3,000 $3,000 13 Insurance Expense $0.00 SO SO $0 14 Depreciation $2,533.33 $2,533.33 $2,533.33 $2,533.33 $2,533.33 15 Operating Income 3,467 (6,863) 5,019 10,245 13,043 16 Interest Expense $1,500 $10,555 $900 $600 300 17 Income before taxes 1,967 (17,418) 4,118 9,645 12,743 18 Income taxes 492 (4,354 1,030 2,411 3,186 19 Net Income/(Loss) 1,475 (13,063) 3,089 7,233 9,557