Please use the provided excel template to solve the question and please show work / formula of working cell for a thumb up.

Please use the provided excel template to solve the question and please show work / formula of working cell for a thumb up.

Please use the provided excel template to solve the question and please show work / formula of working cell for a thumb up.

Please use the provided excel template to solve the question and please show work / formula of working cell for a thumb up.

Please use the provided excel template to solve the question and please show work / formula of working cell for a thumb up.

Please use the provided excel template to solve the question and please show work / formula of working cell for a thumb up.

10-4 VC VALUATION AND DEAL STRUCTURING Chariot.com needs $500,000 in venture capital to bring a new Internet messaging service to market. The firm's management has approached Route 128 Ventures, a venture capital firm located in the high-tech startup mecca known as Route 128 in Boston, Massachusetts, which has expressed an interest in the investment opportunity. Chariot.com's management made the following EBITDA forecasts for the firm, spanning the next five years: Year 1 2 EBITDA -$175,000 75,000 300,000 650,000 1,050,000 3 4 5 Route 128 Ventures believes that the firm will sell for six times EBITDA in the fifth year of its operations and that the firm will have $1.2 million in debt at that time, including $1 million in interest-bearing debt. Finally, Chariot.com's management antici- pates having a $200,000 cash balance in five years. The venture capitalist is considering three ways of structuring the financing 1. Straight common stock, where the investor requires an IRR of 45%. 2. Convertible debt paying 10% interest. Given the change from common stock to debt, the investor would lower the required IRR to 35%. 3. Redeemable preferred stock with an 8% dividend rate, plus warrants entitling the VC to purchase 40% of the value of the firm's equity for $100,000 in five years. In addition to the share of the firm's equity, the holder of the redeemable preferred shares will receive 8% dividends for each of the next five years, plus the face value of the preferred stock in year 5. TERMINOLOGY A convertible security (debt or preferred stock) is replaced with common stock when it is converted. The principal is not repaid. In contrast, the face value of a security with warrants is repaid, and the investor has the right to receive common stock shares by remitting the warrants. Redeemable preferred stock is typically straight preferred with no conversion privi- leges. The preferred always carries a negotiated term to maturity, specifying when it must be redeemed by the company often, the sooner of a public offering or five to eight years). The preferred shareholders typically receive a small dividend (sometimes none), plus the face amount of the preferred issue at redemption, plus a share of the value of the firm in the form of common stock or warrants. a. Based on the offering terms for the first alternative (common stock), what fraction of the firm's shares will it have to give up to get the requisite financing? b. If the convertible debt alternative is chosen, what fraction of the firm's ownership must be given up? What rate of return will the firm have to pay for the new funds if the redeemable preferred stock alternative is chosen? d. Which alternative would you prefer if you were the management of Chariot.com? Why? Given $ $ Capital needed Projected EBITDA in year 5 Exit year EBITDA sales multiple in year 5 Interest bearing debt in year 5 Total debt in years Cash in year 5 500,000 1,050,000 5 6.00 times 1,000,000 1,200,000 200,000 Solution Legend - Value given in problem - Formula/Calculation Analysis required - Qualitative analysis or Short answer required - Goal Seek or Solver cell - Crystal Ball Input - Crystal Ball Output = $ $ $ Analysis of financing structure #1--Straight Common Stock VC's required rate of return 45% Required $ return to VC Firm Enterprise value of firm in year 5 Equity value in year 5 Part a. Alt #1. Required ownership"share" " Year 1 2 3 5 Analysis of financing structure #2-Convertible Debt VC's required rate of return 35% % Coupon rate on debt 10% Required Syear 5 return to VC Firm Enterprise value of firm in year 5 Equity value in year 5 Part b. Alt #2. Required ownership"share" 0 (500,000) Cash to Conv Debt S Terminal cash flow Use Goal Seek IRR (Conv Debt) Analysis of financing structure #3--Redeemable Preferred Plus Equity VC's required ownership share 40% Dividend rate on preferred stock 8% Required Syear 5 return to VC Firm Exercise price of warrants Part c. Alt #3. Required rate of return Use ERATE...) Alternate solution procedure for Alternative #3 Given Estimated Equity value in Year 5 VCs Share of Equity in Year 5 Note: With redeemable preferred stock the value of the firm's equity is reduced by the repayment of the face value of the preferred stock when it is redeemed 40% Solution Year 1 2 3 5 0 (500,000) $ $ Cash Investment Dividends Redemption Value Share of Equity Value minus warrant price Total Cash Flows 500,000 IRR--Preferred Investor Return Part d