Please answer the following question using Excel spreadsheet. Happy, Inc. was founded $9$ years ago by siblings Brandon and Rachael Happy. The company manufactures and installs commercial heating, ventilation, and cooling $($HVAC$)$ units. Happy, Inc. experienced rapid growth because of a proprietary technology that increases the energy efficiency of its units. The company is equally owned by Brandon and Rachael.The original partnership agreement between the siblings gave each $50,000$ shares of stock. In the event either wished to sell stock, the shares first had to be offered to the other at a discounted price. Although neither sibling wants to sell, they had decided they should value their holdings in the company. To get started, they have gathered the following information about their main competitors:

Earnings per share $($EPS$)$ Dividends per share $($DPS$)$ Stock price Return on Equity $($ROE$)$ Required Return

Competitor A $ $1\mathrm{.}30$ $ $0\mathrm{.}16$ $ $25\mathrm{.}348\mathrm{.}50\%10\mathrm{.}00\%$

Competitor B $ $1\mathrm{.}95$ $ $0\mathrm{.}23$ $ $29\mathrm{.}8510\mathrm{.}50\%13\mathrm{.}00\%$

Competitor C $ $(0\mathrm{.}37)$ $ $0\mathrm{.}14$ $ $22\mathrm{.}139\mathrm{.}78\%12\mathrm{.}00\%$

Industry Average $ $0\mathrm{.}96$ $ $0\mathrm{.}18$ $ $25\mathrm{.}779\mathrm{.}59\%11\mathrm{.}67\%$

Competitor C$\text{'}$s negative earnings per share were the result of an accounting write$-$off last year. Without the write$-$off, earnings per share for the company would have been $$1\mathrm{.}10.$

Last year, Happy, Inc. had an EPS of $$3\mathrm{.}15$ and paid a dividend to Brandon and Rachael of $$45,000$ each. The company also had a return on equity of $17$ percent. The siblings believe that $14$ percent is an appropriate required return for the company.

Question $1$

Assuming the company continues its current growth rate, what is the value per share of company's stock?

Question $2$

To verify their calculations, Brandon and Rachael hired ABC Consultants, an equity analysts that covers the HVAC industry. ABC has examined the company's financial statements as well as those of its competitors. Although Happy, Inc. currently has technological advantage, their research indicates that other companies are investigating methods to improve efficiency. Given this, ABC believes that the company's technological advantage will last only for the next five years. After that period, the company's growth will likely slow to the industry growth average. Additionally, ABC believes that the required return used by the company is too high. ABC believes the industry average required return is more approrpriate. Under this growth rate assumption, what is your estimate of the stock price?

Complete the boxes in yellow highlighted area to answer the two questions.

Here are some basic facts to get you started:

Shares owned by each sibling $50,000$

Happy EPS $ $3\mathrm{.}15$

Dividend to each sibling $ $45,000$

Happy ROE $17\%$

Happy required return $14\%$

The following is provided to help you complete this assignment.

Complete the following output areas to answer Q$1.$ Do not hard code values. Show your work by using formulas in the cells.

Total dividends

Total earnings hint: EPS x shares outstanding

Retention ratio this is the percentage of earnings not paid out in dividends

Growth rate hint: retention ratio x ROE

Current dividend per share

Dividend per share next year

Complete the following output areas to answer Q$2.$ Do not hard code values. Show your work by using formulas in the cells.

Question $1$ Answer:

Value per share Use the infomration above to answer Question $1$

Industry EPS hint: for competitor C$,$ use the expert EPS w$/$o write$-$off

Industry payout ratio

Industry retention ratio

Industry growth rate

Year Dividends$/$share hint: use the following growth rates to fill in the shaded area

$1$ Company growth rate

$2$ Company growth rate

$3$ Company growth rate

$4$ Company growth rate

$5$ Company growth rate

$6$ INDUSTRY growth rate

Stock value in Year $5$

Question $1$b Answer:

Stock price today Use the information above to answer Question $2$