SouthWest Capital LLP (SWC) is considering the acquisition of CrissCross Inc. (CCI), a Texas-based manufacturer of widgets.

1. Prepare an integrated forecast of CCIs pro-forma financial statements for the next five years.

1. Use the Excel template and fill out the grey shaded cells (input cells are blue). Start with the income statement and then the balance sheet and the cash flow statement.
2. Net interest expense should reflect net debt, i.e. all interest bearing liabilities less cash balances, if any.
3. Add net income less dividends, if any, to retained earnings in the balance sheet.
4. Use asset and liability plugs to ensure that the balance sheet actually balances.
5. Since this set-up involves circular references, change Excels preferences to allow iterations.
6. Use a circuit breaker for your plugs to stop the propagation of errors and to reset the calculations (reset = 1, calculate = 0).
7. Confirm that the cash flow calculation corresponds to the change in the balance sheets cash position. All checks must be zero.

1. Determine the free cash flows generated by CCI over the planning period.

Assumptions:

1. In year 1, CCI produces and sells 1 million widgets at a price of $6 per unit. Growth in annual volume of 10% and unit price increases of 2%.
2. Cost of goods sold of $3 per unit, growing at 2% per year. Selling, general and administrative expenses of $1,000,000 per year, growing at 5% per year.
3. No amortization. Interest rate of 5%. Income tax rate of 21%. No dividends.
4. At the end of year 0, CCI has no cash but accounts receivables of $950,000, inventory of $2,850,000, and accounts payables of $450,000. In the following years, receivables, inventories, and payables are at 60, 180, and 30 days sales, respectively (1 year = 360 days).
5. Fair value of land $600,000, buildings $2,000,000 (depreciated over 39 years MACRS), and plant, property & equipment $3,500,000 (depreciated over 10 years MACRS). Existing goodwill $100,000. Buildings and PP&E are new and put into service at the end of year 0.
6. Long-term debt of $9,000,000 and equity capital of $550,000.
7. Fixed CAPEX of $500,000 per year (depreciated over 10 years MACRS).

Appendix Table 1: MACRS GDS Property Classes Type Property Class Description Special handling devices for food and beverage manufacture. Special tools for the manufacture of finished plastic products, fabricated metal 3-year property products, and motor vehicles Property with ADR class life of 4 vears or less Information Systems; Computers / Peripherals Aircraft and parts (of non-air-transport companies) Computers 5-year property Petroleum drilling equipment Property with ADR class life of more than 4 years and less than 10 years Certain geothermal, solar, and wind energy properties. Personal All other property not assigned to another class Property 7-year property Office furniture, fixtures, and equipment Property with ADR class life of more than 10 vears and less than 16 vears Assets used in petroleum refining and certain food products 10-year property Vessels and water transportation equipment Property with ADR class life of 16 years or more and less than 20 years Telephone distribution plants 15-year property Municipal sewage treatment plants Property with ADR class life of 20 vears or more and less than 25 years Municipal sewers 20-year property Property with ADR class life of 25 years or more Real 27.5-year property Residential rental property (does not include hotels and motels) Property 39-vear property Non-residential real property 14.29 24.49 6.177 Table 2: MACRS Percentage for Property Class Recovery Year 3-Year 5-Year 7-Year 10-Year 15-Year 20-Year 1 33.33 20.00 10.00 5.00 3.750 2 44.45 32.00 18.00 9.50 7.219 3 14.81 19.20 17.49 14.40 8.55 6.677 4 7.41 11.52 12.49 11.52 7.70 5 11.52 8.93 9.22 6.93 5.713 6 6 5.76 8.92 7.37 6.23 5.285 7 8.93 6.55 5.90 4.888 8 4.46 6.55 5.90 3.2 4.522 9 6.56 3 5.91 4.462 10 6.55 3.70 5.90 4.461 710 11 3.28 5.91 4.462 12 5.90 4.461 13 5.91 4.462 14 5.90 4.461 15 5.91 4.462 16 2.95 4.461 17 4.462 18 4.461 19 4.462 20 4.461 21 2.231 The 3-, 5., 7-, and 10-year classes use 200% and the 15- and 20-year classes use 150% declining balance depreciation. All classes convert to straight-line depreciation in the optimal year, shown with an asterisk (*). A half-year depreciation is allowed in the first and last recovery years. If more than 40% of the year's MACRS property is placed in service in the last three months, then a mid-quarter convention must be used with depreciation tables that are not shown here. Page 2 Month 1 Month 2 2.247 Table 3: MACRS Percentage for Real Property Recovery Year Year 1 Year 2-39 Year 40 2.461 2.564 0.107 2.564 0.321 Month 3 2.033 2.564 0.535 Month 4 1.819 2.564 0.749 Month 5 1.605 2.564 0.963 Meth Month 6 1.391 2.564 1.177 Month 7 1 201 1.177 2.564 1.391 Month 8 0.963 2.564 1.605 Month 9 0.749 2.564 1.819 Month 10 0.535 2.564 2.033 Month 11 0.321 2.564 2.247 Month 12 0.107 2.564 2.461 The useful life is 39 years for nonresidential real property. Depreciation is straight line using the mid-month convention. Thua property placed in service in January would be allowed 11 months depreciation for recovery Year 1. Appendix Table 1: MACRS GDS Property Classes Type Property Class Description Special handling devices for food and beverage manufacture. Special tools for the manufacture of finished plastic products, fabricated metal 3-year property products, and motor vehicles Property with ADR class life of 4 vears or less Information Systems; Computers / Peripherals Aircraft and parts (of non-air-transport companies) Computers 5-year property Petroleum drilling equipment Property with ADR class life of more than 4 years and less than 10 years Certain geothermal, solar, and wind energy properties. Personal All other property not assigned to another class Property 7-year property Office furniture, fixtures, and equipment Property with ADR class life of more than 10 vears and less than 16 vears Assets used in petroleum refining and certain food products 10-year property Vessels and water transportation equipment Property with ADR class life of 16 years or more and less than 20 years Telephone distribution plants 15-year property Municipal sewage treatment plants Property with ADR class life of 20 vears or more and less than 25 years Municipal sewers 20-year property Property with ADR class life of 25 years or more Real 27.5-year property Residential rental property (does not include hotels and motels) Property 39-vear property Non-residential real property 14.29 24.49 6.177 Table 2: MACRS Percentage for Property Class Recovery Year 3-Year 5-Year 7-Year 10-Year 15-Year 20-Year 1 33.33 20.00 10.00 5.00 3.750 2 44.45 32.00 18.00 9.50 7.219 3 14.81 19.20 17.49 14.40 8.55 6.677 4 7.41 11.52 12.49 11.52 7.70 5 11.52 8.93 9.22 6.93 5.713 6 6 5.76 8.92 7.37 6.23 5.285 7 8.93 6.55 5.90 4.888 8 4.46 6.55 5.90 3.2 4.522 9 6.56 3 5.91 4.462 10 6.55 3.70 5.90 4.461 710 11 3.28 5.91 4.462 12 5.90 4.461 13 5.91 4.462 14 5.90 4.461 15 5.91 4.462 16 2.95 4.461 17 4.462 18 4.461 19 4.462 20 4.461 21 2.231 The 3-, 5., 7-, and 10-year classes use 200% and the 15- and 20-year classes use 150% declining balance depreciation. All classes convert to straight-line depreciation in the optimal year, shown with an asterisk (*). A half-year depreciation is allowed in the first and last recovery years. If more than 40% of the year's MACRS property is placed in service in the last three months, then a mid-quarter convention must be used with depreciation tables that are not shown here. Page 2 Month 1 Month 2 2.247 Table 3: MACRS Percentage for Real Property Recovery Year Year 1 Year 2-39 Year 40 2.461 2.564 0.107 2.564 0.321 Month 3 2.033 2.564 0.535 Month 4 1.819 2.564 0.749 Month 5 1.605 2.564 0.963 Meth Month 6 1.391 2.564 1.177 Month 7 1 201 1.177 2.564 1.391 Month 8 0.963 2.564 1.605 Month 9 0.749 2.564 1.819 Month 10 0.535 2.564 2.033 Month 11 0.321 2.564 2.247 Month 12 0.107 2.564 2.461 The useful life is 39 years for nonresidential real property. Depreciation is straight line using the mid-month convention. Thua property placed in service in January would be allowed 11 months depreciation for recovery Year 1