FIN 3110 CFI Finance Ford Motor Company Excel Sheet

Company Name
Ticker Symbol
Best Buy Co., Inc.
BBY
Weights
E = MV of Equity
D = MV of Debt
Yahoo! Finance – Summary – Market Cap
SEC Filings Form 10-K for BBY FY 2022
V = Total MV
WE =
WD =
Cost of Common Equity (RE) Formula
RF
MRP
Beta
CAPM Formula RE = RF + MRP * b
Proxied by T-bonds here, 2013-2022 period (see tab “Historical P
2013-2022 period period (see tab “Historical Performance”)
Yahoo! Finance – Summary Beta (5-year Monthly)
Before-tax Cost of Debt (RD) Formula
EBIT (Operating Income)
Interest Expense ($ mill)
Times Interest Earned (TIE)
Defauld Spread
Before-tax cost of debt =
SEC Filings Form 10-K for BBY FY 2022
SEC Filings Form 10-K for BBY FY 2022
TIE = Operating Income/Interest Expense
Synthetic Rating
Risk-free rate + Default Spread
Tax Rate (TC)
Taxes ($ mill)
Pretax Income (EBT) ($ mill)
Tax Rate = Taxes/Pretax Income
SEC Filings Form 10-K for BBY FY 2022
SEC Filings Form 10-K for BBY FY 2022
WACC
Wi
Equity
Debt
tax rate
Cost
WACC
Upload a scanned image under Screenshots tab
Upload a scanned image under Screenshots tab
2022 period (see tab “Historical Performance”)
ab “Historical Performance”)
a (5-year Monthly)
Upload a scanned image under Screenshots tab
Upload a scanned image under Screenshots tab
Upload a scanned image under Screenshots tab
Upload a scanned image under Screenshots tab
Upload a scanned image under Screenshots tab
Year
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
Stocks
Annuals Returns
T-Bonds
T-Bills
43.81%
-8.30%
-25.12%
-43.84%
-8.64%
49.98%
-1.19%
46.74%
31.94%
-35.34%
29.28%
-1.10%
-10.67%
-12.77%
19.17%
25.06%
19.03%
35.82%
-8.43%
5.20%
5.70%
18.30%
30.81%
23.68%
18.15%
-1.21%
52.56%
32.60%
7.44%
-10.46%
43.72%
12.06%
0.34%
26.64%
-8.81%
22.61%
16.42%
12.40%
-9.97%
23.80%
10.81%
-8.24%
0.84%
4.20%
4.54%
-2.56%
8.79%
1.86%
7.96%
4.47%
5.02%
1.38%
4.21%
4.41%
5.40%
-2.02%
2.29%
2.49%
2.58%
3.80%
3.13%
0.92%
1.95%
4.66%
0.43%
-0.30%
2.27%
4.14%
3.29%
-1.34%
-2.26%
6.80%
-2.10%
-2.65%
11.64%
2.06%
5.69%
1.68%
3.73%
0.72%
2.91%
-1.58%
3.27%
-5.01%
3.08%
3.16%
4.55%
2.31%
1.07%
0.96%
0.28%
0.17%
0.17%
0.28%
0.07%
0.05%
0.04%
0.13%
0.34%
0.38%
0.38%
0.38%
0.38%
0.60%
1.05%
1.12%
1.20%
1.52%
1.72%
1.89%
0.94%
1.72%
2.62%
3.22%
1.77%
3.39%
2.87%
2.35%
2.77%
3.16%
3.55%
3.95%
4.86%
4.29%
5.34%
6.67%
Annual Risk Premium
Stocks – Bonds Stocks – T-Bills
42.98%
40.73%
-12.50%
-11.46%
-29.66%
-29.67%
-41.28%
-46.15%
-17.43%
-9.71%
48.13%
49.02%
-9.15%
-1.47%
42.27%
46.57%
26.93%
31.77%
-36.72%
-35.61%
25.07%
29.22%
-5.51%
-1.14%
-16.08%
-10.71%
-10.75%
-12.90%
16.88%
18.83%
22.57%
24.68%
16.45%
18.65%
32.02%
35.44%
-11.56%
-8.81%
4.28%
4.60%
3.75%
4.66%
13.64%
17.19%
30.38%
29.60%
23.97%
22.16%
15.88%
16.43%
-5.35%
-3.10%
49.27%
51.63%
33.93%
30.87%
9.70%
4.82%
-17.25%
-13.68%
45.82%
41.95%
14.70%
8.67%
-11.30%
-2.54%
24.58%
24.29%
-14.51%
-11.58%
20.93%
19.46%
12.69%
12.87%
11.68%
8.45%
-12.88%
-14.83%
25.38%
19.51%
7.54%
5.48%
-3.23%
-14.91%
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
3.56%
14.22%
18.76%
-14.31%
-25.90%
37.00%
23.83%
-6.98%
6.51%
18.52%
31.74%
-4.70%
20.42%
22.34%
6.15%
31.24%
18.49%
5.81%
16.54%
31.48%
-3.06%
30.23%
7.49%
9.97%
1.33%
37.20%
22.68%
33.10%
28.34%
20.89%
-9.03%
-11.85%
-21.97%
28.36%
10.74%
4.83%
15.61%
5.48%
-36.55%
25.94%
14.82%
2.10%
15.89%
32.15%
16.75%
9.79%
2.82%
3.66%
1.99%
3.61%
15.98%
1.29%
-0.78%
0.67%
-2.99%
8.20%
32.81%
3.20%
13.73%
25.71%
24.28%
-4.96%
8.22%
17.69%
6.24%
15.00%
9.36%
14.21%
-8.04%
23.48%
1.43%
9.94%
14.92%
-8.25%
16.66%
5.57%
15.12%
0.38%
4.49%
2.87%
1.96%
10.21%
20.10%
-11.12%
8.46%
16.04%
2.97%
-9.10%
6.39%
4.33%
4.06%
7.04%
7.85%
5.79%
4.98%
5.26%
7.18%
10.05%
11.39%
14.04%
10.60%
8.62%
9.54%
7.47%
5.97%
5.78%
6.67%
8.11%
7.50%
5.38%
3.43%
3.00%
4.25%
5.49%
5.01%
5.06%
4.78%
4.64%
5.82%
3.40%
1.61%
1.01%
1.37%
3.15%
4.73%
4.36%
1.37%
0.15%
0.14%
0.05%
0.09%
0.06%
-13.19%
4.43%
15.94%
-17.97%
-27.89%
33.39%
7.85%
-8.27%
7.29%
17.85%
34.72%
-12.90%
-12.40%
19.14%
-7.59%
5.52%
-5.79%
10.77%
8.31%
13.78%
-9.30%
15.23%
-1.87%
-4.24%
9.36%
13.71%
21.25%
23.16%
13.42%
29.14%
-25.69%
-17.42%
-37.08%
27.98%
6.25%
1.97%
13.65%
-4.73%
-56.65%
37.05%
6.36%
-13.94%
12.92%
41.25%
-2.83%
9.89%
14.69%
-21.34%
-33.75%
31.21%
18.85%
-12.24%
-0.67%
8.47%
20.34%
-18.74%
9.82%
13.72%
-3.39%
23.76%
12.53%
0.04%
9.87%
23.36%
-10.56%
24.86%
4.06%
6.97%
-2.93%
31.71%
17.68%
28.04%
23.56%
16.25%
-14.85%
-15.25%
-23.57%
27.35%
9.37%
1.69%
10.88%
1.12%
-37.92%
25.78%
14.68%
2.05%
15.80%
32.09%
13.52%
1.38%
11.77%
21.61%
-4.23%
31.21%
18.02%
28.47%
-18.01%
10.75%
1.28%
0.69%
2.80%
-0.02%
9.64%
11.33%
-4.42%
-17.83%
0.03%
0.05%
0.32%
0.93%
1.94%
2.06%
0.35%
0.05%
2.02%
Arithmetic Average Historical Return
Period
Stocks
1928-2022
11.51%
2013-2022
13.59%
T-Bonds
4.87%
0.51%
T-Bills
3.32%
0.78%
2014
2015
2016
2017
2018
2019
2020
2021
2022
2.78%
0.09%
11.08%
18.80%
-4.21%
21.58%
6.69%
32.88%
-0.18%
13.49%
1.33%
11.46%
20.67%
-6.16%
29.15%
17.67%
28.42%
-20.03%
Geometric Average Historical
Period
1928-2022
2013-2022
Market Risk Premium = MRP = E(RM)-RF
Period
1928-2022
2013-2022
Stocks – T-Bonds
6.64%
13.08%
Stocks – T-Bills
8.19%
12.81%
Standard Error of Market Risk Premium (MRP) – higher number would imply greater estima