Extrapolation of the Growing for Broke Case (HBS Publishing) Should Paragon Acquire MonitoRobotics? ASSIGNMENT: the deliverable is a series of three spreadsheets (3) and an explanation (justification) of your assumptions in each. The spreadsheets will show 5-year EPS projections for Paragon if they don't merge MonitoRobitics (MR) if they don't merge Paragon if it acquires MR Based on your analysis, explain whether you would make the acquisition. Expect to be required to defend your analysis individually and orally in class in about a 5 minute session. Using the data below and any observations you think would warrant a change in gross or operating margins, construct a spreadsheet that projects the income statement for five years for both companies assuming they do not merge, and one spreadsheet showing the merger. As a small company scales-up, expansion of gross margin is expected and SGA as a percent of sales normally decreases. Notice these trends in MR's income statement. Determine a purchase price for MonitoRobotics (MR) based on data given, and show how you would pay for it. You MUST, use debt and equity but not cash shown on the balance sheet for the purchase. To the extent you use equity, be sure to show how the issue of incremental shares will impact earnings per share. Likewise, if you use debt, be sure to show the impact of debt (additional interest) on the projected income statement. You will want to turn in the spreadsheets and an explanation of whether you would pursue the acquisition further. You will need to build in assumptions (about growth rates of revenues, purchase price, margins, etc.). Be sure to state them explicitly AND THE RATIONALE behind them. In many ways, the most critical aspect of modeling is being able to support your assumptions. Note: In year C-1, (current year minus one year) Paragon closed an acquisition of Dover Machine Tools to expand their product line, thus accounting for the significant increase in revenues and related expenses over year C-2. "Current" year means the year just completed. PARAGON'S INCOME STATEMENT (MILLIONS) YEAR Sales COGS Gross Profit GSA Op. Income (EBIT) Interest expense Taxes Net Income Current 624 430 194 120 74 40 10 24 C-1 580 405 175 116 59 40 13 6 C-2 480 348 132 101 82 15 4 PARAGONS BALANCE SHEET (MILLIONS) Inventory A/R Other Current assets total current assets Plant property & Equipment Total Assets Total current liabilities Long term liabilities Total Liabilities 150 120 42 312 420 510 Stockholders Equity 222 OP. CASH FLOW net of CAPEX 18 YEAR Sales COGS Gross Profit GSA 732 250 260 Operating Income Interest Taxes NI MONITOROBOTICS: INCOME STATEMENT (Millions USD) Current Current -1 42 22 20 12 8 ON 155 135 48 338 400 6 738 240 244 484 254 20 18 10 8 7 1 0 DOH 1 110 91 39 240 250 490 160 100 260 230 10 Current -2 6 4 2 5 (3) 0 (3) BALANCE SHEET HIGHLIGHTS (Millions USD) Inventory A/R Other Current assets total current assets Plant property & Equipment Total Assets Total current liabilities Long term liabilities Total Liabilities Stockholders Equity MonitoRobotics (MR) Operating Cash Flow 6 5 3 14 3 17 4 0 4 13 1 2 NON NONO 2 5 9 2 11 2 0 2 9 (3) 1 1 7 9 1 10 1 0 1 9 (5) Experts Comments: The Investment banker thinks that MR will probably sell for about 2.5X "current" year's sales, but this number could be as high as 3.5 X sales because another company is interested. Paragon could borrow funds at 8% for the purchase. Paragon currently has 40 million shares outstanding, and their stock is trading at $6 per share. The shares have ranged between $5.25 and $7.75 over the past 6 months. Analysts are impressed with MRs revenue growth and with the fact that they have broken even on an operating cash flow basis. However, there is something of a divided view about where the revenue growth of the industry will go from here. The range of annualized growth for MR industry over the next 5 years ranges from 10 to 20%. The engineers of Paragon and MR held a week off-site to develop a realistic sense of costs and timetable to integrate MRs Software with Paragon's software. They estimate that it would cost $5 million of research and development and 12 months to bring a product to market. Interest charges at Paragon run $40m/yr. The loan does not amortize, so the interest charge remains constant for the next 5 years unless Paragon buys MR. For taxes, use 30% of net income before taxes. Assume any differences in the tax rate in prior years result from tax loss carryforwards. After conducting a market research study with a consultant, Paragon believed that without a merger, they should expect to grow revenues at the industry-wide growth rate of about 2% / year. After the development year, the merger would increase sales of Paragon's products by about 3% above any growth expected in the absence of a merger. The gross margin would remain at about the current levels. However, launching the software services division would require a budget of $4 million per year of additional GS&A. The study also predicted that the MR software business would grow by an incremental (above what it would grow without the merger) 5% per year if it was purchased by Paragon. Gross margin and operating margin should both improve at MR as it achieves economies of scale. Helpful EXCEL Hints: Merged sales = (unmerged P+*1.03) + (umerged MR*1.05) Be sure to capture Unmerged sales cells by formula from the respective spreadsheets. Merged COGS = (merged salesPara * COGS % : PARA) + (merged salesMR* COGS%:MR) Merged SGA = (merged salesPara * SGA%: PARA) + (merged salesMR * SGA%:MR) Note that all EXPENSE ITEMS above are derived as a percent of THAT YEAR's sales.... NOT by some growth rate applied to last year's numbers. This will help you avoid some serious math errors. Extrapolation of the Growing for Broke Case (HBS Publishing) Should Paragon Acquire MonitoRobotics? ASSIGNMENT: the deliverable is a series of three spreadsheets (3) and an explanation (justification) of your assumptions in each. The spreadsheets will show 5-year EPS projections for Paragon if they don't merge MonitoRobitics (MR) if they don't merge Paragon if it acquires MR Based on your analysis, explain whether you would make the acquisition. Expect to be required to defend your analysis individually and orally in class in about a 5 minute session. Using the data below and any observations you think would warrant a change in gross or operating margins, construct a spreadsheet that projects the income statement for five years for both companies assuming they do not merge, and one spreadsheet showing the merger. As a small company scales-up, expansion of gross margin is expected and SGA as a percent of sales normally decreases. Notice these trends in MR's income statement. Determine a purchase price for MonitoRobotics (MR) based on data given, and show how you would pay for it. You MUST, use debt and equity but not cash shown on the balance sheet for the purchase. To the extent you use equity, be sure to show how the issue of incremental shares will impact earnings per share. Likewise, if you use debt, be sure to show the impact of debt (additional interest) on the projected income statement. You will want to turn in the spreadsheets and an explanation of whether you would pursue the acquisition further. You will need to build in assumptions (about growth rates of revenues, purchase price, margins, etc.). Be sure to state them explicitly AND THE RATIONALE behind them. In many ways, the most critical aspect of modeling is being able to support your assumptions. Note: In year C-1, (current year minus one year) Paragon closed an acquisition of Dover Machine Tools to expand their product line, thus accounting for the significant increase in revenues and related expenses over year C-2. "Current" year means the year just completed. PARAGON'S INCOME STATEMENT (MILLIONS) YEAR Sales COGS Gross Profit GSA Op. Income (EBIT) Interest expense Taxes Net Income Current 624 430 194 120 74 40 10 24 C-1 580 405 175 116 59 40 13 6 C-2 480 348 132 101 82 15 4 PARAGONS BALANCE SHEET (MILLIONS) Inventory A/R Other Current assets total current assets Plant property & Equipment Total Assets Total current liabilities Long term liabilities Total Liabilities 150 120 42 312 420 510 Stockholders Equity 222 OP. CASH FLOW net of CAPEX 18 YEAR Sales COGS Gross Profit GSA 732 250 260 Operating Income Interest Taxes NI MONITOROBOTICS: INCOME STATEMENT (Millions USD) Current Current -1 42 22 20 12 8 ON 155 135 48 338 400 6 738 240 244 484 254 20 18 10 8 7 1 0 DOH 1 110 91 39 240 250 490 160 100 260 230 10 Current -2 6 4 2 5 (3) 0 (3) BALANCE SHEET HIGHLIGHTS (Millions USD) Inventory A/R Other Current assets total current assets Plant property & Equipment Total Assets Total current liabilities Long term liabilities Total Liabilities Stockholders Equity MonitoRobotics (MR) Operating Cash Flow 6 5 3 14 3 17 4 0 4 13 1 2 NON NONO 2 5 9 2 11 2 0 2 9 (3) 1 1 7 9 1 10 1 0 1 9 (5) Experts Comments: The Investment banker thinks that MR will probably sell for about 2.5X "current" year's sales, but this number could be as high as 3.5 X sales because another company is interested. Paragon could borrow funds at 8% for the purchase. Paragon currently has 40 million shares outstanding, and their stock is trading at $6 per share. The shares have ranged between $5.25 and $7.75 over the past 6 months. Analysts are impressed with MRs revenue growth and with the fact that they have broken even on an operating cash flow basis. However, there is something of a divided view about where the revenue growth of the industry will go from here. The range of annualized growth for MR industry over the next 5 years ranges from 10 to 20%. The engineers of Paragon and MR held a week off-site to develop a realistic sense of costs and timetable to integrate MRs Software with Paragon's software. They estimate that it would cost $5 million of research and development and 12 months to bring a product to market. Interest charges at Paragon run $40m/yr. The loan does not amortize, so the interest charge remains constant for the next 5 years unless Paragon buys MR. For taxes, use 30% of net income before taxes. Assume any differences in the tax rate in prior years result from tax loss carryforwards. After conducting a market research study with a consultant, Paragon believed that without a merger, they should expect to grow revenues at the industry-wide growth rate of about 2% / year. After the development year, the merger would increase sales of Paragon's products by about 3% above any growth expected in the absence of a merger. The gross margin would remain at about the current levels. However, launching the software services division would require a budget of $4 million per year of additional GS&A. The study also predicted that the MR software business would grow by an incremental (above what it would grow without the merger) 5% per year if it was purchased by Paragon. Gross margin and operating margin should both improve at MR as it achieves economies of scale. Helpful EXCEL Hints: Merged sales = (unmerged P+*1.03) + (umerged MR*1.05) Be sure to capture Unmerged sales cells by formula from the respective spreadsheets. Merged COGS = (merged salesPara * COGS % : PARA) + (merged salesMR* COGS%:MR) Merged SGA = (merged salesPara * SGA%: PARA) + (merged salesMR * SGA%:MR) Note that all EXPENSE ITEMS above are derived as a percent of THAT YEAR's sales.... NOT by some growth rate applied to last year's numbers. This will help you avoid some serious math errors.