finance代考 FIN311 Exam 2

FIN311

Exam 2, Take Home

finance代考 You may work with one other student (from either class). You will need to inform me if you will be working with another student.

You may work with one other student (from either class). You will need to inform me if you will be working with another student. In addition, you will need to inform me of the firm you will be studying. Only 25 students/teams of students can work on each firm. Students who do not fill out the Google form before 1pm on Sunday, March 21 will be assigned a firm.

You will be submitting a 4-5 page written paper, along with a spreadsheet with: finance代考

1. The following financial statements for the firm you selected (each in its own sheet)

a.2020 Balance Sheet (this will include the data from the 2018 Balance Sheet that you manually add)

b.2020 Income Statement

c.2020 Statement of Cash Flows

2.Ratio Analysis in a separate sheet

Please complete all calculations in your spreadsheet. You must use cell references. Points will be deducted for students not using Excel for calculations. Clearly label all worksheets in your spreadsheet so I can easily follow your work. Your written paper should include a brief introduction and conclusion and answer the questions below, using the calculations in your spreadsheet for support. Clearly state which items in your spreadsheet you are referring to in your paper (use the column letter and row number).

Your written paper should include responses to the questions below. You should discuss and explain your financial analysis of the firm in your paper. Any assumptions must be clearly discussed in your paper.

Using the financial statements of the firm you selected, answer the following questions (your response must be clearly written in your paper):

Introduction Questions (20%, your introduction and conclusion are included in this 20%):

1. Briefly describe your firm, including the products and services it sells. Include any other information you believe is relevant to the focus of this paper (financial analysis of a real world company). This should be only 1-2 paragraphs. Please note: answering the first question is NOT an introduction. You need to include a general introduction that introduces your entire paper.

Chapter 3 Questions (50%) (see below for information on downloading the financial statements in Excel):

2. Create Common Size: finance代考

a.*Income Statement (include this in your Income Statement worksheet) for the past three (3) years.

b.*Balance Sheet (include this in your Balance Sheet worksheet) for the past three (3) years.

c.What three (3) observations can you make for each statement?

i.Three (3) for balance sheet

ii.Three (3) for income statement

3.Complete the ratio analysis for the most recent two (2) years. This must be computed using the Ratio Analysis Template provided on myCourses.

a.*Compute the ratios for each category:

i.Liquidity: Compute the current ratio, quick ratio, cash ratio and net working capital ratio

ii.Long Term Solvency: Compute the debt ratio, the debt-equity ratio, the equity multiplier and the long-term debt ratio

iii. Ability to pay interest: Compute the times-interest earned & cash coverage ratio

iv.Asset Management: Compute the inventory turnover, days sales in inventory, receivables turnover, days sales outstanding, total asset turnover, and fixed asset turnover

v.Profitability: Compute the profit margin, return on assets and return on equity

vi.Market Value: Compute the PE ratio, the market to book ratio and the EBITDA ratiosb. How has the firm’s profitability changed over the past two (2) years?

b.How does your firm compare to the industry?

c.Select two (2) other categories of ratios (see lecture notes or your text book for the specific categories to choose from). Explain how this category has changed over the past two (2) years? Explain how your firm compared to the industry?

d.Using Dupont analysis, show how the firm’s ROE has changed over the past two (2) years. What are the significant changes that most impacted the change in the firm’s ROE?

Chapter 4 Questions (30%): finance代考

4. Using finance.yahoo.com, find the expected growth rate in sales for your firm

a.See below for more details

b.Clearly state the growth rate and the date you accessed this data point in your written paper.

5.Using this growth rate, project out the 2021 Income Statement (include this in your Income Statement worksheet)

a.Assumptions

i.The following items will vary with sales (use the 2020) Income Statement to determine each item’s percent of sales)

• COGS
• S,G & A
• Other Income
• There may be others, use your own judgement here and highlight (in yellow) all items that vary with sales in your worksheet

ii.Assume the following items will not change in 2021

• Depreciation and Amortization (if stated on the Income Statement)
• Interest
• Any expenses related to debt
• There may be others, use your own judgement here and highlight (in any color other than yellow) all items that do not vary with sales in your worksheet

6. Determine how much the firm will pay out in dividends and what the Additions to Retained Earnings will be: finance代考

a.You will need to make a realistic assumption (clearly discuss this in your written paper).

i.The most the firm can pay out in dividends is the projected Net Income for 2021 (or 2020)

ii.The least the firm can pay out in dividends is zero

iii. You can compute the 2020 payout ratio and use that to estimate

• If the payout ratio > 100%, do not use this to estimate
• If the payout ratio > 100%, assume the firm will pay out 50% of the projected Net Income in dividends in 2021

iv.Firms can pay out more than 100% of the Net Income earned in dividends. To do so they will need to use some of the firm’s cash or issue debt and/or equity. I will leave this complexity for FIN324. You may use the simplifying assumptions above.

7. Using the Percent of Sales method, project out the 2021 Balance Sheet (include this in your Balance Sheet worksheet)

a.Assets:

i.The following items will vary with sales:

• All current assets
• PPE
• Operating leases
• Other assets
• There may be others, use your own judgement here and highlight (in yellow) all items that vary with sales in your worksheet

ii.The following items will not vary with sales: finance代考

• Goodwill
• There may be others, use your own judgement here and highlight (in a color other than yellow) all items that do not vary with sales in your worksheet

iii. Use the Percent of Sales method for individual line items. Add up the totals for Total Current Assets and Total Assets

b.Liabilities

i.The following items will vary with sales:

• Accounts payable
• Accrued expenses
• Income taxes payable
• Operating lease expenses
• There may be others, use your own judgement here and highlight (in yellow) all items that vary with sales in your worksheet

ii.All remaining items in the Liabilities section will not vary with sales. Highlight these in the same color you used in the Income Statement (not yellow)

iii. Add up the totals for Current Liabilities and Total Liabilities (you may need to add a row for Total Liabilities to your worksheet)

c.Owners’ Equity

i.The only item that will change is the Retained Earnings. This will depend on the assumption you have made about Dividends. The Retained Earnings for 2021 (or 2020) will be equal to the Retained Earnings on the Balance Sheet in 2020 (or 2021) + Additions to Retained Earnings you projected.

ii.All other line items will remain the same as 2020 (or 2019)

iii. Add up the total for Owner’s Equity

Clearly discuss all of your assumptions in your paper. Summarize where appropriate.

Please note: your projected Balance Sheet for 2021 will not balance.

(You are welcome to make your own assumptions, if you do not agree with what I have presented here. If you decide to make an assumption other than what I have presented here, please clearly state them.)

8. Based on your projections, what is the firm’s External Financing Needed? What recommendations do you have for the firm to meet these needs? List three (3) ways the firms can balance the projected 2021 Balance Sheet.

This exam only covers material explicitly from Chapters 3 and 4. The material in Chapter 2 is included implicitly.

To help you with this assignment, I have provided some guidance below:

Accessing Materials: finance代考

To download financial statements in Excel format

Downlaod the template posted on myCourses

– Download XBRL (this will include most recent 2 years for Balance Sheet, you will need 3. Details on how to access the 2020 (or 2019) Balance Sheet are provided below). You should use the most recent 10K available. For some firms this will be 2020, for others it will be 2019.

o Use the following website: https://www.sec.gov/edgar/searchedgar/companysearch.html

o Enter company name

o You should see the latest 10K under ‘Latest Filings’ (to the left of the screen) Click on Filing (to the right of the document) to access the XBRL format of the 10K

 If you do not see the 10K, click on View Filings

 Scroll down until you find the most recent 10K

 Click on Filing, then follow directions below

o Click on Interactive Data (towards the top, left of your screen)

o Click on ‘View Excel Document’ View Excel Document

 This will download an Excel spreadsheet of all of the firm’s financial statements

o Add the following worksheets (title of worksheets may be different) to the Ratio Analysis spreadsheet you have already downloaded:

 Income Statement

 Balance Sheet

 Cash Flow Statement

 Keep only the 3 financial statements listed above. Make sure each financial statement is a separate worksheet within your spreadsheet.

o Now you should be ready to complete the ratio analysis

– To Add the 2018 Balance Sheet information: finance代考

o Use PDF posted on myCourses for your firm and manually add the 2018 Balance Sheet to your spreadsheet

 Do your best to add the items

 Line items and names of line items may have changed since 2018

 You only need to add rows in order to add line items that existed in 2018, but no longer exist

 Enter a zero for any line items that are in the more current financial statements, but not the 2018 finance statements

 You may need to make some assumptions when adding the earlier balance sheet

 Clearly state any assumptions you make

o Add the Ratio Analysis template posted on myCourses

 You can create this manually, but use the ‘Move or Copy…’ feature in Excel to copy the entire sheet into your spreadsheet will make this easier for you

 You may only submit one (1) spreadsheet with your exam

Data items needed from finance.yahoo.com:

1. Stock Price (at the end of the firm’s fiscal year):

a) Search for the firm you are analyzing (you can search by firm name or ticker)

b) Click on the Historical Data tab

c) You can change the Time Period to quickly find the stock price

d) Use the Close on the date on or before the firm’s fiscal date

1. Projected growth rate in sales:

a) Click on Analysis tab

b) Under ‘Revenue Estimate’ find the Sales Growth estimate for 2021

Guidelines: finance代考

a.Use Averages for the following Balance Sheet items

i.Long term solvency ratios

ii.Asset Management ratios

iii. Profitability ratios

b.Do not use Averages for the following Balance Sheet items (use year-end figure)

i.Short term solvency

• Since both numerator and denominator are from the Balance Sheet

ii.Market Value ratios

• Since we want the value of the firm as of the firm’s fiscal date

c.Be sure to use the appropriate stock price (on or before the firm’s fiscal date for each year)

d.Use ‘shares issued and outstanding’ as stated in the Common Stock line item on the balance sheet for shares outstanding

i.If your firm does not list ‘shares issued and outstanding’, compute (shares issued – treasure stock)

ii.You can also find the firm’s 10K (in PDF format) from their investor relations website to find this number

iii. It might be included on the XBRL spreadsheet you downloaded

e.Use Basic EPS (if stated on the income statement) finance代考

i.You can also compute this yourself

ii.Do not use diluted (for anything)

f.EBIT is sometimes called ‘Operating Income’ or ‘Income from Operations’ (or something else) on the Income Statement

g.Use (Cash + Short Term Investments) for Cash & Equivalents when computing the EBITDA ratio

h.Use ‘Depreciation & Amortization’ for Depreciation

i.Look on the Cash Flow Statement to find this

ii.You may need to sum two (2) different line items

i.Read carefully so that all of your numbers are in the same units (thousands, millions, etc.)

j.If your firm lists ‘restrictive cash’ or something like that, you can decide whether or not to include it in Cash. There is no wrong answer here. Clearly state any and all assumptions you make.

k.To compute the payout ratio for 2020, use the Dividends paid from the Cash Flow Statement and divide by Net Income.

l.When creating projecting financial statements, use sums for line items like total current assets, total assets. Do not compute these line items as a percent of sales. finance代考

m.You MUST use the financial statements directly from the firm. Do not use an outside aggregator, such as Bloomberg, to access the financial statements. Points will be deducted if you do not download the financial statements from Edgar (www.sec.gov/edgar or website listed above).

n.Items that are starred (*) only need to be completed in your spreadsheet. All other items should be discussed or mentioned in your written paper, unless otherwise stated. I should be able to read your paper and understand your work without having to analyze your spreadsheet.

It is expected that each student or team of students work individually. If there is any evidence of more than two students working together, all students will receive a zero on this assignment. You are welcome to reach out to the TAs for guidance. You are also welcome to discuss the assignment with other students in the class. But, as a graded assignment, you must submit your own work. Each student or team of students will submit the required documents.

More guidance will be provided for the individual firms. This guidance will be posted on myCourses as I work through this assignment.