Week 2 Financial Ratio Analysis and Forecasting

Overview

Financial ratio analysis is an important tool that businesses use to evaluate their performance and to help determine how to best manage their resources. Businesses use this analysis to safeguard their financial health and to ensure that they provide reasonable returns to shareholders. Financial ratios are tools that help companies, investors, and other stakeholders analyze the relationships between different pieces of financial information found on a company’s financial statements. Financial ratios are used to assess four dimensions of a company’s financial situation: profitability, asset management, debt burden, and liquidity.   

Financial forecasts are future predictions of a company’s financial performance. They are used by start-up companies as well as established firms with operating histories. Companies forecast their performance as reflected in their expected future financial statements. The most critical forecast with respect to a company’s survivability is its expected cash flow over the periods forecasted.

What You’ll Need

  • Your MacBook Pro laptop
  • Internet access
  • MS Word software

Prepare

  • Complete the Week 2 Review assignment.
  • Part 1 – Download the Week 2 Project Financial Statements document from the Downloads section.
  • Part 1 – Download the Week 2 Project (Part 1) Answer Sheet.
  • Part 2 – Download Project 2_Part 2Template. 
  • EBS160_Project2(Part1)AnswerSheet1_MW2_Feb7.docx32 KB
  • Week2ProjectFinancialStatements.pdf107 KB
  • EBS160_Project2Part2Template_MW2z_Feb7.xlsx52 KB

Create

Part 1

Using the Week 2 Project Financial Statements for The Disney Company and Apple Inc. download document, complete the Week 2 Project (Part 1) Answer Sheet. Include the following information:

  1. Calculate both companies’ asset turnover ratios for 2017 and 2016. Show your computations.
  2. Calculate both companies’ net profit margins for 2017 and 2016. Show your computations.
  3. Calculate both companies’ debt ratios for 2017 and 2016.
  4. Calculate both companies’ return on equity ratios for 2017 and 2016.
  5. Calculate both companies’ current ratios for 2017 and 2016.

Part 2

In this part of the assignment you will help develop a financial forecast for a new entertainment company called The 2Live Venue. This is a small live music venue, where indie bands perform in front of a live audience four times each week.

Your goals are to make the business profitable within 2 years on the income statement and ensure that the business raises enough money on the capital investments page so that it never runs out of cash (which can be determined on the cash flow statement).  

You will first demonstrate that the 2Live Venue is likely to break even and earn a profit within two years. To do this, open the Excel workbook within the Project 2 Part 2 Template. You will then input the appropriate numbers in the yellow highlighted cells of the Sales Projections and Capital Investments tabs. Since the financial statements are interconnected, information input in the Sales Projections and Capital Investments cells will automatically update information on the Income Statement and Cash-Flow Statement tabs. No input is required on the Marketing Budget and Capital Expenses tabs, as these expenses cannot be changed.

Sales Projections:

If the venue capacity is 300 seats, and the venue starts out at 50% capacity during the first quarter, what unit volume should you expect, and how many tickets does this translate into in each quarter during the two years? (Shows are 4 nights per week, 4 weeks per month, 3 months per quarter, over the course of the two-year forecasted period.) (50% x 300 x 4 x 4 x 3) In Excel enter this as: =(0.5*300*4*4*3)

Enter this information on the “Sales Projections” tab of the workbook in the “Unit Volume” under the ticket sales revenue stream for Quarter 1.

Now that you have completed Quarter 1, show some growth over time in the ticket sales unit volume line. In the other quarters, use the same formula you just used =(50% x 300 x 4 x 4 x 3), but change the percentage of seats sold in each quarter, as follows: 

For Quarter 2: 55% of seats sold 

For Quarter 3: 58% of seats sold 

For Quarter 4: 67% of seats sold 

For Quarter 5: 63% of seats sold 

For Quarter 6: 68% of seats sold 

For Quarter 7: 80% of seats sold 

For Quarter 8: 88% of seats sold 

Note: beverages and merchandise have automatic formulas that are based on ticket sales.

Income Statement:

On the “Income Statement” tab, notice at the bottom of the statement what the net profit or net loss is for each quarter throughout the two years. The business should show a total profit for the two years of $179,792.65.

Cash-Flow Statement:

Look at the “Cash-Flow Statement” tab of the workbook. Determine how much money needs to be raised so that there are no months with end-of-month negative cash flow. Notice that the “Cash Flow at End of Month” is negative throughout the two years. This is like a bank account balance. If it goes below zero, it means the business is projected to run out of cash and cannot operate. Therefore, cash at the end of the month must always be positive to keep the business alive long enough for it to break even on the income statement.

Find the largest negative number on this statement (the biggest negative number). This is the amount of capital (money) the business needs to raise from owners and investors to keep it operational. You should see the number (343,476).

Now, let’s show in our projections that we are raising this amount of money from the owner and an investor.

Capital Investments:

Click on the “Capital Investments” tab in the workbook. 

>span class=”s1″ data-redactor-span=”true”>What combination of funding sources, how much for each, and how much equity should be given up in order to raise your target amount? Remember that the largest negative number on the Cash Flow Statement was (343,476). Let’s show that we’re raising $350,000 to cover all those negative balances in our bank account. To keep things simple, we’re going to assume we only get money from two sources of capital: the owner’s money and investors’ money (venture capitalists or angel investors).

Enter 100,000 under Contribution amount in the Owner section. 

If the owner has invested $100,000 of their own money, and needs a total of $350,000 of capital, then that means we need to raise an additional $250,000 from outside investors. Let’s say we find a shark willing to invest the whole $250,000 that we need.

Enter 250,000 under “Investment Amount” in the Investor section.

How much of the ownership of the company would we be willing to give up in exchange for their $250,000? How about 30%? In real life, we’d want to give up as little as possible, while maintaining at least 50% of the ownership ourselves so we remain in control of the business.

Enter 30 under “Equity %” in the “Private Investor” section.

(Think about this: if we are giving up 30% of the company to an investor, how much do we own? That’s right, 70%.) 

Enter 70 under “Equity %” in the “Owner 1” section.

However we split this up in real life, the total must be 100% down below next to “TOTAL.”

Submit

  1. Save your completed Week 2 Project (Part 1) Answer Sheet as a PDF document.
  2. Name your Week 2 Project (Part 1) Answer Sheet document as follows: LASTNAME_FIRSTNAME_Project2_Part1
  3. Save your Project 2 Part 2 Template as an Excel file, and rename the file as follows: LASTNAME_FIRSTNAME_Project 2_Part 2 
  4. Upload both the Part 1 and Part 2 files to LAO and Submit.
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *