'1

A C D E F G H Learning Objectives 1. Understand how to use EXCEL Spreadsheet (a) Develop proforma Income Statement Using Excel Spreadsheet (b) Compute Net Project Cashflows, NPV, and IRR (c) Develop problem-solving and critical thinking skills and make long-term investment decisions 1) Life Period of the Equipment = 4 years 8) Sales for first your (1) 200,000 [2) New equipment cost (200,000) 9) Sales increase per year 5%% 3) Equipment ship & Install cost (35,000) 10) Operating cost (60%% of Sales) (120,000) 4) Related start up cost (5,000) (as a percent of sales in Year 1) -60% 15 5) Inventory Increase 25,000 11) Depreciation (Straight Line )/YR (60,000) 8 6) Accounts Payable increase 5,000 12) Marginal Corporate Tax Rate (T) 21% 17 7) Equip. salvage value before tax 15,000 13) Cost of Capital (Discount Rate) 10% 19 20 21 ESTIMATING Initial Outlay (Cash Flow, CFO, T= 0) 22 CFO CF1 CF2 CF3 CF4 24 Year 1 2 3 25 Investments: 26 1) Equipment cost 27 2) Shipping and Install cost 28 3) Start up expenses 29 Total Basis Cost (1+2+3) 30 4) Net Working Capital 31 Total Initial Outlay 33 Operations: 34 Revenue 35 Operating Cost 38 Depreciation EBIT 30 Taxes 30 Net Income Sheell Sheet? Shouts ReadyX Cut Aria 10 Ep Wrap Tart Ga 4 Copy . Merge & Center . Paste B Format 022 x v f A C D E G H Net Income 40 41 Add back Depreciation 43 Total Operating Cash Flow X000XX 3000XX XO0XXX #4 45 Terminal: 45 1) Change in net WC 5 $ S 20,000 47 2) Salvage value (after tax) Salvage Value Before Tax (1-T) XXXXX 48 Total XXXX 49 Project Net Cash Flows $ $ NPV = IRR - Payback= 1 0#1 Would you accept the project based on NPV, IRR? Would you accept the project based on Payback rule if project cut-off 158 is 3 years? Impact of 2017 Tax Cut Act on Net Income, Cash Flows and Capital Budgeting (Investment ] Decisions 59 (a) Estimate NPV, IRR and Payback Period of the project if equipment is fully depreciated in first year and tax rate equals to 21%. Would you accept or reject the project? As a CFO of the firm, which of the above two scenario (a) or (b) would you choose? Why? 84 Q#3 How would you explain to your CEO what NPV means? en Q#4 What are advantages and disadvantages of using only Payback method? Q#5 What are advantages and disadvantages of using NPY versus IRR? RT ON6 Explain the difference between independent projects and mutually exclusive projects. When you are confronted with Mutually Exclusive Projects and have coflicts with NPV and IRR results, which criterion would you use (NPV or IRR) and why? 70 71 Sheell Shoots Sheell + ReadyLearning Objectives 4 5 1. Understand how to use EXCEL Spreadsheet 6 (a) Develop proforma Income Statement Using Excel Spreadsheet 7 (b) Compute Net Project Cashflows, NPV, and IRR (c) Develop problem-solving and critical thinking skills and make long-term investment decisions 10 11 1) Life Period of the Equipment = 4 years 8) Sales for first year (1) 200,000 12 2) New equipment cost $ (200,000) 9) Sales Increase per year 5% 13 3) Equipment ship & Install cost (35,000) 10) Operating cost (60% of Sales) (120,000) 14 4) Related start up cost (5,000) (as a percent of sales In Year 1) -60% 15 5) Inventory Increase 25,000 11) Depreciation (Straight Line )/YR (60,000) 16 6) Accounts Payable Increase 5,000 12) Marginal Corporate Tax Rate (T) 21% 17 7) Equip. salvage value before tax 15,000 13) Cost of Capital (Discount Rate) 10% 18 19 20 21 ESTIMATING Initial Outlay (Cash Flow, CFO, T= 0) 22 23 CFO CF1 CF2 CF3 CF4 24 Year 0 1 2 3 4 25 Investments: 26 1) Equipment cost 27 2) Shipping and Install cost 28 3) Start up expenses 20 Total Basis Cost (1+2+3) 30 4) Net Working Capital 31 Total Initial OutlayA B C D E F G H K 33 Operations: 34 Revenue 35 Operating Cost 36 Depreciation 37 EBIT 38 Taxes 39 Net Income 40 41 Add back Depreciation 42 43 Total Operating Cash Flow XXXXX XXXXX XXXXX 44 45 Terminal: 46 1) Change in net WC S S S 20,000 47 2) Salvage value (after tax) Salvage Value Before Tax (1-T) XXXXX 48 Total XXXXX 49 50 Project Net Cash Flows $ . $ $ $ $ 51 52 NPV = IRR = Payback= 53 54 0#1 Would you accept the project based on NPV, IRR? 55 Would you accept the project based on Payback rule if project cut-off 56 is 3 years? 57 Q#2 Impact of 2017 Tax Cut Act on Net Income, Cash Flows and 58 Capital Budgeting (Investment ) Decisions 59 (a) Estimate NPV, IRR and Payback Period of the project if equipment is fully 60 depreciated in first year and tax rate equals to 21%. Would you 61 accept or reject the project? 62 As a CFO of the firm, which of the above two scenario (a) or (b) would you choose? Why?1 accept or reject the project? 2 ( b) As a CFO of the firm, which of the above two scenario (a) or (b) 3 would you choose? Why? 4 Q#3 How would you explain to your CEO what NPV means? 5 Q#4 What are advantages and disadvantages of using only Payback method? 6 Q#5 What are advantages and disadvantages of using NPV versus IRR? 7 Q#6 Explain the difference between independent projects and mutually exclusive projects B When you are confronted with Mutually Exclusive Projects and have coflicts with NPV and IRR results, which criterion would you use (NPV or IRR) and why?FIN3310: INTERMEDIATE FINANCIAL ANALYSIS SPREADSHEET ASSIGNMENT: Chapter 4 1. Retrieve the file HWChapter4 and solve Problem 4-11, Problem 419, using the organizational design displayed in the worksheet file. Do not modify the fonts, format, or cell addresses. 2. Enter or compute numerical values in cells C97 and D97 and enter equations that solve for the values required in cells C101:C105, D101:D105. 3. Enter equations that solve for the values required in cells D113, D114, F121, F124, F125. 87 Problem 4-17 88 89 Bond C = coupon bond 90 Bond Z = zero coupon bond 91 92 Bond C Bond Z 93 N = 4 4 94 M = $1,000 $1,000 95 YTM = 9.6% 9.6% 96 Coupon rate = 10% 0 97 INT = 98 99 Using Excel's =PV Financial Function Command 100 Year Price of C Price of Z 101 0 102 103 2 104 3 105 4 106 107 108 Problem 4-19 109 DRP = 0 and LP = 0 for Treasury securities. 110 111 r* = 3% 112 I = 8% 113 1, = 5% IP 2 = 114 I3 to Is = 4% IP $ = 115 10% 116 rs = 10% 117 MRP2 = ? 118 MRP ; = 119 120 12 = r* + IP2 + MRP2 + DRP + LP 121 MRP, = 122 123 I's = r* + IPs + MRPs + DRP + LP 124 MRP $ = 125 Thus, MRPS - MRP2 =Problem 9-8 Additional Funds Needed Stevens Textile's 2012 financial statements are shown below: Balance Sheet as of December 31, 2012 (Thousands of Dollars) Cash $ 1,080 Accounts payable $ 4,320 Receivables 6,480 Accruals 2,880 Inventories 9,000 Notes payable 2,100 Total current assets $16,560 Total current liabilities $ 9,300 Net fixed assets 12,600 Mortgage bonds 3,500 Common stock 3,500 Retained earnings 12,860 Total assets $29,160 Total liabilities and equity $29,160 Income Statement for December 31, 2012 (Thousands of Dollars) Sales $36,000 Operating costs 32,440 Earnings before interest and taxes $ 3,560 Interest 460 Earnings before taxes $ 3,100 Taxes (40%) 1,240 Net income $ 1,860 Dividends (45%) $ 837 Addition to retained earnings $ 1,023a. Suppose 2013 sales are projected to increase by 25% over 2012 sales. Use the forecasted financial statement method to forecast a balance sheet and income statement for December 31, 2013. The interest rate on all debt is 7%, and cash earns no interest income. Assume that all additional debt is added at the end of the year, which means that you should base the forecasted interest expense on the balance of debt at the beginning of the year. Use the forecasted income statement to determine the addition to retained earnings. Assume that the company was operating at full capacity in 2012, that it cannot sell off any of its fixed assets, and that any required financing will be borrowed as notes payable. Also, assume that assets, spontaneous liabilities, and operating costs are expected to increase by the same percentage as sales. Determine the additional funds needed. Round your answers to the nearest dollar. Do not round intermediate calculations. Total assets AFN b. What is the resulting total forecasted amount of notes payable? Round your answer to the nearest dollar. Do not round intermediate calculations. Notes payable $ C. In your answers to Parts a and b, you should not have charged any interest on the additional debt added during 2013 because it was assumed that the new debt was added at the end of the year. But now suppose that the new debt is added throughout the year. Don't do any calculations, but how would this change the answers to parts a and b? If debt is added throughout the year rather than only at the end of the year, interest expense will be | -select Y than in the projections of part a. This would cause net income to be -Select " ], the addition to retained earnings to be -Select- " , and the AFN to be |-Select- " ] . Thus, you would have to -Select- new debt