Finance homework need help: 5 years financial data( You can copy and paste) with excel file

User Generated

jwujjjwnpr

Business Finance

Description

Choose Tiffany & Co. (TIF) and find the financial data: https://finance.yahoo.com/quote/TIF/financials?p=T...

the example is in attach

Check the rubric to see what my specific requirements are for your spreadsheet; and use the Ralph Lauren Case Study as an example.

At this first stage, we are just looking for 5 years of raw financial data, which you can obtain from sec.gov (Links to an external site.)Links to an external site.. You can cut and paste; but it should have Colums 2012, 2013, 2014, 2015, 2016 and 2017 reading left to right, just as you see in the Ralph Lauren example.

Don't bother to regroup items at this stage in order to simplify the financial statements.

Unformatted Attachment Preview

Historical and Projected Financia Ralph Lauren Corp. Period Income Statement Revenues: Total revenues Cost of goods sold Gross profit Operating Expenses: Selling, general and administrative Equity in earnings Non-recurring items Unreconciled Amt. Interest expense, net Pre-tax income Income taxes Net income Balance Sheet Assets Cash and equivalents Short-term investments Accounts receivable, net Inventories Other current assets Total current assets Fixed assets (net) Intangibles Goodwill Long-term investments Other fixed assets Total assets Accounts payable Accrued expenses Short-term debt Other current liabilities Total current liabilities Long-term debt Other liabilities 2012 2013 2014 2015 6,860 2,862 3,998 6,945 2,789 4,156 7,450 3,140 4,310 7,620 3,242 4,378 2,916 (9) 12 33 13 1,015 334 681 2,971 (10) 12 58 16 1,089 339 750 3,142 (9) 19 27 17 1,096 320 776 3,301 (11) 17 51 11 987 285 702 672 516 547 842 324 2,900 884 359 1,004 100 130 5,416 974 325 458 896 310 2,963 932 328 968 81 124 5,418 797 488 588 1,020 436 3,329 1,322 299 964 500 644 655 1,042 483 3,324 1,436 267 903 135 6,088 131 6,106 181 147 664 267 43 1,121 203 690 0 77 970 298 786 210 715 234 27 1,186 298 731 0 766 946 274 543 512 Total liabilities Additional Fund Needed - Cumulative (Surplus) Shareholders' equity Common Stock Paid-in capital Retained earnings Treasury stock Total Liabilities and Equity 1,764 1,633 2,054 2,215 0 0 0 0 3,653 198 1,624 4,042 (2,212) 3,785 95 1,752 4,647 (2,709) 4,034 115 1,979 5,257 (3,317) 3,891 (164) 2,117 5,787 (3,849) 6,088 6,106 5,416 5,418 The first step in computing FCF is to restate do a Pen That means to break the BS down into Net operating stakeholder claims. Note that assets are treated as a negative (since they and equity are treated as positive (since they represe Total assets Less non operating assets Marketable securities (short term Investment) Operating liabilities Accounts payable Accruals Short term debt Other Current liabilities Other non current liabilities Net operating assets Non operating assets Book value of the firm Additional Fund Needed - Cumulative (Surplus) Long term debt Common stock Paid In capital Retained earnings Accumulated other(treasury sock) (5,416) 516 (5,418) 325 (6,088) 488 (6,106) 644 181 0 0 766 543 (3,411) 147 664 267 43 512 (3,460) 203 690 0 77 786 (3,844) 210 715 234 27 731 (3,545) (516) (325) (488) (644) (3,927) (3,785) (4,332) (4,189) 0 0 0 0 274 198 1,624 4,042 (2,212) 0 95 1,752 4,647 (2,709) 298 115 1,979 5,257 (3,317) 298 (164) 2,117 5,787 (3,849) Stakeholder claims Total Changes 3,927 3,785 4,332 4,189 0 0 0 0 The next step is to compute changes by subtracting o (2) (670) (18) (191) 163 156 (34) 664 267 (723) (31) (49) 56 26 (267) 34 274 (384) 7 25 234 (50) (55) 299 Non operating assets 191 (163) (156) Book value of the firm 142 (547) 143 0 (274) (103) 128 605 (497) (142) 0 298 20 227 610 (608) 547 0 0 (279) 138 530 (532) (143) (0) 0 0 Total assets Less non operating assets Marketable securities (short term Investment) Operating liabilities Accounts payable Accruals Short term debt Other Current liabilities Other non current liabilities Net operating assets Additional Fund Needed Long term debt Common stock Paid In capital Retained earnings Accumulated other(treasury sock) Stakeholder claims Total Computation of FCF The final step is to use those change numbers to com Net Income Add back interest expense Less tax on Interest expense NOPAT (Additions to Net operating assets) FCF 750 16 (6) 760 (49) 711 776 17 (7) 786 (384) 402 702 11 (4) 709 299 1,008 Additional Fund Needed (Surplus) (Pay off for long term debt) 0 (274) 0 298 0 0 Additional common stock isssued/(Shares repurchased) Additional Paid in capital (Dividends paid) (Interest paid net off tax) (Increase) in non-operating assets Increase/(Decrease) in accumulated other Dispositions of FCF Total (103) 128 (145) (10) 191 20 227 (166) (10) (163) (279) 138 (172) (7) (156) (497) (711) (608) (402) (532) (1,008) 0 0 0 Positive means source of cash and negative represen ed Financial Statements uren Corp. 2016 2017 2018 2019 2020 2021 7,405 3,218 4,187 7,553 3,282 4,271 7,704 3,348 4,356 7,858 3,415 4,443 8,015 3,483 4,532 8,176 3,553 4,623 3,389 (11) 192 28 15 552 156 396 3,457 (11) 0 3,526 (11) 0 3,596 (11) 0 3,668 (11) 0 3,742 (11) 0 15 788 315 473 15 804 322 483 15 821 328 493 15 838 335 503 15 855 342 513 456 629 517 1,125 326 3,053 1,583 244 918 296 6,213 465 629 527 1,148 333 3,101 1,615 249 936 0 302 6,203 474 629 538 1,170 339 3,164 1,647 254 955 0 308 6,327 484 629 549 1,194 346 3,227 1,680 259 974 0 314 6,454 494 629 560 1,218 353 3,291 1,713 264 994 0 320 6,583 503 629 571 1,242 360 3,357 1,748 269 1,014 0 327 6,715 151 898 116 33 1,198 597 674 154 916 116 34 1,220 597 687 157 934 116 34 1,242 597 701 160 953 116 35 1,264 597 715 163 972 116 36 1,287 597 730 167 991 116 36 1,311 597 744 2,469 2,504 2,540 2,576 2,614 2,652 0 (350) (576) (810) (1,053) (1,305) 3,744 (180) 2,258 6,015 (4,349) 4,049 (180) 2,258 6,320 (4,349) 4,363 (180) 2,258 6,634 (4,349) 4,688 (180) 2,258 6,959 (4,349) 5,022 (180) 2,258 7,293 (4,349) 5,368 (180) 2,258 7,639 (4,349) 6,213 6,203 6,327 6,454 6,583 6,715 (6,583) (6,715) o restate do a Penman restatement of the Balance Sheet nto Net operating assets, non-operating assets, and gative (since they represent a use of cash), and liabilities since they represent a source of cash). (6,213) 629 (6,203) 629 (6,327) 629 (6,454) 629 629 629 151 898 116 33 674 (3,712) 154 916 116 34 687 (3,667) 157 934 116 34 701 (3,755) 160 953 116 35 715 (3,845) 163 972 116 36 730 (3,937) 167 991 116 36 744 (4,031) (629) (629) (629) (629) (629) (629) (4,341) (4,296) (4,384) (4,474) (4,566) (4,660) (350) 597 (180) 2,258 6,320 (4,349) (576) 597 (180) 2,258 6,634 (4,349) (810) 597 (180) 2,258 6,959 (4,349) (1,053) 597 (180) 2,258 7,293 (4,349) (1,305) 597 (180) 2,258 7,639 (4,349) 0 597 (180) 2,258 6,015 (4,349) 4,341 4,296 4,384 4,474 4,566 4,660 0 0 0 0 0 0 s by subtracting out the balances from the previous year. (107) 10 (124) (127) (129) (132) (15) 0 0 0 0 0 (59) 183 (118) 6 (57) (167) 3 18 0 1 13 45 3 18 0 1 14 (88) 3 19 0 1 14 (90) 3 19 0 1 14 (92) 3 19 0 1 15 (94) 15 0 0 0 0 0 (152) 45 (88) (90) (92) (94) 0 299 (16) 141 228 (500) 152 (350) 0 0 0 305 0 (45) (226) 0 0 0 315 0 88 (234) 0 0 0 325 0 90 (243) 0 0 0 335 0 92 (251) 0 0 0 345 0 94 0 0 (0) 0 0 0 e numbers to compute FCF and the Disposition of FCF. 396 15 (6) 405 (167) 238 473 15 (6) 482 45 527 483 15 (6) 492 (88) 403 493 15 (6) 502 (90) 411 503 15 (6) 512 (92) 420 513 15 (6) 522 (94) 428 0 299 (350) 0 (226) 0 (234) 0 (243) 0 (251) 0 (16) 141 (168) (9) 15 0 0 (168) (9) 0 0 0 (168) (9) 0 0 0 (168) (9) 0 0 0 (168) (9) 0 0 0 (168) (9) 0 (500) (238) 0 (527) 0 (403) 0 (411) 0 (420) 0 (428) 0 0 (0) 0 negative represents a disposition, or distribution of cash. 0 (0) https://finance.yahoo.com/quote/RL?ltr=1 (Data has been taken from this site) Calculation of WACC Beta Risk-free Rate Market risk premium Cost of Equity Weight of Equity 0.62 0.02 0.08 0.0696 0.8827 Cost of debt Weight of debt Tax Rate 0.06144 Hence, WACC 0.03 0.1173 (Weights were based on book values, and shou 0.4 (We have assumed 40% tax all over) 0.0021107 0.06355 Calculation of Horizon Value FCF 2021 Growth FCF 2022 WACC Growth HV 2021 428.39 0.02 (We have assumed 2% growth all over) 436.957 0.06355 0.02 10033.6 Calculation of Value of the Firm Year FCF PViF 2017 2018 2019 2020 2021 HV 527 403 411 420 428 10034 0.940 0.884 0.831 0.782 0.735 0.735 Total value of the firm Less Debt Value of Equity No. of Shares Value per Share Market value per share PVCF 495 M 357 342 328 315 7,373 9,210 (713) 8,497 48 176.40 111.92 M M M M book values, and should have been based on market values)
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Find the attached copy of the complete assignment
Goodbye

Historical and Projected Financia
Tiffany & Co. (TIF)
(in thousands)

Period
Income Statement
Revenues:
Total revenues
Cost of goods sold
Gross profit
Operating Expenses:
Selling, general and administrative
Equity in earnings
Non-recurring items
Unreconciled Amt.
Interest expense, net
Pre-tax income
Income taxes
Net income
Balance Sheet
Assets
Cash and equivalents
Short-term investments
Accounts receivable, net
Inventories
Other current assets
Total current assets
Fixed assets (net)
Intangibles
Goodwill
Long-term investments
Other fixed assets
Total assets
Accounts payable
Accrued expenses
Short-term debt
Other current liabilities
Total current liabilities
Long-term debt

2013

2014

2015

3.794
1.631
2.163

4.031
1.691
2.340

4.250
1.713
2.537

1.466
0
0
37
16
644
227
416

1.556
0
0
467
63
255
74
181

1.646
0
0
91
63
738
253
484

505
80
174
2.234
159
3.152
819
306
0
0
354
4.631

346
1
221
2.327
436
3.330
1.004
155
0
0
263
4.752

730
2
195
2.362
220
3.509
900
75
39
54
606
5.181

295
31
194
67
587
765

203
690
0
77
970
298

358
0
234
66
658
883

Other liabilities
Total liabilities

668
2.019

786
2.054

789
2.330

0

0

0

Shareholders' equity
Common Stock
Paid-in capital
Retained earnings
Treasury stock

2.599
1
1.020
1.671
(94)

4.034
1
1.979
5.257
(3.317)

2.835
1
1.174
1.951
(291)

Total Liabilities and Equity

4.618

6.088

5.165

(4.631)

(4.752)

(5.181)

Addi...


Anonymous
Really helped me to better understand my coursework. Super recommended.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags