Question

In: Finance

Use the following information to develop a spreadsheet model that will calculate the free cash flows...

Use the following information to develop a spreadsheet model that will calculate the free cash flows and the value of the equity for the company. Cost of capital 12% Most recent year’s sales $1000 Nonoperating assets $100 Interest-bearing debt $250 Operating profit margin 12% Working capital/sales 35% Fixed assets/sales 20% Noninterest-bearing Current liabilities/sales 10% Tax rate 40% Forecasted sales growth Years 1␣2 12% Years 3␣5 8% 6␣N 4%

Solutions

Expert Solution

Please see the snapshot below:

Year, n 0 1 2 3 4 5 6
Sales Si = Si-1 x (1 + gi) 1,000.00          1,120.00 1,254.40 1,354.75 1,463.13 1,580.18 1,643.39
y-o-y growth rate gi 12% 12% 8% 8% 8% 4%
Operating profit EBIT = 12% x S     120.00             134.40     150.53     162.57     175.58     189.62     197.21
Working Capital WC = 35% x S     350.00             392.00     439.04     474.16     512.10     553.06     575.19
[-] Current Liabilities CL = 10% x S     100.00             112.00     125.44     135.48     146.31     158.02     164.34
Net working capital NWC = WC - CL     250.00             280.00     313.60     338.69     365.78     395.05     410.85
Fixed assets FA = 20% x S     200.00             224.00     250.88     270.95     292.63     316.04     328.68
Tax rate T = 40%
Calculation of free cash flows
EBIT x (1 - T)               80.64       90.32       97.54     105.35     113.77     118.32
[-] Increase in net fixed assets FAi - FAi-1               24.00       26.88       20.07       21.68       23.41       12.64
[-] Increase in net working capital NWCi - NWCi-1               30.00       33.60       25.09       27.10       29.26       15.80
Free Cash flows               26.64       29.84       52.38       56.57       61.10       89.88

jeff jeffy answered 6 months ago
Next > < Previous

Related Solutions

Use the Free Cash Flow method of valuation and the following information, to calculate the value...
Use the Free Cash Flow method of valuation and the following information, to calculate the value for a venture with the following information. Expected sales at year zero (or beginning of year 1): $2.50 M; growth rate in sales for the first 8 years: 35%; and for years 9-on: 10%; Annual profit margin (or EBIAT/Sales) for all years=22%; Annual asset intensity ratio (or (FA+WC)/Sales) for all years = 32%; discount rate in years 1-8: 30%, and in years 9-on: 16%....
Use the data given to develop a Statement of Cash flows for 2007 and calculate change...
Use the data given to develop a Statement of Cash flows for 2007 and calculate change in cash from 2006 in operating, investing and financing activities and reconcile to beginning and ending cash. Income Statements (Thousands of Dollars) Katie's Construction Peer 2006 2007 Proj. 2008 Com. Size Net Sales 81,950 83,875 101,488.75 100.00 Cost of goods sold 66,000 70,950 85,849.5 81.00 Gross Profit 15,950 12,925 15,639.25 19.00 0 0 0 S & A Expenses 8,360 9,997.9 11,163.762 10.00 Depreciation 1,100...
Use the following information to calculate: A) Annual Cash Flows for years 1-6; B) the Initial...
Use the following information to calculate: A) Annual Cash Flows for years 1-6; B) the Initial Outlay; C) Depreciable Base; D) Depreciation Expense; E) Proceeds from Sale of Equipment (aka Cash Flow from Sale); F) the CF Total from the Terminal Year 6; G) the Net Present Value (NPV) of the Cash Flows for years 1-6; and H) the IRR (Internal Rate of Return) The answers to this question should be listed from A to H, with their corresponding values...
Spreadsheet and Statement of Cash Flows The following information was taken from Lamberson Company's accounting records:...
Spreadsheet and Statement of Cash Flows The following information was taken from Lamberson Company's accounting records: Account Balances Account Titles January 1, 2016 December 31, 2016 Debits Cash $ 1,400 $ 2,400 Accounts Receivable (net) 2,800 2,690 Marketable Securities (at cost) 1,700 3,000 Allowance for Change in Value 500 800 Inventories 8,100 7,910 Prepaid Items 1,300 1,710 Investments (long-term) 7,000 5,400 Land 15,000 15,000 Buildings and Equipment 32,000 46,200 Discount on Bonds Payable — 290 $69,800 $85,400 Credits Accumulated Depreciation...
Spreadsheet and Statement of Cash Flows The following information was taken from Lamberson Company's accounting records:...
Spreadsheet and Statement of Cash Flows The following information was taken from Lamberson Company's accounting records: Account Balances Account Titles January 1, 2016 December 31, 2016 Debits Cash $ 1,400 $ 2,400 Accounts Receivable (net) 2,800 2,690 Marketable Securities (at cost) 1,700 3,000 Allowance for Change in Value 500 800 Inventories 8,100 7,910 Prepaid Items 1,300 1,710 Investments (long-term) 7,000 5,400 Land 15,000 15,000 Buildings and Equipment 32,000 46,200 Discount on Bonds Payable — 290 $69,800 $85,400 Credits Accumulated Depreciation...
Use the cash flows of a portfolio of the following three bonds to calculate the yield...
Use the cash flows of a portfolio of the following three bonds to calculate the yield to maturity of the portfolio. Each bond has a face value of $1,000. Use the cash flows and yield to maturity to calculate the duration of the portfolio. a. three-year zero-coupon bond with 5% yield, b. five-year zero-coupon bond with 7% yield, c. seven-year zero-coupon bond with 6% yield. Verify your portfolio duration calculation using the weighted average of the individual bond durations.
Upload an Excel spreadsheet or use the indirect method showing the cash flows of the investment...
Upload an Excel spreadsheet or use the indirect method showing the cash flows of the investment for the € 400.000 investment. Discuss your findings in about 100 words. ( note : you have to assume the numbers and how you want to invest ) Estimate/calculate the overall rate of return (WACC) for the € 400.000 investment based on your personal assumptions. How are you going to finance this investment? Discuss about the capital structure mix you have chosen and how...
​(NPVcalculation​)Calculate the NPV given the following free cash​ flows, if the appropriate required rate of return...
​(NPVcalculation​)Calculate the NPV given the following free cash​ flows, if the appropriate required rate of return is 8 percent. Should the project be​ accepted? YEARCASH FLOW    0 −​$90,000    1      10,000    2      10,000    3      15,000    4      15,000    5      30,000    6      30,000 MIRRcalculation​)Calculate the MIRR given the following free cash​ flows if the appropriate required rate of return is 12 percent​ (use this as the reinvestment​rate). Should the project be​ accepted? YEAR   CASH FLOWS 0   -50,000 1   35,000 2   35,000 3   35,000...
The following are cash flows for a prospective project. Use this information for the next few...
The following are cash flows for a prospective project. Use this information for the next few problems Year Cash Flow 0 -132,000 1 97,000 2 42,000 3 28,000 A. what is the payback period for the project? B. If the appropriate discount rate for this project is 10%, what is the projects net present value? C. If the appropriate discount rate for this project is 10%, what is the profitability index? D. An argument against accepting the project is that...
STU Company has the following financial information for its most recent period. Free Cash Flows (Cash...
STU Company has the following financial information for its most recent period. Free Cash Flows (Cash flows to the firm) $325,000 Equity Cash Flows (Cash flows to equity) $230,000 Market value of debt $481,000 A search of comparable firms ("comps") indicated the following price multiples. Guideline Company Analysis Price / Earnings (P/E) Price / EBITDA Comp #1 9.3 6.7 Comp #2 9.1 7.8 Comp #3 7.2 7.0 Comp #4 9.1 8.2 Comp #5 10.0 7.7 Comp #6 6.8 6.9 Comp...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT