Longhorn Enterprises is a private start-up based in Austin, TX that will make high-end leather bags. It wants to launch its products in the U.S. and, as such, needs to build a manufacturing facility in New Mexico. Goal is to have plant for 5 years and then liquidate it in 2027 at 1.0x the 2027 ending balance of net PP&E + working capital. Longhorns investment will be $1,500K in capital expenditures plus $250K in net working capital which is comprised of ($1,000K) in accounts receivables (A/R) and inventories minus $750K in accounts payables (A/P) and accrued expenses.

The attached Excel spreadsheet shows all other required assumptions.

1. Attach model to Canvas submission. Instructor will check for structure/interactivity of model [10 points]

2. What is the projects NPV? [10 points]

3. Change the annual sales growth to 15% and gross margin to 25%. Whats the new NPV? [10 points]

4. Conduct a Goal Seek break-even analysis. What is the break-even gross margin required for this project to have a positive NPV? [10 points]

5. Conduct a 2x2 sensitivity analysis of sales growth (5%-15%) and gross margin (20%-40%). What conclusion do you make about this business case? [10 points]

Question 2 (20 points total)

NewCorp is a corporation of cosmetics and medical subsidiaries. The cosmetics subsidiary is worth $20M while the medical subsidiary is worth $30M. The firm has a debt-to-capitalization ratio of 50%. The tax rate for all firms is assumed to be 30%. The risk-free rate is 7% and the market risk premium is 6%.

The following information has been obtained for firms with comparable systematic risk:

Comparable Firms	Average	Average D/E Ratio
Cosmetics	0.9	20%
Medical	1.2	60%

Note that the average s above denote the average of the levered or equity s of these firms. Also, note that the combined entitys unlevered is the weighted-average of the unlevered s of its respective subsidiaries.

1. Calculate New Corps levered (10 points)

1 2 4 5 6 T 8 0 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 STUDENT NAMES: MODELING ASSUMPTIONS ($ in Thousands) Initial Investment Cap Ex Acct. Receivables + Inv. Accts. Payables+Accrued Exp. Net Working Capital Total Net Investment 2023E Sales Annual Sales Gr. % '23E-'27E Gross Profit Mgn. % '23E-'27E OpEx Before Depr. as % Sales Debt Depreciation Ending PP&E Cumul. CapEx LONGHORN BAGS HOMEWORK #1 - FINANCIAL MODEL WORKING CAPITAL A/R + Inv. A/P + Exp. Net WC Change in NWC $1,000.0 750.0 500.0 250.0 $1,250.0 $5,625.0 10.0% 30.0% CAPEX, DEPRECIATION & WORKING CAPITAL ($ in Thousands) PP&E 2022 2023E Beg. PP&E CapEx 22.5% $0.0 2024E (A/R + Inventory) / Sales (A/P + Accrued Exp.) / Sales CapEx / Sales CapEx Depreciation (Yrs.) Hint: Depreciation is calculated based off of the cumulative CapEx of prior years Tax Rate Terminal Value (Liq. Proceeds) @ 2027 Mult. of '27E Net PP&E+NWC Cost of Capital 2025E 2026E 20.0% 15.0% 3.5% 10.0 25.0% 2027E 1.0x 15.0% 1 2 4 5 6 T 8 0 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 STUDENT NAMES: MODELING ASSUMPTIONS ($ in Thousands) Initial Investment Cap Ex Acct. Receivables + Inv. Accts. Payables+Accrued Exp. Net Working Capital Total Net Investment 2023E Sales Annual Sales Gr. % '23E-'27E Gross Profit Mgn. % '23E-'27E OpEx Before Depr. as % Sales Debt Depreciation Ending PP&E Cumul. CapEx LONGHORN BAGS HOMEWORK #1 - FINANCIAL MODEL WORKING CAPITAL A/R + Inv. A/P + Exp. Net WC Change in NWC $1,000.0 750.0 500.0 250.0 $1,250.0 $5,625.0 10.0% 30.0% CAPEX, DEPRECIATION & WORKING CAPITAL ($ in Thousands) PP&E 2022 2023E Beg. PP&E CapEx 22.5% $0.0 2024E (A/R + Inventory) / Sales (A/P + Accrued Exp.) / Sales CapEx / Sales CapEx Depreciation (Yrs.) Hint: Depreciation is calculated based off of the cumulative CapEx of prior years Tax Rate Terminal Value (Liq. Proceeds) @ 2027 Mult. of '27E Net PP&E+NWC Cost of Capital 2025E 2026E 20.0% 15.0% 3.5% 10.0 25.0% 2027E 1.0x 15.0%