After many years trying to come up with an experience retail concept, you have finally

found one. Its called The Guild. Building the Guild will cost approximately

$18,000,000. It will include a number of pieces of machinery which in total cost

$4,500,000 and training for the equipment will be an additional $4,000,000.

In order to run the business, inventory will need to be increased by $5,000,000.

The guild is expected to generate $10,000,000 million in revenue in its first year of

operation. Each year the companys revenue will grow by 9%.

The companys cost of goods sold are 23% of sales while its total operating costs are

$7,000,000.

Most companies like the Guild trade at 5 times after tax cash flow. Use this multiple to

calculate the terminal value for the company.

The company will have a tax rate of 25%.

The guild has a three part capital structure with common equity, preferred equity, and

debt. The expected return on the stock market is 8.6% and the 10-year government bond

is currently yielding 3.2%. Companies like The Guild have a beta of 1.1. No flotation is

noted.

Bonds for The Guild are currently trading at 95% of their par value, with a ten-year

maturity, and a 6% coupon, paid semiannually. A 4% flotation cost exists.

Preferred stock trades at $52 per share and pays a dividend of $1.25 per share. A 3%

flotation cost is noted.

The companys capital structure is 30% common equity, 10% preferred equity, and 60%

debt.

1. Layout all the companys cash flows.

2. Calculate the NPV, IRR, and Payback period.

3. Fill in all the shaded areas of the excel file.

4. Open the exam in Canvas and answer the questions.

5. Attach your excel file to the assignment in Canvas.

PUT ALL OF THESE ANSWERS IN AN EXCEL FILE AND HURRY. IT HAS TO BE DONE BY 9 AM.