Income Statement
FORECASTED INCOME STATEMENT Assumptions Assumption Explanations GRADING
YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 Cell Ref. Round Co
ect #s Co
ect/Inco
ect Points Awarded
Revenue GM - Yr 1 0 82500 INCORRECT 0
Cost of Sales GM - Yr 2 0 84975 INCORRECT 0
Gross Margin - - - - - GM - Yr 3 0 87524 INCORRECT 0
Operating Expenses GM - Yr 4 0 91,900 INCORRECT 0
Advertising GM - Yr 5 0 96495 INCORRECT 0
Equipment Rental
Wages Operating Income - Yr 1 0 39900 INCORRECT 0
Office Rent Operating Income - Yr 2 0 42195 INCORRECT 0
Utilities Operating Income - Yr 3 0 37559 INCORRECT 0
Insurance Expense Operating Income - Yr 4 0 36817 INCORRECT 0
Depreciation Operating Income - Yr 5 0 41078 INCORRECT 0
Operating Income - - - - -
Interest Expense Net Income - Yr 1 0 24765 INCORRECT 0
Income before taxes - - - - - Net Income - Yr 2 0 26647 INCORRECT 0
Income taxes Net Income - Yr 3 0 24023 INCORRECT 0
Net Income - - - - - Net Income - Yr 4 0 23931 INCORRECT 0
Net Income - Yr 5 0 26700 INCORRECT 0
TOTAL 0
Balance Sheet
FORECASTED BALANCE SHEET Assumptions Assumption Explanations GRADING
YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 Cell Ref. Round Co
ect #s Co
ect/Inco
ect Points Awarded
Assets Assets - Yr 1 0 133400 INCORRECT 0
Cash
Dr. Charmaine Felder: Should agree to the Statement of Cash Flows Assets - Yr 2 0 142960 INCORRECT 0
Accounts Receivable Assets - Yr 3 0 156971 INCORRECT 0
Prepaid Insurance Assets - Yr 4 0 162,252 INCORRECT 0
Cu
ent Assets - - - - - Assets - Yr 5 0 194444 INCORRECT 0
Furniture & Fixtures (net)
Equipment (net) Liabilities - Yr 1 0 78635 INCORRECT 0
Total Assets - - - - - Liabilities - Yr 2 0 61548 INCORRECT 0
Liabilities - Yr 3 0 41536 INCORRECT 0
Liabilities Liabilities - Yr 4 0 22886 INCORRECT 0
Accounts payable Liabilities - Yr 5 0 28377 INCORRECT 0
Customer deposits
Interest payable S/E & Liab.- Yr 1 0 133400 INCORRECT 0
Taxes payable S/E & Liab.- Yr 2 0 142960 INCORRECT 0
Cu
ent Liabilities - - - - - S/E & Liab.- Yr 3 0 156971 INCORRECT 0
Bank loan S/E & Liab.- Yr 4 0 162252 INCORRECT 0
Total Liabilities - - - - - S/E & Liab.- Yr 5 0 194444 INCORRECT 0
TOTAL 0
Stockholder's Equity
Capital
Retained Earnings
Total Stockholder's Equity - - - - -
Total Liabilities & Stockholder's Equity - - - - -
Cashflow Statement
CASHFLOW STATEMENT Assumptions Assumption Explanations GRADING
YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 Cell Ref. Round Co
ect #s Co
ect/Inco
ect Points Awarded
Dr. Charmaine Felder: Enter all subtractions as negative numbers
Operating - Yr 1 0 36400 INCORRECT 0
Net Income Operating - Yr 2 0 18560 INCORRECT 0
Add back depreciation Operating - Yr 3 0 35011 INCORRECT 0
Changes in working capital Operating - Yr 4 0 34,281 INCORRECT 0
Accounts Receivable Operating - Yr 5 0 35192 INCORRECT 0
Prepaid Expenses
Accounts Payable Investing - Yr 1 0 -25000 INCORRECT 0
Customer Deposits Investing - Yr 2 0 0 INCORRECT 0
Interest payable Investing - Yr 3 0 0 INCORRECT 0
Taxes Payable Investing - Yr 4 0 0 INCORRECT 0
Cash flows from operating activities - Investing - Yr 5 0 0 INCORRECT 0
Financing- Yr 1 0 60000 INCORRECT 0
Amount paid for equipment Financing- Yr 2 0 -20000 INCORRECT 0
Amount paid for furniture & fixtures Financing- Yr 3 0 -10000 INCORRECT 0
Cash flow from investing activities - Financing- Yr 4 0 -20000 INCORRECT 0
Financing- Yr 5 0 0 INCORRECT 0
Proceeds from bank loan Ending Cash Bal. - Yr 1 0 101400 INCORRECT 0
Proceeds from issuing capital Ending Cash Bal. - Yr 2 0 99960 INCORRECT 0
Repayment of bank loan Ending Cash Bal. - Yr 3 0 124971 INCORRECT 0
Cash flow from financing activities - Ending Cash Bal. - Yr 4 0 139252 INCORRECT 0
Ending Cash Bal. - Yr 5 0 174444 INCORRECT 0
Net increase (decrease) in cash - TOTAL 0
Add: Beginning cash balance
Ending Cash balance -
Ratios
RATIOS Formulas Show Work GRADING
YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEAR 5 Cell Ref. Round Co
ect #s Points Awarded
*ROUND TO TWO DECIMAL PLACES CR - Yr 1 0.00 5.98 0
CURRENT RATIO CR - Yr 2 0.00 5.75 0
AVERAGE COLLECTION PERIOD CR - Yr 3 0.00 6.55 0
PAYABLES TURNOVER CR - Yr 4 0.00 6.52 0
TOTAL ASSET TURNOVER CR - Yr 5 0.00 6.5 0
DEBT RATIO
TIMES INTEREST EARNED AVG COLLECTION - Yr 1 0.00 24.33 0
GROSS PROFIT MARGIN AVG COLLECTION - Yr 2 0.00 28.35 0
NET PROFIT MARGIN AVG COLLECTION - Yr 3 0.00 18.35 0
RETURN ON EQUITY AVG COLLECTION - Yr 4 0.00 13.11 0
AVG COLLECTION - Yr 5 0.00 20.80 0
PAYABLES - Yr 1 0.00 33.75 0
PAYABLES - Yr 2 0.00 17.38 0
PAYABLES - Yr 3 0.00 14.32 0
PAYABLES - Yr 4 0.00 12.53 0
PAYABLES - Yr 5 0.00 8.77 0
TOT. ASSET - Yr 1 0.00 1.12 0
TOT. ASSET - Yr 2 0.00 1.08 0
TOT. ASSET - Yr 3 0.00 1.01 0
TOT. ASSET - Yr 4 0.00 1.03 0
TOT. ASSET - Yr 5 0.00 0.90 0
DEBT RATIO - Yr 1 0.00 0.59 0
DEBT RATIO - Yr 2 0.00 0.43 0
DEBT RATIO - Yr 3 0.00 0.26 0
DEBT RATIO - Yr 4 0.00 0.14 0
DEBT RATIO - Yr 5 0.00 0.15 0
TIE - Yr 1 0.00 22.17 0
TIE - Yr 2 0.00 35.16 0
TIE - Yr 3 0.00 62.60 0
TIE - Yr 4 0.00 0.00 0
TIE - Yr 5 0.00 0.00 0
GPM - Yr 1 0.00 0.55 0
GPM - Yr 2 0.00 0.55 0
GPM - Yr 3 0.00 0.55 0
GPM - Yr 4 0.00 0.55 0
GPM - Yr 5 0.00 0.55 0
NPM - Yr 1 0.00 0.17 0
NPM - Yr 2 0.00 0.17 0
NPM - Yr 3 0.00 0.15 0
NPM - Yr 4 0.00 0.14 0
NPM - Yr 5 0.00 0.15 0
ROE - Yr 1 0.00 0.45 0
ROE - Yr 2 0.00 0.33 0
ROE - Yr 3 0.00 0.21 0
ROE - Yr 4 0.00 0.17 0
ROE - Yr 5 0.00 0.16 0
TOTAL 0
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?
Due Sunday Week 5
100 Points
Students will construct a set of forecasted financial statements and calculate a set of financial ratios for a start-up business.
Note: Please read the documents related to forecasting financial statements under Documents & Resources in Week 4. Additionally, search online for information regarding forecasting financial statements. [Don’t skip this step! It will provide background context and insight into what you’ll be doing for this assignment.] You will notice that there are many methods to use to forecast financials. Some are quantitative while others are qualitative. Often times, a mix of the two are used. There is no one exact way to perform forecasting.
For the purpose of this assignment, you will be relying on the directions given under ‘Forecasting Assumptions’ to complete the Excel workbook.
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 Day1 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 payed 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
Year 2
Year 3
Year 4
Year 5
Accounts Receivable
10,000
12,000
8,000
6,000
10,000
Accounts Payable
2,000
4,000
5,000
6,000
9,000
Customer Deposits
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.
Chart of Accounts
All accounts listed here should be included in your financial statements.
· Cash
· Accounts Receivable
· Prepaid Insurance
· Furnitures & Fixtures
· Equipment
· Accounts Payable
· Customer Deposits
· Interest Payable
· Taxes Payable
· Bank Loan
· Capital
· Retained Earnings
· Revenue
· Cost of Sales
· Advertising
· Equipment Rental
· Wages
· Office Rent
· Utilities
· Insurance Expense
· Depreciation
· Interest Expense
· Income Taxes
Additional Instructions & Guidance
· You must use the given Excel template attached to this assignment.
· The three financial statements are interconnected. Where applicable, reference the appropriate cells from other tabs. Also, use formulas within the cells where applicable. (Instructors may deduct points for not using cell references and formulas.) There are some formulas already included in various cells – do not alter these formulas.
· Some cells have additional notes (cells marked with a red triangle in the upper right-hand corner; hover over the triangle to see the note).
· Review Chapter 4 for helpful information in completing the Cash Flow Statement.
· The shaded areas are for data input. (NOTE: You will need to enter formulas to calculate subtotals on the ‘Cash Flow Statement’ tab for each major category)
· On each tab there is an area to keep track of the various assumptions. The column labeled ‘Assumptions’ is to make note of any numbers, percentages, etc. relevant to that line item. The column labeled ‘Assumption Explanations’ is to help keep track of the ‘Forecasting Assumptions’ from above. (You can copy and paste the assumptions onto the relevant line item). On the ‘Ratios’ tab there is an area to layout the formulas and show your work.
· There is a grading area on each tab that is being calculated as you input information. Please use this area to see where any