Please fill in the cells with the information provided and show excel formulas used.
ITX Corp. (ITX), a publicly-traded consumer products company is considering the acquisition of a competitor, House Stuff Inc. (HOUSE), that has a different product line. The corporate development department of ITX determined that an acquisition of HOUSE would increase product offerings and sales opportunities. As such, ITX approached HOUSE with its interest in purchasing HOUSE. HOUSE hired an investment banker to advise on the potential sale to ITX. HOUSE's investment banker has determined that an asking price of $50.0million for 100% of HOUSE's outstanding shares is a reasonable value. The investment banker for HOUSE met with Ms. Brenda Camton, the CFO of ITX Ms. Camton believes that ITX could improve the cash flow of HOUSE through increased product offerings and cross-selling and also reducing cost of goods sold and administrative expenses. Overall Ms. Camton sees both topline and bottomline growth for ITX as a result of the acquisition of HOUSE and it should increase shareholder value. Ms. Camton met with her FP\&A department to determine a value for HOUSE and whether $50.0 million was a reasonable price for HOUSE. The FP\&A team was given these parameters for a 3-year forecast and to value HOUSE and its equity for Ms. Camton's negotiations with HOUSE. 1. Sales growth: 15% in 2023,15% in 2024 and 12.5% in 2025 2. The long-term growth rate after the projection period is 4.5%.605 3. Cost of Goods Sold as a % of sales of 60.0% in 2023,59.5% in 25024 and 59.0% in 2025 . 4. Selling Expense as a % of sales of 9.5% in 2023,9.5% in 2024 an 25d9.5% in 2025 . 5. Administrative Expense as a % of sales of 5.5% in 2023,5.5% in 2024 and 5.5% in 2025. 6. Depreciation expense will be 4.0% of sales over the projection period 7. Accounts receivable are projected to be 30 DSO of the respective year's sales over the projection period. 8. Inventory tumover of 5.8x based on annual COGS over the projection period. 9. Accounts payable are projected to be 32 days of the respective year's COGS over the projection period. 10. Accrued Expense as a \% of sales of 5.0% in 2023,5.0% in 2024 and 5.0% in 2025 . 11. CAPX will be 10.5% of sales over the projection period. 12. Tax rate is 25% for both companies. 13. ITX's current capital structure consists of: a. $700 million face value of debt currently selling at 1.09 of face value. It has a stated interest rate of 7.0% and a market yield of 6.0%. b. 45 million shares are outstanding currently trading at $36.00 per share. c. ITX's beta is 1.05, the current 20 -year Treasury Bond yield is 4.0%. The equity risk premium (ERP) is 7.0%. Note: Insert line items if needed to show inputs for calculations in order to get credit for applicable calculations
