I have done most of the project, there are some cells that I couldn't figure out I, Help me, please. Thank You.
Ps :I can email the excel sheet if you need it.
The income statement is all correct 
For the Balance sheet I need help with
assets years3,4and 5
liabilities year 3
total liabilities and stockholder equity year 3,4 and 5
For the Cashflow statement, I need help with :
I need help with operating year 3
ending cash balance for year 3,4 and 5
For the ratios I need help with the blank spaces, the rest is correct. Thank you so much
These are the instructions
FORECASTED INCOME STATEMENT YEAR 1 $150,000 67,500 $82,500 YEAR 2 154,500 69,525 84,975 YEAR 3 159,135 71,611 87,524 YEAR 4 167,092 75,191 91,900 YEAR 5 175,446 78,951 96,495 Revenue Cost of Sales Gross Margin Operating Expenses Advertising Equipment Rental Wages Office Rent Utilities Insurance Expense Depreciation Operating Income Interest Expense Income before taxes Income taxes Net Income 6,000 6,000 9,600 14,400 3,600 6,180 6,000 9,600 14,400 3,600 6,365 9,000 9,600 14,400 3,600 4,000 3,000 37,559 600 36,959 12,936 24,023 6,684 9,000 14,400 14,400 3,600 4,000 3,000 36,817 7,018 9,000 14,400 14,400 3,600 4,000 3,000 41,078 3,000 39,900 1,800 38,100 13,335 24,765 3,000 42,195 1,200 40,995 14,348 26,647 36,817 12,886 23,931 41,078 14,377 26,700 FORECASTED BALANCE SHEET umptioption Explanat GRADING YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 Points Awarded 1 1 101,400 10,000 171,324 10,000 Assets Cash Accounts Receivable Prepaid Insurance Current Assets Furniture & Fixtures (net) Equipment (net) Total Assets 0 0 Cell Ref. Assets - Yr 1 Assets - Yr 2 Assets - Yr 3 Assets - Yr 4. Assets - Yr 5 99,960 12,000 12,000 123,960 13000 6,000 142,960 Correct/Incorrect CORRECT CORRECT INCORRECT INCORRECT INCORRECT 120,171 8,000 8,000 136,171 12000 4,000 152,171 136,132 6,000 4,000 146,132 11000 2,000 159,132 111,400 14,000 8,000 133,400 0 181,324 10000 1 CORRECT CORRECT 191,324 1 Liabilities - Yr 1 Liabilities - Yr 2 Liabilities - Yr 3 Liabilities - Yr 4 Liabilities - Yr 5 0 INCORRECT CORRECT CORRECT 1 1 6,000 4,000 9,000 5,000 Liabilities Accounts payable Customer deposits Interest payable Taxes payable Current Liabilities Bank loan Total Liabilities 2,000 1,500 1,800 13,335 60,000 1 4,000 2,000 1,200 14,348 21,548 40,000 61,548 5,000 3,000 600 11,256 19,856 20,000 39,856 1 12,886 22,886 14,377 28,377 S/E & Liab.- Yr 1 S/E & Liab.- Yr 2 S/E & Liab.-Yr 3 S/E & Liab.-Yr 4 S/E & Liab.-Yr 5 0 CORRECT CORRECT INCORRECT INCORRECT INCORRECT TOTAL 0 78,635 22,886 28,377 0 8 Stockholder's Equity Capital Retained Earnings Total Stockholder's Equity Total Liabilities & Stockholder's Equity 30,000 24,765 54,765 133,400 30,000 51,412 81,412 142,960 40,000 72,315 112,315 152,171 40,000 96,246 136,246 159,132 40,000 122,946.0 162,946 191,323 CASHFLOW STATEMENT GRADING YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 1 26,647 23,931 24,765 3,000 24,023 3,000 26,700 3,000 mption Explan Cell Ref. Operating - Yr 1 Operating - Yr 2 Operating - Yr 3 Operating - Yr 4 Operating - Yr 5 Correct/Incorrect Points Awarded CORRECT CORRECT 1 INCORRECT 0 CORRECT 1 CORRECT 3,000 3,000 (10,000) 4,000 (4,000) 4,000 4,000 Net Income Add back depreciation Changes in working capital Accounts Receivable Prepaid Expenses Accounts Payable Customer Deposits Interest payable Taxes Payable Cash flows from operating activities (2,000) (12,000) 2,000 500 (600) 1,013 18,560 2,000 1,500 1,800 13,335 36,400 3,000 1,000 2,000 4,000 1,000 1,000 (600) (50) 34,281 1,000 1,000 (600) (1,412) 36,423 Investing - Yr 1 Investing - Yr 2 Investing - Yr 3 Investing - Yr 4 Investing - Yr 5 CORRECT CORRECT CORRECT CORRECT CORRECT 1 1 1 1 1,491 35,192 1 Amount paid for equipment Amount paid for furniture & fixtures Cash flow from investing activities (10,000) (15,000) (25,000) Financing-Yr 1 Financing-Yr 2 Financing-Yr 3 Financing-Yr 4 Financing-Yr 5 CORRECT CORRECT CORRECT CORRECT CORRECT 1 1 1 60,000 1 1 Proceeds from bank loan Proceeds from issuing capital Repayment of bank loan Cash flow from financing activities 10,000 (20,000) (20,000) (20,000) (20,000) (10,000) (20,000) Ending Cash Bal. - Yr 1 Ending Cash Bal. - Yr 2 Ending Cash Bal. - Yr 3 Ending Cash Bal. - Yr 4 Ending Cash Bal. - Yr 5 0 CORRECT CORRECT INCORRECT INCORRECT INCORRECT TOTAL 60,000 0 0 101,400 16 Net increase (decrease) in cash Add: Beginning cash balance Ending Cash balance (1,440) 101,400 99,960 24,023 99,960 123,983 14,281 123,983 138,264 35,192 141,944 177,136 101,400 YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 5.98 24.33 5.75 28.35 18.35 13.11 20.80 *ROUND TO TWO DECIMAL PLACES CURRENT RATIO AVERAGE COLLECTION PERIOD PAYABLES TURNOVER TOTAL ASSET TURNOVER DEBT RATIO TIMES INTEREST EARNED GROSS PROFIT MARGIN NET PROFIT MARGIN RETURN ON EQUITY 1.01 26% 1.12 59% 22.17 55% 17% 45% 1.08 43% 35.16 55% 17% 33% 1.03 14% 0.00 55% 14% 17% 0.90 15% 0.00 55% 15% 16% 55% 15% 21% 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? . Case Overview Janelle Higgins has decided to start her own event planning business (JH Events) in the upcoming year. She believes that she has an innovative business model and is seeking a business loan. Therefore, Janelle is in the process of putting together a business plan. As part of her business plan, Janelle 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. Janelle must also include a set of calculated ratios along with an overall assessment on the projected health of the company. Forecasting Assumptions First year sales are projected to be $150,000 and grow 3% for the next two years and 5% in year 4 and 5. Cost of sales are projected to be 45% of revenue in the first year and is expected to grow at the same rate as sales. Advertising expenses are projected to be 4% of each year's projected revenue. Janelle will need to rent equipment for the events she puts on. She has an agreement with a rental company for a flat rate of $500 per month and is sufficient to cover all of her estimated events. Starting in Year 3, she expects to be able to handle more events and the rental rate will increase to $750 per month. Janelle plans to start off by hiring 2 people to work the events with her. Each person will be paid $100 per event and is expected to work 4 events per month. After Year 3, Janelle plans to hire 2 additional people. (At this point, each person will be paid $100 per event and is expected to work 3 events per month). Office rent is estimated to be a flat rate of $1,200 per month. Utilities for the rented office space are estimated to be $300 per month. The office space Janelle plans to rent is unfurnished. She plans to purchase $15,000 worth of furniture and fixtures at the beginning of Year 1. The furniture and fixtures will have a useful life of 15 years. Janelle will also need to buy some computers and other . . . office equipment that she will purchase 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). HINT: Fixed Assets - Accumulated Depreciation = 'Net' Fixed Assets Janelle is asking for a 3-year bank loan for $60,000 to be funded on Dayl of Year 1. The estimated interest on the loan is 3% (assume simple interest). She will pay the loan back in $20,000 installments starting in Year 2. Interest is due at the end of each year and paid in January of the following year. (Assume interest is. paid on the principal balance still outstanding at year-end). The tax rate for JH Events is 35%. Taxes for the year just ended are paid in the first quarter of the following year. Janelle will invest $30,000 of her own money and from family and friends, before the beginning of the year, to start the business. This $30,000 investment of capital is also the beginning bank balance of Year 1. Schedule of expected year-end balances of selected accounts: Year 1 10,000 Year 2 12,000 Year 3 8,000 Year 4 6,000 Year 5 10,000 Accounts Receivable Accounts Payable Customer Deposits 2,000 4,000 5,000 6,000 9,000 1,500 2,000 3,000 4,000 5,000 At the end of Year 2,Janelle 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 $12,000. During Year 3, Janelle plans to personally invest $10,000 of additional capital into the business
