"You are required to perform valuation for HES Company as given below.
Show detail work and discuss any assumptions you make.
Consider HES Company's financial statements given below. Assume the Company's beta is estimated to be 1.5, risk free rate 2%, and market risk premium 10%. Furthermore, assume the company has a long-term growth rate for 2% after the fifth year and net income and comprehensive income will be identical.
What is the company's value using the following methods:
a) Residual Income, Value to Book, Free Cash Flow to Equity, Free Cash Flow to Company (FCF, Debt and Equity)
c) If Owners decide to issue 1,000,000 common shares, how much would be the value for each share?
d) Analyze and explain which one of the above valuation methods provides a better and more realistic valuation. "
Need to be able to review all calculations so nothing can be hard coded in excel without an accompanying note to explain how the number was derived.?
| Income Statement |
| |
Year 1 |
Year 2 |
Year 3 |
Year 4 |
Year 5 |
| Revenues |
$1,976,000 |
$2,074,800 |
$2,178,540 |
$2,287,467 |
$2,401,840 |
| Cost of Sales |
1,213,659 |
1,274,342 |
1,338,059 |
1,404,962 |
1,475,210 |
| Gross Profit |
$762,341 |
$800,458 |
$840,481 |
$882,505 |
$926,630 |
| |
|
|
|
|
|
| Accounting |
6,000 |
6,300 |
6,615 |
6,946 |
7,293 |
| Advertising & Promotion |
15,000 |
12,360 |
12,731 |
13,113 |
13,506 |
| Bank Charges |
41,496 |
43,571 |
45,749 |
48,037 |
50,439 |
| Compensation & Benefits |
246,643 |
254,042 |
261,663 |
269,513 |
277,598 |
| Consulting Fees |
2,400 |
0 |
0 |
0 |
0 |
| Insurance |
1,000 |
1,050 |
1,103 |
1,158 |
1,216 |
| Lease - Facilities |
336,000 |
336,000 |
336,000 |
336,000 |
336,000 |
| Legal & Professional |
500 |
500 |
500 |
500 |
500 |
| Licenses & Fees |
500 |
510 |
520 |
531 |
541 |
| Maintenance |
600 |
612 |
624 |
637 |
649 |
| Miscellaneous |
1,800 |
1,836 |
1,873 |
1,910 |
1,948 |
| Office supplies |
2,700 |
2,754 |
2,809 |
2,865 |
2,923 |
| Security |
720 |
742 |
764 |
787 |
810 |
| Telephone |
1,800 |
1,836 |
1,873 |
1,910 |
1,948 |
| Utilities |
4,200 |
4,410 |
4,631 |
4,862 |
5,105 |
| Website |
1,800 |
1,800 |
1,800 |
1,800 |
1,800 |
| Total Operating Exp. |
663,159 |
668,323 |
679,255 |
690,569 |
702,276 |
| EBIDTA |
$99,182 |
$132,135 |
$161,226 |
$191,936 |
$224,354 |
| Depreciation |
4,916 |
4,916 |
4,916 |
4,916 |
4,916 |
| Operating Profit |
$94,266 |
$127,219 |
$156,310 |
$187,020 |
$219,438 |
| Interest Expense |
43,199 |
40,274 |
37,059 |
33,524 |
29,639 |
| Earnings Before Taxes |
51,067 |
86,945 |
119,251 |
153,496 |
189,799 |
| Income Taxes |
17,873 |
30,431 |
41,738 |
53,724 |
66,429 |
| Net Income |
$33,194 |
$56,514 |
$77,513 |
$99,772 |
$123,370 |
| Balance sheets |
| |
Begin |
Year 1 |
Year 2 |
Year 3 |
Year 4 |
Year 5 |
| Cash |
$33,150 |
$110,697 |
$210,697 |
$243,259 |
$293,834 |
$363,370 |
| Inventory |
425,000 |
425,000 |
424,781 |
446,020 |
468,321 |
491,737 |
| Prepaid Leases |
28,000 |
28,000 |
28,000 |
28,000 |
28,000 |
28,000 |
| Total Current Assets |
$486,150 |
$563,697 |
$663,478 |
$717,279 |
$790,155 |
$883,107 |
| |
|
|
|
|
|
|
| Fixed Assets |
33,850 |
33,850 |
33,850 |
33,850 |
33,850 |
33,850 |
| Less: Depreciation |
0 |
4,916 |
9,832 |
14,747 |
19,664 |
24,579 |
| Net Fixed Assets |
33,850 |
28,934 |
24,018 |
19,103 |
14,186 |
9,271 |
| Total Assets |
$520,000 |
$592,631 |
$687,496 |
$736,382 |
$804,341 |
$892,378 |
| |
|
|
|
|
|
|
| Accounts Payable |
0 |
68,908 |
139,654 |
146,637 |
153,968 |
161,667 |
| Long Term Debt |
468,000 |
438,529 |
406,133 |
370,522 |
331,377 |
288,346 |
| Total Liabilities |
468,000 |
507,437 |
545,787 |
517,159 |
485,345 |
450,013 |
| Owner's Equity |
|
|
|
|
|
|
| Paid-in Capital |
52,000 |
52,000 |
52,000 |
52,000 |
52,000 |
52,000 |
| Retained Earnings |
0 |
33,194 |
89,709 |
167,223 |
266,996 |
390,365 |
| Total Liabilities & Equity |
$520,000 |
$592,631 |
$687,496 |
$736,382 |
$804,341 |
$892,378 |
| Cash Flow Statements |
| |
Year 1 |
Year 2 |
Year 3 |
Year 4 |
Year 5 |
| Operating Profit |
$94,266 |
$127,219 |
$156,310 |
$187,020 |
$219,438 |
| Add: Depreciation |
4,916 |
4,916 |
4,916 |
4,916 |
4,916 |
| |
99,182 |
132,135 |
161,226 |
191,936 |
224,354 |
| Working Capital Investment |
-68,908 |
-70,966 |
14,256 |
14,968 |
15,719 |
| Cash From Operations |
168,090 |
203,101 |
146,970 |
176,968 |
208,635 |
| Interest Expense |
43,199 |
40,274 |
37,059 |
33,524 |
29,639 |
| Income Taxes |
17,873 |
30,431 |
41,738 |
53,724 |
66,429 |
| Net Cash Before Debt Pmt |
107,018 |
132,396 |
68,173 |
89,720 |
112,567 |
| Debt Payment |
29,471 |
32,396 |
35,611 |
39,145 |
43,031 |
| Change in Cash |
$77,547 |
$100,000 |
$32,562 |
$50,575 |
$69,536 |
| |
|
|
|
|
|
| Cash Reconciliation |
|
|
|
|
|
| Beginning Cash |
$33,150 |
$110,697 |
$210,697 |
$243,259 |
$293,834 |
| Change in Cash |
77,547 |
100,000 |
32,562 |
50,575 |
69,536 |
| Ending Cash |
$110,697 |
$210,697 |
$243,259 |
$293,834 |
$363,370 |
Attachment:- problem-set.rar