Homework 1 FINC-6320-01. Due Sunday by 11:59pm Points 10 Submitting a file upload
SEE 2 FILES BELOW (WORD AND EXCEL MODEL)
Question 1 (50 points total)
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. Longhorn’s 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
REST OF QUESTIONS IN EXCEL
HOMEWORK #1 - FINANCIAL MODEL
MODELING ASSUMPTIONS ($ in Thousands)
CapEx $1,000.0 (A/R + Inventory) / Sales 20.0%
Acct. Receivables + Inv. 750.0 (A/P + Accrued Exp.) / Sales 15.0%
Accts. Payables+Accrued Exp. 500.0
Net Working Capital 250.0 CapEx / Sales 3.5%
Total Net Investment $1,250.0 CapEx Depreciation (Yrs.) 10.0
Hint: Depreciation is calculated based
2023E Sales $5,625.0 off of the cumulative CapEx of prior years
Annual Sales Gr. % '23E-'27E 10.0%
Tax Rate 25.0%
Gross Profit Mgn. % '23E-'27E 30.0%
Terminal Value (Liq. Proceeds) @ 2027
OpEx Before Depr. as % Sales 22.5% Mult. of '27E Net PP&E+NWC 1.0x
Debt $0.0 Cost of Capital 15.0%
CAPEX, DEPRECIATION & WORKING CAPITAL ($ in Thousands)
PP&E 2022 2023E 2024E 2025E 2026E 2027E
A/R + Inv.
A/P + Exp.
Change in NWC
PRO FORMA INCOME STATEMENT ($ in Thousands)
2023E 2024E 2025E 2026E 2027E
OpEx Before Depreciation
DISCOUNTED CASH FLOW ANALYSIS ($ in Thousands)
(Investment) 2023E 2024E 2025E 2026E 2027E
Change in NWC
SENSITIVY ANALYSES ($ in Thousands)
Growth Gross Margin
20.0% 25.0% 30.0% 35.0% 40.0%
Team Names: _______________________________
Question 1 (50 points total)
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. Longhorn’s 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.
a. Attach model to Canvas submission. Instructor will check for structure/interactivity of model [10 points]
b. What is the project’s NPV? [10 points]
c. Change the annual sales growth to 15% and gross margin to 25%. What’s the new NPV? [10 points]
d. 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]
e. 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:
Average D/E Ratio
Note that the average βs above denote the average of the levered or equity βs of these firms. Also, note that the combined entity’s unlevered β is the weighted-average of the unlevered βs of its respective subsidiaries.
A) Calculate New Corp’s levered β (10 points)
B) What is its cost of equity? (10 points)
Question 3 (20 points total)
You have been approached by the owner of a successful restaurant in Dallas who is raising $275,000 of new equity to fund the investments necessary to support anticipated growth over the next few years.
The restaurant owner tells you his restaurant will generate (in yr. 1) $1,000,000 in revenues and incur operating cash expenses of $650,000. He expects revenue and expenses to grow 2% in perpetuity. He also expects capital expenditures and depreciation each to be equal to 5% of sales. Working capital is 2% of sales.
You believe that the target capital structure is debt-free. The average tax rate is 25%. A review of comparable publicly-traded companies indicate a levered beta of 2.0 with average debt/(debt+equity) of 50%. The risk-free rate is 3.0% and market risk premium is 6.0%.
A) What is the expected rate of return on equity? [10 points]
B) What percentage of the equity in the restaurant would you expect if you were to provide the entire $275,000? [10 points]
Question 4 (10 points total)
Suppose Kraft Foods’ stock has a beta of 0.50, whereas Boeing’s beta is 1.25. If the risk-free rate is 4%, and the expected return of the market is 10%, what is the expected return of an equally-weighted portfolio of Kraft Foods and Boeing stocks, according to CAPM? [10 points]