Simulation Problem for MGMT 595 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 dont merge MonitoRobitics (MR) if they dont 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 MRs 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. PARAGONS INCOME STATEMENT (MILLIONS) YEAR Current C-1 C-2 Sales 624 580 480 COGS 430 405 348 Gross Profit 194 175 132 GSA 120 116 101 Op. Income (EBIT) 74 59 82 Interest expense 40 40 15 Taxes 10 13 0 Net Income 24 6 4 PARAGONS BALANCE SHEET (MILLIONS) Inventory 150 155 110 A/R 120 135 91 Other Current assets 42 48 39 total current assets 312 338 240 Plant property & Equipment 420 400 250 Total Assets 732 738 490 Total current liabilities 250 240 160 Long term liabilities 260 244 100 Total Liabilities 510 484 260 Stockholders Equity 222 254 230 OP. CASH FLOW net of CAPEX 18 20 10 MONITOROBOTICS: INCOME STATEMENT (Millions USD) YEAR Current Current -1 Current -2 Sales 42 18 6 COGS 22 10 4 Gross Profit 20 8 2 GSA 12 7 5 Operating Income 8 1 (3) Interest 0 0 0 Taxes 2 0 0 NI 6 1 (3) BALANCE SHEET HIGHLIGHTS (Millions USD) Inventory 6 2 1 A/R 5 2 1 Other Current assets 3 5 7 total current assets 14 9 9 Plant property & Equipment 3 2 1 Total Assets 17 11 10 Total current liabilities 4 2 1 Long term liabilities 0 0 0 Total Liabilities 4 2 1 Stockholders Equity 13 9 9 MonitoRobotics (MR) Operating Cash Flow 1 (3) (5) ========================================================== Experts Comments: The Investment banker thinks that MR will probably sell for about 2.5X current years 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 Paragons 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 Paragons 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 = (unmergedP+*1.03) + (umergedMR*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 YEARs sales NOT by some growth rate applied to last years numbers. This will help you avoid some serious math errors