EXCEL HELP! I already answered the question ( look at my screenshots) however the 2nd part of the question states to "set up a simple excel data table" as you can see I did multiple excel data tables for each of the rates asked in the question. I need help to make a table with all 12 of the percentage rates to automatically be in one data table not 12 separate ones.
Only answer if you can help me turn this into one data table. Not too good with excel so if you could explain how you set it up that would help me tremendously.
Thanks in advance!
4. Glasgo Inc. forecasts the free cash flows (FCFs) (in millions) shown below. The weighted average cost of capital (WACC) is 12%, and the FCFs are expected to continue growing at a 5% rate after Year 4. The firm has $239.27 million of market value debt, but it has no preferred stock or any other outstanding claims. There are 145 million shares outstanding. Year FCF 2 $75 3 $90 4 $150 $45 A) What is the value of the stock price today (Year O)? Year FCF 1 2. $45 $75 $90 $150 3 4 Terminal Value Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $2,250.00 $2,400 0.63552 $1,525.24 Total Value of Firm $1,689.27 Market Value of Debt 239.27 Value of Common Equity $1,450.00 Number of Shares Outstanding 145 Value of Stock Price Today (Year O) $10.00 B) Set up a simple Excel data table where you show how the estimated intrinsic value varies as the long-run growth rate varies over the following range (4.00%, 4.25%, 4.50%, 4.75%, 5.00%, 5.25%, 5.50%, 5.75%, 6.00%, 6.25%, 6.50%, and 6.75%) assuming everything else stays constant Long-Term Growth Rate= 4.00% Growth Rate Stock Price Year FCF Terminal Value 4.00% $8.69 1 $45 4.25% 2 4.50% 3 $75 $90 $150 4.75% 4 $1,950 5.00% Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $2,100 0.63552 $1,334.59 Total Value of Firm $1,498.62 Market Value of Debt 239.27 Value of Common Equity $1,259.35 Number of Shares Outstanding 145 Value of Stock Price Today (Year 0) $8.69 5.25% 5.50% $8.98 $9.30 $9.64 $10.00 $10.39 $10.81 $11.26 $11.75 $12.29 $12.87 $13.51 5.75% 6.00% 6.25% 6.50% 4.25% 6.75% Long-Term Growth Rate= Year FCF Terminal Value 1 2 $45 $75 $90 3 4 $150 $2,018 Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $2,168 0.63552 $1,377.64 Total Value of Firm $1,541.67 Market Value of Debt 239.27 Value of Common Equity $1,302.40 Number of Shares Outstanding 145 A B D E F 45 145 Number of Shares Outstanding Value of Stock Price Today (Year O) 46 $8.98 47 48 4.50% Long-Term Growth Rate= Year 49 FCF Terminal Value 50 1 $45 51 2 52 3 $75 $90 $150 53 4 $2,090 Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $2,240 0.63552 $1,423.56 Total Value of Firm $1,587.59 Market Value of Debt 239.27 Value of Common Equity $1,348.32 Number of Shares Outstanding 145 Value of Stock Price Today (Year O) $9.30 54 55 56 57 58 59 60 4.75% Long-Term Growth Rate= Year 61 FCF Terminal Value 62 1 $45 63 2 64 3 $75 $90 $150 65 4 $2,167 Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $2,317 0.63552 $1,472.65 Total Value of Firm $1,636.68 Market Value of Debt 239.27 Value of Common Equity $1,397.41 Number of Shares Outstanding 145 Value of Stock Price Today (Year O) $9.64 66 67 68 69 70 71 5% Long-Term Growth Rate= Year FCF Terminal Value 1 $45 2 3 $75 $90 $150 4 $2,250 Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $2,400 0.63552 $1,525.24 Total Value of Firm $1,689.27 Market Value of Debt 239.27 Value of Common Equity $1,450.00 Number of Shares Outstanding Value of Stock Price Today (Year 0) $10.00 145 5.25% Long-Term Growth Rate= Year FCF Terminal Value 1 $45 2 $75 3 $90 $150 4 $2,339 Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $2,489 0.63552 $1,581.73 Total Value of Firm $1,745.76 Market Value of Debt 239.27 Value of Common Equity $1,506.49 Number of Shares Outstanding 145 Value of Stock Price Today (Year 0) $10.39 - Long-Term Growth Rate= 5.50% Year FCF Terminal Value 1 $45 $75 2 3 $90 4 $150 $2,435 Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $2,585 0.63552 $1,642.57 Total Value of Firm $1,806.60 Market Value of Debt 239.27 Value of Common Equity $1,567.33 Number of Shares Outstanding 145 Value of Stock Price Today (Year 0) $10.81 Long-Term Growth Rate= 5.75% Year FCF Terminal Value 1 $45 $75 2 3 $90 4 $150 $2,538 Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $2,688 0.63552 $1,708.27 Total Value of Firm $1,872.30 Market Value of Debt 239.27 Value of Common Equity $1,633.03 Number of Shares Outstanding 145 Value of Stock Price Today (Year 0) $11.26 Long-Term Growth Rate= 6% Year FCF Terminal Value 1 2 $45 $75 $90 $150 3 4 $2,650 Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $2,800 0.63552 $1,779.45 Total Value of Firm $1,943.48 Market Value of Debt 239.27 Value of Common Equity $1,704.21 Number of Shares Outstanding 145 Value of Stock Price Today (Year 0) $11.75 Long-Term Growth Rate= 6.25% Year FCF Terminal Value 1 2 $45 $75 $90 $150 3 4 $2,772 Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $2,922 0.63552 $1,856.82 Total Value of Firm $2,020.85 Market Value of Debt 239.27 Value of Common Equity $1,781.58 Number of Shares Outstanding 145 Value of Stock Price Today (Year 0) $12.29 - Long-Term Growth Rate= 6.50% Year FCF Terminal Value 1 $45 2 3 $75 $90 $150 4 $2,905 Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $3,055 0.63552 $1,941.22 Total Value of Firm $2,105.25 Market Value of Debt 239.27 Value of Common Equity $1,865.98 Number of Shares Outstanding 145 Value of Stock Price Today (Year 0) $12.87 Long-Term Growth Rate= 6.75% Year FCF Terminal Value 1 2 $45 $75 $90 $150 3 4 $3,050 Total Cash Flows Present Value Factor @ 12% Present Value of Total Cash Flows $45 0.89286 $40.18 $75 0.79719 $59.79 $90 0.71178 $64.06 $3,200 0.63552 $2,033.66 Total Value of Firm $2,197.69 Market Value of Debt 239.27 Value of Common Equity $1,958.42 Number of Shares Outstanding 145 Value of Stock Price Today (Year 0) $13.51
