Nordstrom Inc. Financial analysis

profileMonis3322
example1.xlsx
Home>Business & Finance homework help>Nordstrom Inc. Financial analysis

Sheet1

Process (Steps) for Computation
1. Complete the "Fundamental Statistics" section
2. Complete the Amortization table. Same as you did in Buy-Rent assignment
3. Complete the Rev/Net Inc Table
4. Complete the FCFE by Year
5. Compute PV of Cash Flows
6. Compute NPV
Step 1
FUNDAMENTAL STATISTICS
Home Depot New Store Project
Estimated Cost of Equity 9.78% Derived as follows: R(rf) 5%
Beta 0.869 Today's Beta will be different
Risk Prem 5.50%
Debt Rating A+
Def Spread 0.80%
Market Interest Rate 5.80%
Marginal Tax Rate 35%
After Tax Cost of Debt 0.0377 3.77% 0.3141666667
Initial CapEX (T0 - New Store) $20M 20,000,000
Debt Issued $5M 5,000,000
Initial Investment in WC $3.2M 3,200,000
WC in Y1 (% of Rev) 8%
WC in Y2-Y10 (change in Rev) 8%
Life of Store (yrs) 10
Residual Value at Y10 $7.5M
Tony Sanichara: Tony Sanichara: End of Life value		 7,500,000
Depreciation/Yr (SL) $1.25M (20M-7.5M)/10
Tony Sanichara: Tony Sanichara: dep 1.25m
Initial Rev per store $40M 40,000,000
Growth in Rev/yr 5%
Tony Sanichara: Tony Sanichara: .058*5m

Tony Sanichara: Tony Sanichara: Use Calc to cpt PMT over 10 yrs at 5.8%
Step 2: Amortization Table for Debt
Tony Sanichara: Tony Sanichara: Note: This is an annual repayment scheule		 Yr OutS Debt Int Exp Total Pmt Prin Repaid Prin Rem
1 5,000,000.00 290,000.00 672,917.36
Tony Sanichara: Tony Sanichara: Use Calc to cpt PMT over 10 yrs at 5.8%		 382,917.36 4,617,082.64 use calc Input 5.8 not .058
2 4,617,082.64 267,790.79 672,917.36 405,126.57 4,211,956.07
3 4,211,956.08 244,293.45 672,917.36 428,623.91 3,783,332.17
4 3,783,332.17 219,433.27 672,917.36 453,484.09 3,329,848.08
5 3,329,848.08 193,131.19 672,917.36 479,786.17 2,850,061.91
6 2,850,061.91 165,303.59 672,917.36 507,613.77 2,342,448.14
7 2,342,448.14 135,861.99 672,917.36 537,055.37 1,805,392.78
8 1,805,392.78 104,712.78 672,917.36 568,204.58 1,237,188.20
9 1,237,188.20 71,756.92 672,917.36 601,160.44 636,027.76
10 636,027.76 36,889.61 672,917.36 636,027.75 0.00
Tony Sanichara: Tony Sanichara: 0.01…rounding errors
Step 3: Revenues/Net Income Yr Revenues Op Exp Dep EBIT Int Exp Tax Income Taxes Net Income
1 40,000,000.00
Tony Sanichara: Tony Sanichara: Rev growth 5%		 36,000,000.00
Tony Sanichara: Tony Sanichara: pre-tax op margin is 10% So Op Ex = 90% of Revs		 1,250,000.00 2,750,000.00 290,000.00 2,460,000.00 861,000.00 1,599,000.00
2 42,000,000.00 37,800,000.00 1,250,000.00 2,950,000.00 267,790.79 2,682,209.21 938,773.22 1,743,435.98
3 44,100,000.00 39,690,000.00 1,250,000.00 3,160,000.00 244,293.45 2,915,706.55 1,020,497.29 1,895,209.26
4 46,305,000.00 41,674,500.00 1,250,000.00 3,380,500.00 219,433.27 3,161,066.73 1,106,373.36 2,054,693.38
5 48,620,250.00 43,758,225.00 1,250,000.00 3,612,025.00 193,131.19 3,418,893.81 1,196,612.83 2,222,280.98
6 51,051,262.50 45,946,136.25 1,250,000.00 3,855,126.25 165,303.59 3,689,822.66 1,291,437.93 2,398,384.73
7 53,603,825.63 48,243,443.06 1,250,000.00 4,110,382.56 135,861.99 3,974,520.57 1,391,082.20 2,583,438.37
8 56,284,016.91 50,655,615.22 1,250,000.00 4,378,401.69 104,712.78 4,273,688.91 1,495,791.12 2,777,897.79
9 59,098,217.75 53,188,395.98 1,250,000.00 4,659,821.78 71,756.92 4,588,064.86 1,605,822.70 2,982,242.16
10 62,053,128.64 55,847,815.78 1,250,000.00 4,955,312.86 36,889.61 4,918,423.25 1,721,448.14 3,196,975.12
Step 4: FCF and NPV
Yr Net Income Depreciation Capital Expesnditure Debt Issued/Prin Rep Change in WC Salvage FCFE
Revs WC
(20,000,000) 5,000,000 (3,200,000) (18,200,000) 40,000,000 3,200,000
1 1,599,000 1,250,000 (382,917) (160,000) 2,306,083 2,306,083 42,000,000 3,360,000 160,000
2 1,743,436 1,250,000 (405,127) (168,000) 2,420,309 2,420,309 44,100,000 3,528,000
3 1,895,209 1,250,000 (428,624) (176,400) 2,540,185 2,540,185 46,305,000 3,704,400
4 2,054,693 1,250,000 (453,484) (185,220) 2,665,989 2,665,989 48,620,250 3,889,620
5 2,222,281 1,250,000 (479,786) (194,481) 2,798,014 2,798,014 51,051,263 4,084,101
6 2,398,385 1,250,000 (507,614) (204,205) 2,936,566 2,936,566 53,603,826 4,288,306
7 2,583,438 1,250,000 (537,055) (214,415) 3,081,968 3,081,968 56,284,017 4,502,721
8 2,777,898 1,250,000 (568,205) (225,136) 3,234,557 3,234,557 59,098,218 4,727,857
9 2,982,242 1,250,000 (601,160) (236,393) 3,394,689 3,394,689 62,053,129 4,964,250
10 3,196,975 1,250,000 (636,028) 4,964,250 7,500,000 16,275,198 16,275,198 0
(4,964,250)
Future CFs To Equity PV @ COE
Tony Sanichara: Tony Sanichara: Cost of Equity 9.78%
Tony Sanichara: Tony Sanichara: Rev growth 5%
Tony Sanichara: Tony Sanichara: .058*5m
Tony Sanichara: Tony Sanichara: pre-tax op margin is 10% So Op Ex = 90% of Revs
Tony Sanichara: Tony Sanichara: Use Calc to cpt PMT over 10 yrs at 5.8%
Tony Sanichara: Tony Sanichara: Use Calc to cpt PMT over 10 yrs at 5.8%
Tony Sanichara: Tony Sanichara: End of Life value
Tony Sanichara: Tony Sanichara: dep 1.25m
Step 5: Compute PV of Cash Flows 0 (18,200,000) (18,200,000)
1 2,306,083 2,100,640
2 2,420,309 2,008,281
3 2,540,185 1,919,976
4 2,665,989 1,835,547
5 2,798,014 1,754,825
6 2,936,566 1,677,646
7 3,081,968 1,603,856
8 3,234,557 1,533,307
9 3,394,689 1,465,855
10 16,275,198 6,401,681
Step 6: NPV (Done by Add here) NPV 4,101,613

Sheet2

Sheet3