Please useExcel

Case I is due at the end of this week. Prepare a memo in Word, which answers the questions in the Chapter 2 Case, Cash Flows and Financial Statements at Sunset Boards, Inc., on page 51 of the textbook. Use Excel to solve any financial calculations. You will be graded on correct financial analysis, proper use of technology, business-like presentation of technology, and business-like presentation.

Case I is due at the end of this week. Prepare a memo in Word, which answers the questions in the Chapter 2 Case, Cash Flows and Financial Statements at Sunset Boards, Inc., on page 51 of the textbook. Use Excel to solve any financial calculations. You will be graded on correct financial analysis, proper use of technology, business-like presentation of technology, and business-like presentation. CASH FLOWS AND FINANCIAL STATEMENTS AT SUNSET BOARDS, INC. 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. 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 has 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. 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 Paula Wolfe to evaluate the performance of the company over the past year. After rooting through old bank statements, sales receipts, tax returns, and other records, Paula has assembled the following information: 20132014Cost of goods sold$169,969 214,607Cash 24,524 26,056Depreciation 47,980 54,230Interest expense 10,442 11,954Selling & administrative expenses 33,425 43,626Accounts payable 43,344 48,090Net fixed assets 211,680 264,021Sales 333,426 406,427Accounts receivable 17,378 22,542Notes payable 19,757 21,571Long-term debt 106,848 119,976Inventory 36,570 50,185New equity 0 20,160 Sunset Boards currently pays out 50 percent of net income as dividends to Tad and the other original investors, and has a 20 percent tax rate. You are Paula's assistant, and she has asked you to prepare the following: 1. An income statement for 2013 and 2014. 2. A balance sheet for 2013 and 2014. 3. Operating cash flow for each year. 4. Cash flow from assets for 2014. 5. Cash flow to creditors for 2014. 6. Cash flow to stockholders for 2014. Income Statement 2013 Sales 333426 Cost of goods sold 169969 Gross Profit 163457 Operating expenses: Sellinga dn administrative expen 33425 Depreciation 47980 Income from operations 82052 Interest expense 10442 Income before taxes 71610 Tax @ 20% 14322 Net Income 57288 Dividends 28644 Addition to retained earnings 28644 2014 406427 214607 191820 43626 54230 93964 11954 82010 16402 65608 32804 32804 10/10pts Balance sheet Assets Cash Accounts receivables Inventory Total current assets 24524 17378 36570 78472 26056 22542 50185 98783 211680 290152 264021 362804 Liabilities and Shareholder's equity Current liabilities Accounts payable 43344 Notes payable 19757 Total current liabilities 63101 48090 21571 69661 Net fixed Assets Total Assets Long term debt Total liabilities 106848 169949 119976 189637 10/10pts Equity Retained earnings Total liabilities and equity 0 120203 290152 20160 153007 362804 Operating cash flow = EBIT + Depreciation - taxes 2013 2014 EBIT Add: Depreciation Less: Taxes OCF Change 82052 47980 14322 115710 93964 54230 16402 131792 10/10pts 13.90% The change was 13.90%, because of increase in sales Net capital spending = NFA end Less: NFA beg Add: Depreciation NCS NFAend - NFAbeg + depreciation 264021 211680 54230 106571 NWC = CA end CL end CA beg CL beg NWC = (Caend - Clend) - (Cabeg - Clbeg) 98783 69661 78472 63101 13751 Free cash flow = OCF NCF NWC FCF = OCF - NCS - NWC 131792 106571 13751 11470 Cash flow to creditors = Interest paid LTDend LTDbeg Cash flow to creditors = Interest paid - (LTDend - LTDbeg) 11954 119976 106848 10/10pts -1174 Cash flow to shareholders = Dividends paid Net new equity Cash flow to shareholders = Dividends paid - Net new equity 32804 20160 10/10pts 12644 10/10pts B. Answers to questions 1. How would you describe Sunset Boards' cash flows for 2 The firm had positive earnings in an accounting sense (NI > The firm had positive cash flow from operations. The firm invested $13,751 in new net working capital The firm invested $106,571 in new fixed assets. The firm gave $11,470 to its stakeholders. The firm raised $1,174 from bondholders, The firm paid $12,644 to stockholders. 2. What do you think about Tad's expansion plans? The expansion plans may be a little risky. The company does have a positive cash flow, but a large po The company has had to raise capital from creditors and st On the other hand, companies do need capital to grow. Be 60/90! set Boards' cash flows for 2014? n an accounting sense (NI > 0). from operations. w net working capital ew fixed assets. 's expansion plans? ve cash flow, but a large portion of the operating cash flow is already going to capital spending. apital from creditors and stockholders for its current operations. So, the expansion plans may be too aggressive at this time. do need capital to grow. Before investing or loaning the company money, you would want to know where the current capital spending is go ve at this time. current capital spending is going, and why the company is spending so much in this area already. Income Statement 2013 Sales 333426 Cost of goods sold 169969 Gross Profit 163457 Operating expenses: Sellinga dn administrative expen 33425 Depreciation 47980 Income from operations 82052 Interest expense 10442 Income before taxes 71610 Tax @ 20% 14322 Net Income 57288 Dividends 28644 Addition to retained earnings 28644 2014 406427 214607 191820 43626 54230 93964 11954 82010 16402 65608 32804 32804 10/10pts Balance sheet Assets Cash Accounts receivables Inventory Total current assets 24524 17378 36570 78472 26056 22542 50185 98783 211680 290152 264021 362804 Liabilities and Shareholder's equity Current liabilities Accounts payable 43344 Notes payable 19757 Total current liabilities 63101 48090 21571 69661 Net fixed Assets Total Assets Long term debt Total liabilities 106848 169949 119976 189637 10/10pts Equity Retained earnings Total liabilities and equity 0 120203 290152 20160 153007 362804 Operating cash flow = EBIT + Depreciation - taxes 2013 2014 EBIT Add: Depreciation Less: Taxes OCF Change 82052 47980 14322 115710 93964 54230 16402 131792 10/10pts 13.90% The change was 13.90%, because of increase in sales Net capital spending = NFA end Less: NFA beg Add: Depreciation NCS NFAend - NFAbeg + depreciation 264021 211680 54230 106571 NWC = CA end CL end CA beg CL beg NWC = (Caend - Clend) - (Cabeg - Clbeg) 98783 69661 78472 63101 13751 Free cash flow = OCF NCF NWC FCF = OCF - NCS - NWC 131792 106571 13751 11470 Cash flow to creditors = Interest paid LTDend LTDbeg Cash flow to creditors = Interest paid - (LTDend - LTDbeg) 11954 119976 106848 10/10pts -1174 Cash flow to shareholders = Dividends paid Net new equity Cash flow to shareholders = Dividends paid - Net new equity 32804 20160 10/10pts 12644 10/10pts B. Answers to questions 1. How would you describe Sunset Boards' cash flows for 2 The firm had positive earnings in an accounting sense (NI > The firm had positive cash flow from operations. The firm invested $13,751 in new net working capital The firm invested $106,571 in new fixed assets. The firm gave $11,470 to its stakeholders. The firm raised $1,174 from bondholders, The firm paid $12,644 to stockholders. 2. What do you think about Tad's expansion plans? The expansion plans may be a little risky. The company does have a positive cash flow, but a large po The company has had to raise capital from creditors and st On the other hand, companies do need capital to grow. Be 60/90! set Boards' cash flows for 2014? n an accounting sense (NI > 0). from operations. w net working capital ew fixed assets. 's expansion plans? ve cash flow, but a large portion of the operating cash flow is already going to capital spending. apital from creditors and stockholders for its current operations. So, the expansion plans may be too aggressive at this time. do need capital to grow. Before investing or loaning the company money, you would want to know where the current capital spending is go ve at this time. current capital spending is going, and why the company is spending so much in this area already