A B D E F G I 1 Bio Hawaii Corporation 2 Company Description 3 Five year old Hawaii ocean plant based pharmaceutical company 4 BioHawaii has 2 promising drugs that are in phase III of FDA approval 5 The Biopharmaceutical industry is experiencing approximately 10% annual growth worldwide. 6 BioHawaii Corp equity is 100% owned by one founder. 7 To finance growth and R&D, BioHawaii Corp is looking to raise significant capital (debt or equity) over the next few years. 8 BioHawaii's founder has hired you as an advisor for raising additional capital from VC funds and banks. 9 10 Assignment (For calculations, assume it is now Jan 1, 2021) 11 1) Determine pro-forma financials for years 2021-25. Set your own optimal Capital Raising and Dividend policies. 12 2) Derive comparable (comp) valuation metrics (P/E, EV/Sales) and financial metrics (Margins, Leverage) for BioHawaii. 13 3) Use both a multiples method and a discounted future IPO method for your valuation. 14 4) Write a consulting report (Max 500 words) to BioHawaii founder including: 15 a) Recommend if the founders should move ahead with this business opportunity and why? Growth drivers, market size, profit potential, etc. 16 b) What is your final valuation for Bio Hawaii's equity and briefly explain your valuation method. 17 c) Based on your valuation, how much of BioHawaii's ownership (equity %) do you recommend to sell in order to fund AFN? 18 d) Recommendations on current and future Financial Policy (Capital Raising, Dividends, working capital, etc). 19 e) Feel free to use visuals and charts in your writeup (visuals are not counted in 500 word limit) 20 5) Complete this assignment in your Groups 216) Due Tuesday, November 9th before class. 22 7) E-mail me (bbystrom@hawaii.edu) both your financial excel model and the consulting report writeup (in MS Word) 23 8) Make sure your excel is well formatted, print ready and a saved file (no google docs). 24 9) Use the format below to create your proforma financial statements. 25 26 Industry Comparables VC Discount Rate used for Future IPO method 27 Beta 28 P/E 29 EV / EBITDA 30 EV / Sales 31 32 [Find the industry or company comps that fit your venture business best, use separate worksheet if appropriate) 33 [In addition to valuation comps many analyst will include financial comps on margins and other ratios to help in the analysis] 34 35 Projections 2020 Actual 2021 2022 2023 2024 2025 36 Sales 37 Net Income 38 Dividend 39 EBITDA 40 Cash Ratio 41 FFO / Debt 42 Levered FCF 43 Capex [After completing your 5 yr pro-forma financials for BioHawaii, then complete this section to provide you 44 Working Capital CF with a summary of BioHawaii's projected performance which can help with financial policy recommendations.] 45 AFN 46 Leverage DI(D+E) 47 ROE 48 Growth in Earnings 49 Gross Profit Margin 50 Operating Margin 51 Net Margin Assumptions in Blue) [Determine your own financing and dividend policy to best fit the venture's operations] Year 2020 Actual 2021 2022 2023 2024 2025 Notes: Sales Growth Rate 60% 70% 120% 90% 40% 10% COGS as % of Sales 60% 60% 55% 55% 50% 50% 1) Margins improve with increased scale Fixed Operating Expenses (40) (40) (95) (150) (155) ( (165) 2) Fixed Expense rise but slower than sales Dividend Payout Rate 0% % Corp Tax Rate 0% 0% 0 0% 10% 25% 25% 25% Depr as % of Net Fixed Assets 1270 % 12% 12% 12% 12% 12% 3) Depr is tied to level of Net Fixed Assets A/R as % of Sales 25% 25% 20% 25% 25% Sales 25% nas % 25% Inventory as % of Sales 10% 10% 10% To 10% 10% 10% A/P as % of Sales 10% 10% 10% 10% 10% 10% Accrued Liab as % of Sales 5 5% 5% 5% 5% 5% 5% Net Fixed Assets as % of Sales 50% 60% 60% 45% 40% 40% 4) Fixed asset requirements fall as BioHawaii matures Short-term Bank Loan as % of Sales % 30% AFN Financing % Equity 100% AFN Financing % LT Debt 0% Interest Rate on Debt 10% 10.00% 9.00% 7.00% 7.00% 7.00% Recommended adjustments to AFN 6) If BioHawaii's cash balance is too low or too high, make adjustments in this row. Note) You may add assumptions and ratio calculations as you feel appropriate to model this venture but document changes. Pro Forma Financial Statements (in US$ millions) (Note: I/S shows expenses as negative numbers and revenues as positive numbers) Income Statement 2020 Actual 2021 2022 2023 2024 2025 Net Sales 100 COGS (60) ( Gross Profit 40 Fixed Operating Expenses (40) EBITDA 0 Depreciation (6) 6 EBIT (6) complete proforma financials for 5 years based on 2020 results] Interest (3) ) EBT (9) Taxes 0 Net Income (NI) (9) Cash Dividends Added Retained Earnings 0 (9) ) 2025 Balance Sheet Cash & Mkt Sec Accounts Rec Inventories Total Current Assets Fixed Assets Net Total Assets 2020. Actual 2021 2022 2023 2024 15 [Reference Ending Cash from CF Statement] 25 10 50 50 [Use the following font color pattern: 100 Blue = Hardcoded assumptions and historical numbers Black - Formulas 10 Green = Referenced from another worksheet) 30 5 45 0 0 0 [New Debt and Equity feeds into Old Debt and Accounts Pay ST Bank Loan Acc Liab Total Current Liab Long-Term Debt (Old) Long-Term Debt (New) 0 [New Debt and Equity feeds into Old Debt and Equity in the following year] Long-Term Debt (Old) Long-Term Debt (New) Common Stock (New) Common Stock (Old) Retained Earnings Total Liab & Equity Assets - (Liab + Equity) Check Leverage Total Debt Total Equity Debt/(Debt + Equity) 0 0 20 45 (10) 100 0.00 65 30 55 35% [Calculate AFN for 2021 through 2025] AFN Calculations + Investments in Assets - Change in Bank Loan - Free Working Cap Financing - Retained Net Income AFN per year = Cummulative AFN = 2021 2022 2023 2024 2025 Statement of CFs Net Income + Depreciation Change in AIR Change in Inv. Change in A/P Change in Acc. Liab. CF from Operations 2020 Actual (9) 6 (15) (5) 3 3 (17) Capital Expenditures Other Investments CF from Investments (20) [Add Depr Exp to the difference in Net Fixed Assets to get Capex] 0 (20) 10 Change in Bank Loan Change in Long-Term Debt Change in Common Stock Payment of Cash Dividends CF from Financing 0 20 0 30 Net Cash Flow Beginning Cash Ending Cash (7) 22 15 A B D E F G I 1 Bio Hawaii Corporation 2 Company Description 3 Five year old Hawaii ocean plant based pharmaceutical company 4 BioHawaii has 2 promising drugs that are in phase III of FDA approval 5 The Biopharmaceutical industry is experiencing approximately 10% annual growth worldwide. 6 BioHawaii Corp equity is 100% owned by one founder. 7 To finance growth and R&D, BioHawaii Corp is looking to raise significant capital (debt or equity) over the next few years. 8 BioHawaii's founder has hired you as an advisor for raising additional capital from VC funds and banks. 9 10 Assignment (For calculations, assume it is now Jan 1, 2021) 11 1) Determine pro-forma financials for years 2021-25. Set your own optimal Capital Raising and Dividend policies. 12 2) Derive comparable (comp) valuation metrics (P/E, EV/Sales) and financial metrics (Margins, Leverage) for BioHawaii. 13 3) Use both a multiples method and a discounted future IPO method for your valuation. 14 4) Write a consulting report (Max 500 words) to BioHawaii founder including: 15 a) Recommend if the founders should move ahead with this business opportunity and why? Growth drivers, market size, profit potential, etc. 16 b) What is your final valuation for Bio Hawaii's equity and briefly explain your valuation method. 17 c) Based on your valuation, how much of BioHawaii's ownership (equity %) do you recommend to sell in order to fund AFN? 18 d) Recommendations on current and future Financial Policy (Capital Raising, Dividends, working capital, etc). 19 e) Feel free to use visuals and charts in your writeup (visuals are not counted in 500 word limit) 20 5) Complete this assignment in your Groups 216) Due Tuesday, November 9th before class. 22 7) E-mail me (bbystrom@hawaii.edu) both your financial excel model and the consulting report writeup (in MS Word) 23 8) Make sure your excel is well formatted, print ready and a saved file (no google docs). 24 9) Use the format below to create your proforma financial statements. 25 26 Industry Comparables VC Discount Rate used for Future IPO method 27 Beta 28 P/E 29 EV / EBITDA 30 EV / Sales 31 32 [Find the industry or company comps that fit your venture business best, use separate worksheet if appropriate) 33 [In addition to valuation comps many analyst will include financial comps on margins and other ratios to help in the analysis] 34 35 Projections 2020 Actual 2021 2022 2023 2024 2025 36 Sales 37 Net Income 38 Dividend 39 EBITDA 40 Cash Ratio 41 FFO / Debt 42 Levered FCF 43 Capex [After completing your 5 yr pro-forma financials for BioHawaii, then complete this section to provide you 44 Working Capital CF with a summary of BioHawaii's projected performance which can help with financial policy recommendations.] 45 AFN 46 Leverage DI(D+E) 47 ROE 48 Growth in Earnings 49 Gross Profit Margin 50 Operating Margin 51 Net Margin Assumptions in Blue) [Determine your own financing and dividend policy to best fit the venture's operations] Year 2020 Actual 2021 2022 2023 2024 2025 Notes: Sales Growth Rate 60% 70% 120% 90% 40% 10% COGS as % of Sales 60% 60% 55% 55% 50% 50% 1) Margins improve with increased scale Fixed Operating Expenses (40) (40) (95) (150) (155) ( (165) 2) Fixed Expense rise but slower than sales Dividend Payout Rate 0% % Corp Tax Rate 0% 0% 0 0% 10% 25% 25% 25% Depr as % of Net Fixed Assets 1270 % 12% 12% 12% 12% 12% 3) Depr is tied to level of Net Fixed Assets A/R as % of Sales 25% 25% 20% 25% 25% Sales 25% nas % 25% Inventory as % of Sales 10% 10% 10% To 10% 10% 10% A/P as % of Sales 10% 10% 10% 10% 10% 10% Accrued Liab as % of Sales 5 5% 5% 5% 5% 5% 5% Net Fixed Assets as % of Sales 50% 60% 60% 45% 40% 40% 4) Fixed asset requirements fall as BioHawaii matures Short-term Bank Loan as % of Sales % 30% AFN Financing % Equity 100% AFN Financing % LT Debt 0% Interest Rate on Debt 10% 10.00% 9.00% 7.00% 7.00% 7.00% Recommended adjustments to AFN 6) If BioHawaii's cash balance is too low or too high, make adjustments in this row. Note) You may add assumptions and ratio calculations as you feel appropriate to model this venture but document changes. Pro Forma Financial Statements (in US$ millions) (Note: I/S shows expenses as negative numbers and revenues as positive numbers) Income Statement 2020 Actual 2021 2022 2023 2024 2025 Net Sales 100 COGS (60) ( Gross Profit 40 Fixed Operating Expenses (40) EBITDA 0 Depreciation (6) 6 EBIT (6) complete proforma financials for 5 years based on 2020 results] Interest (3) ) EBT (9) Taxes 0 Net Income (NI) (9) Cash Dividends Added Retained Earnings 0 (9) ) 2025 Balance Sheet Cash & Mkt Sec Accounts Rec Inventories Total Current Assets Fixed Assets Net Total Assets 2020. Actual 2021 2022 2023 2024 15 [Reference Ending Cash from CF Statement] 25 10 50 50 [Use the following font color pattern: 100 Blue = Hardcoded assumptions and historical numbers Black - Formulas 10 Green = Referenced from another worksheet) 30 5 45 0 0 0 [New Debt and Equity feeds into Old Debt and Accounts Pay ST Bank Loan Acc Liab Total Current Liab Long-Term Debt (Old) Long-Term Debt (New) 0 [New Debt and Equity feeds into Old Debt and Equity in the following year] Long-Term Debt (Old) Long-Term Debt (New) Common Stock (New) Common Stock (Old) Retained Earnings Total Liab & Equity Assets - (Liab + Equity) Check Leverage Total Debt Total Equity Debt/(Debt + Equity) 0 0 20 45 (10) 100 0.00 65 30 55 35% [Calculate AFN for 2021 through 2025] AFN Calculations + Investments in Assets - Change in Bank Loan - Free Working Cap Financing - Retained Net Income AFN per year = Cummulative AFN = 2021 2022 2023 2024 2025 Statement of CFs Net Income + Depreciation Change in AIR Change in Inv. Change in A/P Change in Acc. Liab. CF from Operations 2020 Actual (9) 6 (15) (5) 3 3 (17) Capital Expenditures Other Investments CF from Investments (20) [Add Depr Exp to the difference in Net Fixed Assets to get Capex] 0 (20) 10 Change in Bank Loan Change in Long-Term Debt Change in Common Stock Payment of Cash Dividends CF from Financing 0 20 0 30 Net Cash Flow Beginning Cash Ending Cash (7) 22 15