Excel Online Structured Activity: New Stock Issue Bynum and Crumpton Inc. (B&C), a small jewelry manufacturer, has been successful and has enjoyed a positive growth trend. Now B&C is planning to go public with an issue of common stock, and it faces the problem of setting an appropriate price for the stock. The company and its investment banks believe that the proper procedure is to conduct a valuation and select several similar firms with publicly traded common stock and to make relevant comparisons. Several jewelry manufacturers are reasonably similar to B&C with respect to product mix, asset composition, and debt/equity proportions. Of these companies, Abercrombe Jewelers and Gunter Fashions are most similar. When analyzing the following data, assume that the most recent year has been reasonably "normal" in the sense that it was neither especially good nor especially bad in terms of sales, earnings, and free cash flows. Abercrombe is listed on the AMEX and Gunter on the NYSE, while B&C will be traded in the Nasdaq market. B&C Company data Shares outstanding Price per share Earnings per share Free cash flow per share Book value per share Total assets Total debt Abercrombe 5 million $32.00 $2.20 $1.63 $15.00 $110 million $35 million Gunter 10 million $52.00 $3.13 $2.54 $22.00 $270 million $50 million 500,000 NA $2.60 $2.00 $18.00 $10 million $3 million The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. a. BBC is a closely held corporation with only 500,000 shares outstanding. Free cash flows have been low and in some years negative due to B&C's recent high sales growth rates, but as its expansion phase comes to an end B&C's free cash flows should increase. B&C anticipates the following free cash flows over the next 5 years! Year 1 2 4 FCF 3 5 1,000,000 1,050,000 1,208,000 1,329,000 1,462,000 After Year 5, free cash flow growth will be stable at 7% per year. Currently, B&C has no non-operating assets, and its WACC is 12%. Using the free cash flow valuation model, estimate B&C's intrinsic value of equity and intrinsic per share price. Do not round intermediate calculations. Round your answer for the value of equity to the nearest dollar. Round your answer for the value of equity per share to the nearest cent. Value of equity Per share value of equity $ b. Calculate debt to total assets, P/E, market to book, P/FCF, and Roe for Abercrombe, Gunter, and B&C. For calculations that require a price for B&C, use the per share price you obtained with the corporate valuation model in part a. Do not round intermediate calculations, Round your answers to two decimal places. $ Gunter Abercrombe % B&C % 9 D/A P/E MarketBook ROE % % P/FCF c. Using Abercrombe's and Gunter's P/E, Market/Book, and Price/ECF ratios, calculate the range of prices for B&C's stock that would be consistent with these ratios. For example, if you multiply B&C's earnings per share by Abercrombe's P/E ratio you get a price. What range of prices do you get? Do not round intermediate calculations. Round your answers to the nearest cent. The range of prices: from 5 to $ 1 D Abercrombe 5,000,000 $32.00 $2.20 $1.63 $15,00 $110.00 $36.00 Gunter 10,000,000 S52.00 53.13 $2.54 $22.00 $270.00 $50,00 B&C 500,000 NA S260 $200 $18.00 $12.00 $3.00 7.00% 12.00% $0 New Stock 2 3 Company data 4 Shares outstanding S Price per share 6 Earnings per share 7 Free cash flow per share 8 Book value per share 9 Total assets (in milions) 10 Total debt (in millions) 11 12 Free Cash Flow Valuation Model 11 Constant growth rate afer Year 5 14 WACC 15 Non operating assets 16 17 18 B&C's expected free cash flows (FCFS) 19 Horizon value 20 FCFS 21 22 23 Value of operations 24 Non-operating assets 25 Debt 26 Intrinsic value of equity 22 28 Intrinsic price per share 29 30 Rato Calculations 31 Debt Assets (DIA) 32 Price Earings (PE) 33 Market Book (MB) 34 Return on Equity (ROE) 35 Price Free Cash Pow (PFCF 36 Year 1 $1,000,000 Year 2 $1,050,000 Year 3 $1,208,000 Year 4 Years 51.329,000 $1,462.000 Formula WNIA $1.000.000 $1,050.000 $1.208,000 51.329,000 51,462.000 Formulas WNA SO WNA MNA WNA Abercrombe Gunter B&C Abercrombie AN/A WNIA ONIA NA ANIA Formulas Gunter ENIA NNA INA WNA ONA B&C UNIA WNIA WNA WNIA WNIA B D G 50 WNA WNA NNA A 24 Non-operating assets 25 Debt 26 Intrinsic value of equity 27 28 Intrinsic price per share 29 30 Radio Calabon 31 DebtAssets (DA) 32 Price Earings (P/E) 33 MarketBook (MB) 34 Return on Equily (ROE) 35 Price Free Cash Flow (P/FCF) 36 37 Abercrombe Gunter B&C Abercrombe ANA N/A ANIN ONIA ONIA Formulas Gunter MNA WNIA WNIA NNA WNIA B&C WNA WNIA WNIA MNIA WNIA Range of B&C Pro Rased on 38 Abercrombie and Gunter Ratas: 39 Abercrombie P/E 40 Gunter PIE 41 Abercrombe MB 42 Gunter MB 43 Abercrombe P/FCF 24 Gunter P/FCF 45 46 Stock price range 47 Formulas 4B 49 Ratio 0.00 0.00 0.00 0.00 0.00 0.00 Formulas Implied Implied B&C B&C Price B&C Measure Price Per Share B&C Measure Per Share #N/A WNIA 50.00 WNA MN/A ANA $0.00 #NA #N/A UNA 50.00 WNIA ONIA ANIA