Assignment #2 - Case Study: Forecasting Financial Statements & Ratios 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 Overvio 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, cach 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 Cost of Sales Advertising Equipment Rental Wages Office Rent Utilities Insurance Expense Depreciation Interest Expense Income Taxes Additional Instructions 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 comer, 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 cach 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 mistakes are being made. You must upload your file to Blackboard under Week 5 Assignments (Include your name as part of the file name). Go to the Assignment, scroll down to "Attach Local File" and click Browse to select YOUR file, then hit SUBMIT Income Statement (15%) Evaluation Criteria for: Assignment 2 (100 points) Gross Margin-5 points Operating Income-5 points Net Income-5 points 20 points Balance Sheet (15%) 15 points Assets-5 points Liabilities-5 points Stockholder's Equity-Spoints Statement of Cash Flows (20%) Cash flows from operating activities-5 points Cash flows from investing activities - 5 points Cash flows from financing activities - 5 points Ending Cash balance-5 points 20 points Ratios (50%) Ratios - 45 points (1 point for cach ratio -9 ratios across 5 years) 50 points Ratio Analysis -(0-5 points) A score of 5 will be awarded for responses that fully addresses all questions listed in the text box. Response must be reasonable and logical. *Refer to grading area on each tab within the Excel Workbook. FORECASTED INCOME STATEMENT YEAR 1 YEAR 2 Revenue Cost of Sales Gross Margin Operating Expenses Advertising Equipment Rental wages office Rent Lutilities insurance Expense Depreciation Operating Income 6 interest Expense Income before taxes B income taxes 9 Net Income - PR FORECASTED BALANCE SHEET Assumption Explanations YEAR 1 YEAR YEAR) YEAR 4 YEARS is cash 6 Accounts Receivable 7 Prepaid Insurance 8 Current Assets 9 Furniture & Fixtures (net) 10 Equipment (net) 11 Total Assets 13 Liabilities 14 Accounts payable 15 Customer depouts 16 Interest payable 17 Taxes payable 18 Current Liabilities Bank loan 20 Total Liabilities 22 Stockholder's Equity 2 Capital 24 Retained Earnings 25 Total Stockholder's Equity 26 Total Liabilities & Stockholder's Equity CASHFLOW STATEMENT Assumptions Assumption Explanations YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEARS Net Income Add back depreciation Changes in working capital Accounts Receivable Prepaid Expenses Accounts Payable Customer Deposits 2 Interest payable Taxes Payable Cash flows from operating activities 7 Amount paid for equipment Amount paid for furniture & fixtures 9 Cash flow from investing activities 2 Proceeds from bank loan 3 Proceeds from issuing capital 24 Repayment of bank loan 25 Cash flow from financing activities 27 Net Increase (decrease) in cash 28 Addi Beginning cash balance 29 Ending Cash balance RATIOS Show Work YEAR1 YEAR 2 YEAR YEAR 4 4 ROUND TO TWO DECIMAL PLACES 5 CURRENT RATIO 6 AVERAGE COLLECTION PERIOD 7 PAYABLES TURNOVER TOTAL ASSET TURNOVER 9 DEBT RATIO 10 TIMES INTEREST EARNED 11 GROSS PROFIT MARGIN 12 NET PROFIT MARGIN 13 RETURN ON EQUITY 17 Comment on the projected health of the company Years 1-5 in terms of liquidity, activity, and profitability. As a financial 18 analyst, what suggestions would you make to anvelle to improve certain ratios. If you were the bank would you give kanelle 19 the business loan? NONEN Assignment #2 - Case Study: Forecasting Financial Statements & Ratios 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 Overvio 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, cach 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 Cost of Sales Advertising Equipment Rental Wages Office Rent Utilities Insurance Expense Depreciation Interest Expense Income Taxes Additional Instructions 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 comer, 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 cach 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 mistakes are being made. You must upload your file to Blackboard under Week 5 Assignments (Include your name as part of the file name). Go to the Assignment, scroll down to "Attach Local File" and click Browse to select YOUR file, then hit SUBMIT Income Statement (15%) Evaluation Criteria for: Assignment 2 (100 points) Gross Margin-5 points Operating Income-5 points Net Income-5 points 20 points Balance Sheet (15%) 15 points Assets-5 points Liabilities-5 points Stockholder's Equity-Spoints Statement of Cash Flows (20%) Cash flows from operating activities-5 points Cash flows from investing activities - 5 points Cash flows from financing activities - 5 points Ending Cash balance-5 points 20 points Ratios (50%) Ratios - 45 points (1 point for cach ratio -9 ratios across 5 years) 50 points Ratio Analysis -(0-5 points) A score of 5 will be awarded for responses that fully addresses all questions listed in the text box. Response must be reasonable and logical. *Refer to grading area on each tab within the Excel Workbook. FORECASTED INCOME STATEMENT YEAR 1 YEAR 2 Revenue Cost of Sales Gross Margin Operating Expenses Advertising Equipment Rental wages office Rent Lutilities insurance Expense Depreciation Operating Income 6 interest Expense Income before taxes B income taxes 9 Net Income - PR FORECASTED BALANCE SHEET Assumption Explanations YEAR 1 YEAR YEAR) YEAR 4 YEARS is cash 6 Accounts Receivable 7 Prepaid Insurance 8 Current Assets 9 Furniture & Fixtures (net) 10 Equipment (net) 11 Total Assets 13 Liabilities 14 Accounts payable 15 Customer depouts 16 Interest payable 17 Taxes payable 18 Current Liabilities Bank loan 20 Total Liabilities 22 Stockholder's Equity 2 Capital 24 Retained Earnings 25 Total Stockholder's Equity 26 Total Liabilities & Stockholder's Equity CASHFLOW STATEMENT Assumptions Assumption Explanations YEAR 1 YEAR 2 YEAR 3 YEAR 4 YEARS Net Income Add back depreciation Changes in working capital Accounts Receivable Prepaid Expenses Accounts Payable Customer Deposits 2 Interest payable Taxes Payable Cash flows from operating activities 7 Amount paid for equipment Amount paid for furniture & fixtures 9 Cash flow from investing activities 2 Proceeds from bank loan 3 Proceeds from issuing capital 24 Repayment of bank loan 25 Cash flow from financing activities 27 Net Increase (decrease) in cash 28 Addi Beginning cash balance 29 Ending Cash balance RATIOS Show Work YEAR1 YEAR 2 YEAR YEAR 4 4 ROUND TO TWO DECIMAL PLACES 5 CURRENT RATIO 6 AVERAGE COLLECTION PERIOD 7 PAYABLES TURNOVER TOTAL ASSET TURNOVER 9 DEBT RATIO 10 TIMES INTEREST EARNED 11 GROSS PROFIT MARGIN 12 NET PROFIT MARGIN 13 RETURN ON EQUITY 17 Comment on the projected health of the company Years 1-5 in terms of liquidity, activity, and profitability. As a financial 18 analyst, what suggestions would you make to anvelle to improve certain ratios. If you were the bank would you give kanelle 19 the business loan? NONEN