I am trying to figure out the change in cash for this problem. However, I am confused on how to calculate the investment activities portion on the statement of cash flows sheet. Please help!

Ross, Westerfield, Jaffe, and Jordan's Excel Master Corporate Finance: Core Principles and Applications, 4th edition by Brad Jordan and Joe Smolira Version 4.0 Chapter 2 In these spreadsheets, you will learn how to use the following Excel fu Cell formatting Trace Dependents/Precedents VLOOKUP Referencing across worksheets The following conventions are used in these spreadsheets: 1) Given data in blue 2) Calculations in red NOTE: Some functions used in these spreadsheets may require that the "Analysis ToolPak" or "Solver Add-In" be installed in Excel. To install these, click on the Office button then "Excel Options," "Add-Ins" and select "Go." Check "Analysis ToolPak" and "Solver Add-In," then click "OK." he following Excel functions: Chapter 2 - Section 1 The Balance Sheet Spreadsheets are a very useful way to do financial calculations. Throughout these spreadsheets, we will use Exce textbook. However, using Excel or any spreadsheet program is not a substitute for understanding the underlying definitely applies to spreadsheets. You should realize that you do not need to understand these spreadsheets to would recommend that you first read the textbook to understand the concepts before you work through these sp the problems by hand or using a financial calculator. As with most calculations, there are often multiple ways to a method of solving the problem presented, not necessarily the only way to solve the problem with a spreadsheet RWJ Excel Tip Throughout these spreadsheets, Excel tips will be highlighted in blue. The tips will range from specific functions t use is building a basic balance sheet. Although we could build a balance sheet and enter the numbers directly int a column of variables and then refer to these input cells in the balance sheet. We also believe in the use of color spreadsheets, if the number is blue, it is an input cell. If the number is red, the cell is a calculation or references a Given the following information, what does the balance sheet look like? What is shareholders' equity? What is ne Current assets: Net fixed assets: Short-term debt: Long-term debt: $ $ $ $ 100 500 70 200 RWJ Excel Tip In order to make a spreadsheet more presentable, cell formatting is important. To make the numbers in a cell app percentage, or other format, select the cells you want to format, right-click, and select the "Format Cells" option. font, text color, cell background color, and lots of other options. Assets Current assets Fixed assets $ 100 500 Total assets $ 600 Liabilities & Shareholders' Eq Current liabilities Long-term debt Shareholders' equity Total liabilities and shareholders' equity To calculate the shareholders' equity, realize that total liabilities and shareholders' equity equals both total assets equity. Using these relationships, we can solve for shareholders' equity. Net working capital = Current assets - Current liabilities Net working capital = $ 30 Many of the examples in this chapter deal with the fictitious U.S. Composite Corporation. The balance sheet for t RWJ Excel Tip Note that we will enter the data for the balance sheet here, but reference the numbers across different sheets, a balance sheet balances, we will have Excel do the balance sheet calculations for us anyway. For example, total cu is given in the textbook. U.S. Composite Corporation 2013 and 2014 Balance Sheets ($ in millions) Assets Current assets Cash and equivalents Accounts receivable Inventory Total current assets Fixed assets Property, plant and equipment Less accumulated depreciation Net property, plant, and equipment Intangible assets and others Total fixed assets Total assets Notes to balance sheet Par value of stock: New debt issued: Debt retired: Stock repurchased: New shares issued: Share price of new shares: Total par value of new shares: Additional capital surplus of new shares: 2013 2014 157 $ 270 280 707 $ 198 294 269 761 $ 1,274 $ 460 814 $ 1,423 550 873 $ 221 1,035 $ 245 1,118 $ 1,742 $ 1,879 $ $ $ $ $ $ $ $ $ 1 86 73 6 23 1.87 23 $ 20 RWJ Excel Tip In the balance sheet, select the 2014 cash balance, go to Formulas, and select Trace Dependents. This will draw a liabilities. This function draws an arrow to any cell that uses the selected cell in a calculation. If you go to the tota menu, Excel will draw a line from total current assets and net plant and equipment to total assets. Tracing preced for the calculation in that cell. Both functions are useful for tracing where cells are used in future calculations or We will be using this balance sheet in future calculations. Example 2.1: Market Value versus Book Value We are given the following information on the Cooney Corporation: Book value of fixed assets: Market value of fixed assets: Book value of net working capital: Market value of net working capital: Book value of long-term debt: Market value of long-term debt: $ $ $ $ $ $ 700 1,000 400 600 500 500 Below we have both the book value and market value balance sheets for the Cooney Corporation. Cooney Corporation Balance Sheets Book Value and Market Value Assets Book Market Net working capital Net fixed assets $ 400 $ 700 600 1,000 Total assets $ 1,100 $ 1,600 RWJ Excel Tip Notice we used separate input cells for the market value and book value of debt even though the two numbers a have to be the same. Using different input cells makes the spreadsheet more versatile. e spreadsheets, we will use Excel to perform most of the calculations that are presented in the or understanding the underlying calculations. The old adage "Garbage in - Garbage out" derstand these spreadsheets to understand Finance, but it is a useful tool for calculations. We before you work through these spreadsheets. It might also be advantageous to work through here are often multiple ways to arrive at the correct solution. The spreadsheets show one the problem with a spreadsheet. ill range from specific functions to formatting and displaying output. The first application we will nd enter the numbers directly into the balance sheet, in some cases, our preference is to create e also believe in the use of color to differentiate input cells and calculation cells. In these ell is a calculation or references an input cell. shareholders' equity? What is net working capital? To make the numbers in a cell appear the way you would like them to, whether a dollar amount, select the "Format Cells" option. This will allow you to change the format of the numbers, the Liabilities & Shareholders' Equity nt liabilities $ term debt holders' equity liabilities and eholders' equity $ 70 200 330 600 s' equity equals both total assets and current liabilities plus long-term debt plus shareholders' poration. The balance sheet for the company is below. umbers across different sheets, a very handy tool in Excel. Also, even though we know the us anyway. For example, total current assets will be calculated in Excel even though the number site Corporation 14 Balance Sheets millions) Liabilities and Stockholder's Equity 2013 Current liabilities Accounts payable $ 455 $ Total current liabilities $ 455 $ Long-term liabilities Deferred taxes Long-term debt Total long-term liabilities $ $ 486 486 104 $ 458 562 $ 117 471 588 39 55 347 390 26 805 1,879 Stockholders' equity Preferred stock Common stock ($1 par value) Capital surplus Accumulated retained earnings Less treasury stock Total equity $ 39 $ 32 327 347 20 725 $ Total liabilities and owner's equity $ 1,742 $ $ 2014 ace Dependents. This will draw a line from the cash balance cell to the 2014 total current a calculation. If you go to the total assets cell, and choose Trace Precedents from the same ent to total assets. Tracing precedents allows us to see which cells the cell we selected is using re used in future calculations or which cell is used in the current calculation. oney Corporation. Corporation nce Sheets and Market Value Long-term debt Owner's equity Total liabilities and owner's equity Liabilities and Owner's Equity Book $ 500 $ 600 $ 1,100 $ Market 500 1,100 1,600 even though the two numbers are the same. While they are the same in this case, they do not rsatile. Chapter 2 - Section 2 The Income Statement The income statement measures performance over time. The income statement for U.S. Composite Corporation U.S. Composite Corporation 2014 Income Statement ($ in millions) Total operating revenues Cost of goods sold Selling, general, and administrative expenses Depreciation Operating income Other income Earnings before interest and taxes (EBIT) Interest expense Pretax income Taxes Current: $ 71 Deferred: 13 Net income Dividends Addition to retained earnings $ $ $ $ 2,262 1,655 327 90 190 29 219 49 170 84 $ 86 $ 43 43 Suppose that the shares outstanding for U.S. Composite Corporation in millions are: What are the earnings per share and dividends per share? Earnings per share = Net income / Total shares outstanding = Dividends per share = Total dividends / Total shares outstanding = $ $ 2.97 1.48 nt for U.S. Composite Corporation is: 29 Chapter 2 - Section 3 Taxes The corporate income tax table currently in effect is: Taxable income is But greater than less or equal than or equal to.... to... $ - $ 50,000 50,001 75,000 75,001 100,000 100,001 335,000 335,001 10,000,000 10,000,001 15,000,000 15,000,001 18,333,333 18,333,334 Tax rate 15% 25% 34% 39% 34% 35% 38% 35% Notice that we entered the minimum and maximum amount for each marginal tax rate in separate columns. Why the tax on the next dollar of income. Suppose we have gathered the following information from a company's inco Taxable income: Taxes: $ $ 200,000 61,250 The marginal tax rate will be: Marginal tax rate: 39% RWJ Excel Tip To have Excel find the marginal tax rate at the requested level of income, we used VLOOKUP. This function is foun VLOOKUP(lookup_value,table_array,col_index_num,range_lookup). The lookup_value is the number we want to where we want to look for the number, col_index_num is the column in the selected data we want the answer re answer only for an exact match (if FALSE is entered) or an approximate match (the default, or if TRUE is entered.) with the net income amount as the lookup_value, and entered "3" as the col_index_num since we wanted the nu answer. Since we did not enter a value for range_lookup, the default option was to look for the closest number a income tax rate cell calculation cell to see the syntax we used. The average tax rate is simply the total taxes divided by the taxable income, or: Average tax rate: 30.625% Of course, you could create a spreadsheet to calculate the total tax bill and average tax rate for a given level of ta this workbook asks you to create such a spreadsheet. tax rate in separate columns. Why we did this we will explain shortly. The marginal tax rate is nformation from a company's income statement: ed VLOOKUP. This function is found under Lookup & Reference. The format for this function is p_value is the number we want to find, in this case the taxable income. The table_array is ected data we want the answer returned from, and range_lookup allows you to request an the default, or if TRUE is entered.) In this case, we highlighted the entire table, used the cell ndex_num since we wanted the number from the 3rd column we highlighted returned as our s to look for the closest number and return the value from the column. Click on the marginal rage tax rate for a given level of taxable income. In fact, the Master It! problem at the end of Chapter 2 - Section 4 Net Working Capital Net working capital is current assets minus current liabilities. When we calculate the net working capital, we are worksheets so we do not have to enter the balance sheet again. RWJ Excel Tip To reference data in another worksheet, first enter an equal sign in the cell you want the data in, then move the c corresponds to the worksheet you want. Go to the cell that contains the desired data, left click your mouse, and click on the appropriate worksheet tab at the bottom of the spreadsheet. U.S. Composite Corporation Net Working Capital 2013 2014 Current assets $707 761 Current liabilities $455 486 Net working capital $252 275 We can calculate the change in net working capital, which is the firm's investment in net working capital for the y Change in net working capital: $23 te the net working capital, we are going to reference inputs and calculations from the previous want the data in, then move the cursor to the tab at the bottom of the spreadsheet that d data, left click your mouse, and press enter. To get back to the current worksheet, simply ent in net working capital for the year. The change in net working capital is: Chapter 2 - Section 5 Financial Cash Flow Now we are ready to calculate the financial cash flows for the fictitious U.S. Composite Corporation. When we ca calculations from the previous worksheets so we do not have to enter the balance sheet and income statements The cash flow from operations is the cash flow generated by the business activities of the firm. For the U.S. Comp U.S. Composite Corporation 2014 Operating Cash Flow Earnings before interest and taxes + Depreciation - Current taxes Operating cash flow $ $ 210 120 84 246 RWJ Excel Tip Excel will not let you enter a mathematical operator ( = , + , - , etc.) at the beginning of a text cell. However, if you consider anything in the cell as text, not a mathematical operation. This is how we entered the "+" before Deprec Capital spending is the amount a company spends on fixed assets. For the first method of calculating, we need th the sale of fixed assets. Since these are amounts we need to know outside the spreadsheet, we have made these capital spending, we need numbers from the balance sheet. Below you will see both calculations for capital spen U.S. Composite Corporation 2014 Capital Spending Acquisition of fixed assets - Sales of fixed assets Net capital spending $ $ 198 25 173 To calculate the total cash flows of the firm, we also need the change in net working capital, which we previously the firm for U.S. Composite Corporation. The cash flow from the firm is the operating cash flow less the capital sp is: U.S. Composite Corporation 2014 Cash Flow from Assets Operating cash flow - Net capital spending - Change in NWC Cash flow from assets $ $ 246 173 23 50 The cash flow to creditors can be calculated as interest plus retirement of debt minus the proceeds of new debt, borrowing is simply the difference between the ending long-term debt and the beginning long-term debt. Below U.S. Composite Corporation 2014 Cash Flow to Creditors Interest paid + Retirement of debt Debt service - Proceeds from long-term debt sales Cash flow to creditors $ $ $ 49 73 122 86 36 The cash flow to stockholders can also be calculated in two ways. Below, we show the calculations for both meth U.S. Composite Corporation 2014 Cash Flow to Stockholders Dividends paid + Repurchase of stock Cash to stockholders - Proceeds from new stock issue Cash flow to stockholders $ $ $ 43 6 49 43 6 mposite Corporation. When we calculate the cash flows, we are going to reference inputs and nce sheet and income statements again. ties of the firm. For the U.S. Composite Corporation, the operating cash flow is: ning of a text cell. However, if you enter an apostrophe as the first character in a cell, Excel will we entered the "+" before Depreciation. method of calculating, we need the dollar amounts for the purchases of new fixed assets and spreadsheet, we have made these cells inputs. For the second method of calculating the both calculations for capital spending. U.S. Composite Corporation 2014 Capital Spending Ending fixed assets - Beginning fixed assets + Depreciation Net capital spending $ $ 1,118 1,035 90 173 rking capital, which we previously calculated. Now we are ready to calculate the cash flow of erating cash flow less the capital spending less the change in NWC. The cash flow from the firm minus the proceeds of new debt, or as the interest paid minus net new borrowing. Net new beginning long-term debt. Below, we calculate the cash flow to creditors using both methods. U.S. Composite Corporation 2014 Cash Flow to Creditors Interest paid - Net new borrowing Cash flow to creditors $ $ 49 13 36 ow the calculations for both methods. U.S. Composite Corporation 2014 Cash Flow to Stockholders Dividends paid - Net new equity Cash flow to stockholders $ $ 43 37 6 Chapter 2 - Section 6 The Accounting Statement of Cash Flows The accounting statement of cash flows has three sections. U.S. Composite Corporation 2014 Statement of Cash Flows Operations Net income Depreciation Deferred taxes Changes in assets and liabilities Accounts receivable Inventories Accounts payable Total cash flow from operations Investing activities Investment/Acquisiton of Fixed Assets Total cash flow from investing activities Financing activities Long Term Borrowings Dividends Paid Total cash flow from financing activities Change in cash (on balance sheet) $ 126 120 84 $ 11 31 372 $ - $ 80 65 145 517 $ Chapter 2 - Master it! Using Excel to find the marginal tax rate can be accomplished using the VLOOKUP function. However, calculating the total tax bill is a little more diffic Below we have shown a copy of the IRS tax table for an individual. Often, tax tables are presented in this format. If taxable income is over -$0 8,350 33,950 82,250 171,550 372,680 But not over -$8,350 33,950 82,250 171,550 372,950 The tax is: 10% of the amount over $0 $835 plus 15% of the amount over $8,350 $4,675 plus 25% of the amount over $33,950 $16,750 plus 28% of the amount over $82,250 $41,754 plus 33% of the amount over $171,550 $108,216 plus 35% of the amount over $372,950 In reading this table, the marginal tax rate for taxable income less than $7,825 is 10%. If the taxable income is between $7,825 and $31,850, the tax $782.50 plus the marginal taxes. The marginal taxes are calculated as the taxable income minus $7,825 times the marginal tax rate of 15%. Below, we have the tax table as shown in the textbook, as well as a taxable income. Taxable income: $ 1,350,000 The corporate income tax table currently in effect is: Taxable income is greater than But or equal less to.... than... $ - $ 50,000 50,001 75,000 75,001 100,000 100,001 335,000 335,001 10,000,000 10,000,001 15,000,000 15,000,001 18,333,333 18,333,334 Tax rate 15% 25% 34% 39% 34% 35% 38% 35% a. Create a tax table for corporate taxes similar to the individual tax table shown above. b. For a given taxable income, what is the marginal tax rate? c. For a given taxable income, what is the total tax bill? d. For a given taxable income, what is the average tax rate? e total tax bill is a little more difficult. een $7,825 and $31,850, the tax bill is marginal tax rate of 15%. Master it! Solution a. If taxable income is over -- But not over -- The tax is: plus plus plus plus plus plus plus b. The marginal tax rate is: c. The total tax bill is: Tax on the bottom of marginal tax bracket: Amount of income in marginal tax bracket: Total tax: d. The average tax rate is: of the amount over of the amount over of the amount over of the amount over of the amount over of the amount over of the amount over of the amount over