Case Study: Forecasting Financial Statements & Ratios

Case Overview Ben Francis has decided to start an Internet business selling quality workout gear in the upcoming year. He believes that his products are innovative and tending well and is seeking a business loan. Therefore, Ben is in the process of putting together a business plan. As part of the business plan process, Ben must include forecasted financial statements for the first 5 years. The forecasted financial statements to be included are: Income Statement, Balance Sheet, and Statement of Cash Flows. Forecasting Assumptions First year sales are projected to be $100,000 and grow/decline at the following rates: Year 1 Year 2 + 2% Year 2 Year 3 -4% Year 3 Year 4 +10% Year 4 Year 5 + 5% Cost of sales are projected based on percentage of revenue as follows: Year 1 Year 2 45% Year 2 Year 3 56% Year 3 Year 4 42% Year 4 Year 5 42% Advertising expenses are projected to be 3% of each years projected revenue. Ben will outsource the creation and maintenance of his proprietary website with an outsourcing contract starting at $5,000 in year one and a 3% escalation clause each year. A part-time assistant will be hired stating on day one/year one for $20,000 per year. Office rent is estimated to be a flat rate of $1,500 per month under a five-year lease agreement with no escalation clause for the duration of the lease. Utilities for the rented office space are estimated to be $250 per month. The office space be to rented is unfurnished. The company will purchase $8,000 worth of furniture and fixtures at the beginning of Year 1. The furniture and fixtures will have a useful life of 15 years. The company will also purchase some computers and other office equipment for $10,000 also at the beginning of Year 1. The office equipment will have a useful life of 5 years. Both the furniture and fixtures as well as the equipment will be depreciated on a straight-line basis (Assume zero salvage value for calculations). A 5-year bank loan will be negotiated for $75,000. The estimated interest on the loan is 2% (assume simple interest). It will be paid back in $15,000 installments starting in Year 2. Interest is due at the end of each year and paid in January of the following year. The tax rate is 25%. Taxes for the year just ended are payed in the first quarter of the following year. Ben will invest $10,000 of her own money and from family and friends to start the business. This $10,000 investment of capital is also the beginning bank balance of Year 1. Accounts Receivable at year end is equal to 30 days of sales. Accounts Payable at year end is equal to 45 days of purchases (cost of sales) At the end of Year 2, the company will purchase an insurance policy to help cover the business. The policy has a term of 3 years and coverage starts at the beginning of Year 3. The policy costs $5,000.

*Please use templates below for all statements, it's required. Thank you!

FORECASTED INCOME STATEMENT Assumptions Assumption Explanations YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 Revenue Cost of Sales Gross Margin Operating Expenses Advertising Website/Content Creation Wages Office Rent Utilities Insurance Expense Depreciation Operating Income Interest Expense Income before taxes Income taxes Net Income/(Loss) FORECASTED BALANCE SHEET Assumptions Assumption Explanations YEAR 1 YEAR 3 YEAR 4 YEAR 5 Assets Cash Accounts Receivable Prepaid Insurance Current Assets Furniture & Fixtures (net) Equipment (net) Total Assets Liabilities Accounts payable Interest payable Taxes payable/(receivable ) Current Liabilities Bank loan Total Liabilities Stockholder's Equity Capital Retained Earnings Total Stockholder's Equity Total Liabilities & Stockholder's Equity CASHFLOW STATEMENT Assumptions Assumption Explanations YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 Net Income Add back depreciation Changes in working capital Accounts Receivable Prepaid Expenses Accounts Payable Interest payable Taxes Payable Cash flows from operating activities Amount paid for equipment Amount paid for furniture & fixtures Cash flow from investing activities Proceeds from bank loan Proceeds from issuing capital Repayment of bank loan Cash flow from financing activities Net increase (decrease) in cash Add: Beginning cash balance Ending Cash balance RATIOS Formulas Show Wor YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 *ROUND TO TWO DECIMAL PLACES CURRENT RATIO TOTAL ASSET TURNOVER DEBT RATIO TIMES INTEREST EARNED GROSS PROFIT MARGIN NET PROFIT MARGIN (%) RETURN ON EQUITY (%) Comment on the projected health of the company (Years 1-5) in terms of liquidity, activity, and profitability. As a financial analyst, what suggestions would you make to Janelle to improve certain ratios. If you were the bank would you give Janelle the business loan? FORECASTED INCOME STATEMENT Assumptions Assumption Explanations YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 Revenue Cost of Sales Gross Margin Operating Expenses Advertising Website/Content Creation Wages Office Rent Utilities Insurance Expense Depreciation Operating Income Interest Expense Income before taxes Income taxes Net Income/(Loss) FORECASTED BALANCE SHEET Assumptions Assumption Explanations YEAR 1 YEAR 3 YEAR 4 YEAR 5 Assets Cash Accounts Receivable Prepaid Insurance Current Assets Furniture & Fixtures (net) Equipment (net) Total Assets Liabilities Accounts payable Interest payable Taxes payable/(receivable ) Current Liabilities Bank loan Total Liabilities Stockholder's Equity Capital Retained Earnings Total Stockholder's Equity Total Liabilities & Stockholder's Equity CASHFLOW STATEMENT Assumptions Assumption Explanations YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 Net Income Add back depreciation Changes in working capital Accounts Receivable Prepaid Expenses Accounts Payable Interest payable Taxes Payable Cash flows from operating activities Amount paid for equipment Amount paid for furniture & fixtures Cash flow from investing activities Proceeds from bank loan Proceeds from issuing capital Repayment of bank loan Cash flow from financing activities Net increase (decrease) in cash Add: Beginning cash balance Ending Cash balance RATIOS Formulas Show Wor YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 *ROUND TO TWO DECIMAL PLACES CURRENT RATIO TOTAL ASSET TURNOVER DEBT RATIO TIMES INTEREST EARNED GROSS PROFIT MARGIN NET PROFIT MARGIN (%) RETURN ON EQUITY (%) Comment on the projected health of the company (Years 1-5) in terms of liquidity, activity, and profitability. As a financial analyst, what suggestions would you make to Janelle to improve certain ratios. If you were the bank would you give Janelle the business loan