Loading...

Assignment: Sunset Boards, Inc. Work in the provided excel spreadsheet

Open Posted By: highheaven1 Date: 05/05/2021 Graduate Rewriting & Paraphrasing
  1. Review the attached powerpoint for knowledge
  2. Download the Excel file attached
  3. In an Excel document, prepare the following financial statements for the mini-case “Cash Flows and Financial Statements at Sunset Boards, Inc.” from the Assignment 1.6 Worksheet file:
    1. Income Statement for 2018 and 2019
    2. Balance Sheet for 2018 and 2019
  4. In the provided Excel document, also calculate:
    1. the Operating Cash Flow for each year
    2. the Cash Flow from Assets for 2019
    3. the Cash Flow to Creditors for 2019
    4. the Cash Flow to Stockholders for 2019
  5. Tad Marks, the company’s founder, is considering expanding the business, which will require a substantial investment.
    1. Evaluate the financial statements you have developed and Sunset Board’s cash flow.
    2. In your Excel document or a separate Word document, write a paragraph explaining whether you feel expansion would be a wise choice and why.
Category: Business & Management Subjects: Auditing Deadline: 12 Hours Budget: $120 - $180 Pages: 2-3 Pages (Short Assignment)

Attachment 1

Sunset Boards Case

Chapter 2 - Dropbox 1.6
Mini-Case Study: Cash Flows and Financial Statements at Sunset Boards
Sunset Boards is a small company that manufactures and sells surfboards in Malibu. Tad Marks, the founder of the company, is in charge of the design and sale of the surfboards, but his background is in surfing, not business. As a result, the company's financial records are not well maintained.
2018 2019
Cost of goods sold $ 196,619 $ 248,263
Cash 28,372 42,865
The initial investment in Sunset Boards was provided by Tad and his friends and family. Because the initial investment was relatively small, and the company made surfboards only for its own store, the investors haven't required detailed financial statements from Tad. But thanks to word of mouth among professional surfers, sales have picked up recently, and Tad is considering a major expansion. His plans include opening another surfboard store in Hawaii, as well as supplying his "sticks" (surfer lingo for boards) to other sellers. Depreciation 55,506 62,738
Interest expense 12,067 13,831
Selling & Administrative Expenses 38,668 50,469
Accounts payable 20,143 34,091
Net fixed assets 244,881 298,350
Sales 385,724 470,172
Tad's expansion plans require a significant investment, which he plans to finance with a combination of additional funds from outsiders plus some money borrowed from banks. Naturally, the new investors and creditors require more organized and detailed financial statements than Tad has previously prepared. At the urging of his investors, Tad has hired financial analyst Christina Wolfe to evaluate the performance of the company over the past year. Accounts receivable 20,104 26,078
Notes payable 22,855 24,955
Long-term debt 123,607 140,000
Inventory 38,706 52,057
New equity - 15,000
After rooting through old bank statements, sales receipts, tax returns, and other records, Christina has assembled the financial information shown in the table to the right. ======> Tax rate (average on all Taxable Income) 30%
Dividend Payout Ratio (of Net Income) 40%
As Christina's assistant, you are asked to prepare financial calculations and then use them to evaluate the wisdom of Tad's expansion plans. The financial statements and calculations you must prepare include:
1) Prepare an Income Statement for both 2018 and 2019
2) Prepare a Balance Sheet for both 2018 and 2019
3) Calculate Operating Cash Flow (OCF) both 2018 and 2019
4) Calculate Cash Flow from Assets (CFFA) for 2019
5) Calculate Cash Flow to Creditors (CFC) for 2019
6) Calculate Cash Flow to Stockholders (CFS) for 2019
Finally, using the above calculations as a foundation for your analysis, comment on the wisdom of Tad's expansion plans. Should the company expand at this time? Why or why not?
Create your Original Solution Below - Be sure to show all calculations, to carefully complete all parts of the assignment, and to clearly indicate answers (create additional worksheets to organize your work if necessary).
This is the Student Template, provided in the course materials by D. Kendall April 2019

Attachment 2

Financial Statements, Taxes, and Cash Flow

Chapter 2

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.1

Describe the difference between accounting value (or “book” value) and market value

Describe the difference between accounting income and cash flow

Describe the difference between average and marginal tax rates

Determine a firm’s cash flow from its financial statements

Key Concepts and Skills

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

The Balance Sheet

The Income Statement

Taxes

Cash Flow

Chapter Outline

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

The balance sheet is a snapshot of the firm’s assets and liabilities at a given point in time.

Assets are listed in order of decreasing liquidity.

Ease of conversion to cash

Without significant loss of value

Balance Sheet Identity

Assets = Liabilities + Stockholders’ Equity

Balance Sheet

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.4

Section 2.1

Liquidity is a very important concept. Students tend to remember the “convert to cash quickly” component of liquidity, but often forget the part about “without loss of value.” Remind them that we can convert anything to cash quickly if we are willing to lower the price enough, but that doesn’t mean it is liquid.

Also, point out that a firm can be TOO liquid. Excess cash holdings lead to overall lower returns.

The Balance Sheet Figure 2.1

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.5

Section 2.1

The left-hand side lists the assets of the firm. Current assets are listed first because they are the most liquid. Fixed assets can include both tangible and intangible assets, and they are listed at the bottom because they generally are not very liquid. These are a direct result of management’s investment decisions. (Please emphasize that “investment decisions” are not limited to investments in financial assets.)

Note that the balance sheet does not list some very valuable assets, such as the people who work for the firm. The liabilities and equity (or ownership) components of the firm are listed on the right-hand side. This indicates how the assets are paid for. Since the balance sheet has to balance, total equity = total assets - total liabilities. The portion of equity that can most easily fluctuate to create this balance is retained earnings. The right-hand side of the balance sheet is a direct result of management’s financing decisions.

Remember that shareholders’ equity consists of several components and that total equity includes all of these components, not just the “common stock” item. In particular, remind students that retained earnings belong to the shareholders.

Net Working Capital

= Current Assets - Current Liabilities

Positive when the cash that will be received over the next 12 months exceeds the cash that will be paid out

Usually positive in a healthy firm

Liquidity

Ability to convert to cash quickly without a significant loss in value

Liquid firms are less likely to experience financial distress.

But liquid assets typically earn a lower return.

Trade-off to find balance between liquid and illiquid assets

Net Working Capital and Liquidity

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.6

Section 2.1 (C)

After a basic accounting class, students may believe a higher current ratio (or, similarly, more cash on hand) is always better. So, it is good to remind students that a cash balance is a use of funds and has an opportunity cost.

U.S. Corporation Balance Sheet Table 2.1

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

To I/S

Back to Example

2-‹#›

2.7

Section 2.1 (E)

The first example computing cash flows has a link to the information in this table. The arrow in the corner is used to return you to the example.

Here is an example of a simplified balance sheet. Many students make it through business school without ever seeing an actual balance sheet, particularly those who are not majoring in finance or accounting. I encourage you to bring in some annual reports and let the students see the differences between the simplified statements they see in textbooks and the real thing.

This is a good place to talk about some of the specific types of items that show up on a balance sheet and remind the students what accounts receivable, accounts payable, notes payable, etc. are.

The embedded links are used to navigate through a later example.

The balance sheet provides the book value of the assets, liabilities, and equity.

Market value is the price at which the assets, liabilities, or equity can actually be bought or sold.

Market value and book value are often very different. Why?

Which is more important to the decision-making process?

Market Value vs. Book Value

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.8

Section 2.1 (F)

Current assets and liabilities generally have book values and market values that are very close. This is not necessarily the case with the other assets, liabilities, and equity of the firm.

Assets are listed at historical costs less accumulated depreciation – this may bear little resemblance to what they could actually be sold for today. The balance sheet also does not include the value of many important assets, such as human capital. Consequently, the “Total Assets” line on the balance sheet is generally not a very good estimate of what the assets of the firm are actually worth.

Liabilities are listed at face value. When interest rates change or the risk of the firm changes, the value of those liabilities change in the market as well. This is especially true for longer-term liabilities.

Equity is the ownership interest in the firm. The market value of equity (stock price times number of shares) depends on the future growth prospects of the firm and on the market’s estimation of the current value of ALL of the assets of the firm.

The best estimate of the market value of the firm’s assets is market value of liabilities + market value of equity.

Market values are generally more important for the decision making process because they are more reflective of the cash flows that would occur today.

Example 2.2 Klingon Corporation

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.9

Section 2.1 (F)

Shareholders are the ones that benefit from increases in the market value of a firm’s assets. They are also the ones that bear the losses of a decrease in market value. Consequently, managers need to consider the impact of their decisions on the market value of assets, not on their book value. Here is a good illustration:

Suppose that the MV of assets declined to $700 and the market value of long-term debt remained unchanged. What would happen to the market value of equity? It would decrease to 700 - 500 = 200.

The market-to-book ratio, which compares the market value of equity to the book value of equity, is often used by analysts as a measure of valuation for a stock. It is generally a bad sign if a company’s market-to-book ratio approaches 1.00 (meaning market value = book value) because of the GAAP employed in creating a balance sheet. It is definitely a bad sign if the ratio is less than 1.00.

GAAP does provide for some assets to be marked-to-market, primarily those assets for which current market values are readily available due to trading in liquid markets. However, it does not generally apply to long-term assets, where market values and book values are likely to differ the most.

The income statement is more like a video of the firm’s operations for a specified period of time.

You generally report revenues first and then deduct any expenses for the period.

Matching principle – GAAP says to show revenue when it accrues and match the expenses required to generate the revenue

Income Statement

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.10

Section 2.2

Matching principle – this principle leads to non-cash deductions like depreciation. This is why net income is NOT a measure of the cash flow during the period.

Consider discussing that the top half of the income statement addresses investment decisions, whereas the bottom half deals with financing.

U.S. Corporation Income Statement – Table 2.2

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

To B/S

Back to Example

2-‹#›

2.11

Section 2.2

The first example computing cash flows has a link to the information in this table. The arrow in the corner is used to return you to the example.

Remember that these are simplified income statements for illustrative purposes.

Earnings before interest and taxes is often called operating income.

COGS would include both the fixed costs and the variable costs needed to generate the revenues.

Analysts often look at EBITDA (earnings before interest, taxes, depreciation, and amortization) as a measure of the operating cash flow of the firm. It is not true in the strictest sense because taxes are an operating cash flow as well, but it does provide a reasonable estimate for analysis purposes.

It is important to point out that depreciation expense is often figured two different ways, depending on the purpose of the financial statements. If we are computing the taxes that we will owe, we use the depreciation schedule provided by the IRS. In this instance, the “life” of the asset for depreciation purposes may be very different from the useful life of the asset. Statements that are prepared for investors often use straight-line depreciation because it will tend to have a lower depreciation charge than MACRS early in the asset’s life. This reduces the “expense,” and thus increases the firm’s reported EPS. This is a good illustration of why it is important to look at a firm’s cash flow and not just its EPS.

The embedded links are used to navigate through a later example.

Publicly traded companies must file regular reports with the Securities and Exchange Commission.

These reports are usually filed electronically and can be searched at the SEC public site called EDGAR.

Visit EDGAR to search for company filings.

Work the Web Example

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

Section 2.2

2.12

The one thing we can rely on with taxes is that they are always changing.

In fact, the Tax Cuts and Jobs Act of 2017 will drop the corporate tax rate to a flat 21 percent beginning in 2018.

Marginal vs. average tax rates

Marginal tax rate – the percentage paid on the next dollar earned

Average tax rate – the tax bill / taxable income

Average tax rates vary widely across different companies and industries

Check out the IRS website for up-to-date information.

Taxes

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.13

Section 2.3

Point out that taxes can be a very important component of the decision making process, but what students learn about tax specifics now could change tomorrow. Consequently, it is important to keep up with the changing tax laws and to utilize specialists in the tax area when making decisions where taxes are involved.

Click on the embedded link to go to the IRS web site for the most up-to-date tax information.

It is important to point out that we are concerned with the taxes that we will pay if a decision is made. Consequently, the marginal tax rate is what we should use in our analysis.

Students can view the average tax rates for various industries in Table 2.5.

Point out that the tax rates discussed in the book are just federal taxes. Many states and cities have income taxes as well, and those taxes should figure into any analysis that we conduct.

The new Tax Cuts and Jobs Act reduces the U.S. corporate tax rate from among the highest in the developed world, to a “middle of the road” rate.

Suppose your firm earns $4 million in taxable income.

What is the firm’s tax liability?

What is the average tax rate?

What is the marginal tax rate?

If you are considering a project that will increase the firm’s taxable income by $1 million, what tax rate should you use in your analysis?

Example: Marginal vs. Average Rates

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.14

Section 2.3 (B)

Tax liability:

Using 2017 rates:

.15(50,000) + .25(75,000 - 50,000) + .34(100,000 - 75,000) + .39(335,000 - 100,000) + .34(4,000,000 - 335,000) = $1,360,000

Average rate: 1,360,000 / 4,000,000 = .34 or 34% Marginal rate comes from the table and it is 34% also, but they are not always the same.

Using 2018 rates:

.21*4,000,000 = $840,000 (notice the large drop vs. 2017!)

Average = marginal = flat = .21 or 21%

In either case, the marginal rate is appropriate for analysis of a proposed project.

Cash flow is one of the most important pieces of information that a financial manager can derive from financial statements.

The statement of cash flows does not provide us with the same information that we are looking at here.

We will look at how cash is generated from utilizing assets and how it is paid to those that finance the purchase of the assets.

The Concept of Cash Flow

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

Section 2.4

2.15

Cash Flow From Assets (CFFA) =

Cash Flow to Creditors

+ Cash Flow to Stockholders

Cash Flow From Assets = Operating Cash Flow - Net Capital Spending - Changes in NWC

Cash Flow From Assets

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.16

Section 2.4 (A)

The first equation is how the cash flow from the firm is divided among the investors who financed the assets.

The second equation is the cash flow that the firm receives from its assets. This is an important equation to remember. We will come back to it and use it again when we do our capital budgeting analysis. We want to base our decisions on the timing and risk of the cash flows we expect to receive from a project.

OCF (I/S) = EBIT + depreciation - taxes = $628

NCS (B/S and I/S) = ending net fixed assets - beginning net fixed assets + depreciation = $130

Changes in NWC (B/S) = ending NWC - beginning NWC = $391

CFFA = 628 - 130 - 391 = $107

Example: U.S. Corporation – Part I

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.17

Section 2.4 (A)

Use the information from the balance sheet and income statement presented previously to work through this example. There is a hyperlink on “I/S” that will take you to that slide. Another one exists on “B/S.” There are links on each statement to bring you back here.

OCF = 694 + 65 - 131 = 628

NCS = 1709 - 1644 + 65 = 130

Students often have a difficult time understanding why a cash outflow has a positive sign and a cash inflow has a negative sign. Emphasize that we are talking about spending in the net capital spending formula and investment in NWC. The formula for CFFA takes care of reducing cash flow when NCS is positive and increasing CF when it is negative.

Ending NWC = 1464 - 389 = 1075

Beginning NWC = 1112 - 428 = 684

Changes in NWC = 1075 - 684 = 391

CF to Creditors (B/S and I/S) = interest paid - net new borrowing = $24

CF to Stockholders (B/S and I/S) = dividends paid - net new equity raised = $83

CFFA = 24 + 83 = $107

Example: U.S. Corporation – Part II

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.18

Section 2.4 (A)

Use the information from the balance sheet and income statement presented previously to work through this example. There is a hyperlink on “I/S” that will take you to that slide. Another one exists on “B/S.” There are links on each statement to bring you back here.

Net New Borrowing = ending LT debt - beginning LT debt = 454 - 408 = 46

CF to creditors = 70 - 46 = 24

Net New Equity = 640 - 600 = 40 (Be sure to point out that we want equity raised in the capital markets, not retained earnings).

CF to Stockholders = 123 - 40 = 83

Cash Flow Summary - Table 2.6

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.19

Section 2.4 (C)

This provides a summary for the various cash flow calculations. It is a good place to refer back when working on cash flows in the capital budgeting section.

Current Accounts

2018: CA = 3,625; CL = 1,787

2017: CA = 3,596; CL = 2,140

Fixed Assets and Depreciation

2018: NFA = 2,194; 2014: NFA = 2,261

Depreciation Expense = 500

Long-term Debt and Equity

2018: LTD = 538; Common stock & APIC = 462

2017: LTD = 581; Common stock & APIC = 372

Income Statement

EBIT = 1,014; Taxes = 193

Interest Expense = 93; Dividends = 460

Example: Balance Sheet and Income Statement Info

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.20

Section 2.5

OCF = 1,014 + 500 - 193 = 1,321

NCS = 2,194 - 2,261 + 500 = 433

Changes in NWC = (3,625 - 1,787) - (3,596 - 2,140) = 382

CFFA = 1,321 - 433 - 382 = 506

CF to Creditors = 93 - (538 - 581) = 136

CF to Stockholders = 460 - (462 - 372) = 370

CFFA = 136 + 370 = 506

The CF identity holds.

Example: Cash Flows

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.21

Section 2.5

What is the difference between book value and market value? Which should we use for decision-making purposes?

What is the difference between accounting income and cash flow? Which do we need to use when making decisions?

What is the difference between average and marginal tax rates? Which should we use when making financial decisions?

How do we determine a firm’s cash flows? What are the equations, and where do we find the information?

Quick Quiz

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

Section 2.5

2.22

Why is manipulation of financial statements not only unethical and illegal, but also bad for stockholders?

Ethics Issues

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.23

Current Accounts

2018: CA = 4,400; CL = 1,500

2017: CA = 3,500; CL = 1,200

Fixed Assets and Depreciation

2018: NFA = 3,400; 2014: NFA = 3,100

Depreciation Expense = 400

Long-term Debt and Equity (R.E. not given)

2018: LTD = 4,000; Common stock & APIC = 400

2017: LTD = 3,950; Common stock & APIC = 400

Income Statement

EBIT = 2,000; Taxes = 300

Interest Expense = 350; Dividends = 500

Compute the CFFA

Comprehensive Problem

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2.24

OCF = $2,000 + $400 - $300 = $2,100

NCS = $ 3,400 - $3,100 + $400 = $700

Changes in NWC = ($4,400 - $1,500) - ($3,500 - $1,200) = $600

CFFA = $2,100 - $700 - $600 = $800

CF to Creditors = $350 - ($4,000 - $3,950) = $300

CF to Stockholders = $500

CFFA = $300 + $500 = $800

END OF CHAPTER

CHAPTER 2

Copyright © 2019 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.

2-‹#›

2-‹#›

2.25