Please post the answer with detailed excel sheet and show the formulas used.

Q2. Joe's house cleaning is a company that provide household junk removal services. The owner of the company thinks they can cross sell power washing (that is, cleaning the outside of a house and driveway and walkway by waterpower-blasting using a machine with a compressor and hose) services to their customers. The initial cost of this venture would include installing an automated power washing machine, purchasing cleaning equipment and hiring a field operations manager (i.e., expenditures on fixed costs). Costs that will vary power wash include cleaning supplies (rags, dusters, wipes, solvents), city water usage, and hourly wages for workers. Three service packages are being considered. Joe is interested in knowing the following: The break-even volume based on the number of washes and sales dollars The sensitives of the break-even volumes to changes in one of the decision variables The sensitives of the break-even volumes to changes in two of the decision variables The data below is related to houses where they perform junk removal, which is 200 houses daily. The input variables use base case assumptions and include information about assumed costs, prices, and sales mix. The expected fixed costs every year are as follows: Power wash machine: $20,000; 5-year life, no salvage value ; $4,000 straight-line depreciation Car wash cleaning equipment: $1,000 for replacement parts for hoses, sprayers, and brushes Operations manager salary: $40,000, including all employee benefits TOTAL ANNUAL FIXED COST = $40,000 The variable costs, per car wash, include the following: Cleaning supplies: $2.00 for cleaning solvents, disposable rags, etc. Municipal water use: $1.00, based on the annual water bill divided by 6,000 homes washed Hourly wage rate: $10.00 per hour, or $2.50 per wash, where for 1 worker requires 15 minutes to complete a wash (Note that the labor cost per wash for the Economy package is $2.50; this cost increases based on the time required to provide additional washing and cleaning services, such as those for the basic and deluxe offerings.) UNIT VARIABLE COSTS: Economy = $5.00; Basic = $9.50; Deluxe = $13. The sales price per wash will depend on the level of service desired. Joe is thinking about offering three "packages " for customers: Economy wash at $15.00 per wash Basic wash at $25.00 per wash Deluxe wash at $40.00 per wash It is expected that 65% of all sales will be for the economy package ; 20 % for the basic package and 15 % for the deluxe treatment. Required: Prepare a financial model in Excel to evaluate and model the profitability of this business. | Q2. Joe's house cleaning is a company that provide household junk removal services. The owner of the company thinks they can cross sell power washing (that is, cleaning the outside of a house and driveway and walkway by waterpower-blasting using a machine with a compressor and hose) services to their customers. The initial cost of this venture would include installing an automated power washing machine, purchasing cleaning equipment and hiring a field operations manager (i.e., expenditures on fixed costs). Costs that will vary power wash include cleaning supplies (rags, dusters, wipes, solvents), city water usage, and hourly wages for workers. Three service packages are being considered. Joe is interested in knowing the following: The break-even volume based on the number of washes and sales dollars The sensitives of the break-even volumes to changes in one of the decision variables The sensitives of the break-even volumes to changes in two of the decision variables The data below is related to houses where they perform junk removal, which is 200 houses daily. The input variables use base case assumptions and include information about assumed costs, prices, and sales mix. The expected fixed costs every year are as follows: Power wash machine: $20,000; 5-year life, no salvage value ; $4,000 straight-line depreciation Car wash cleaning equipment: $1,000 for replacement parts for hoses, sprayers, and brushes Operations manager salary: $40,000, including all employee benefits TOTAL ANNUAL FIXED COST = $40,000 The variable costs, per car wash, include the following: Cleaning supplies: $2.00 for cleaning solvents, disposable rags, etc. Municipal water use: $1.00, based on the annual water bill divided by 6,000 homes washed Hourly wage rate: $10.00 per hour, or $2.50 per wash, where for 1 worker requires 15 minutes to complete a wash (Note that the labor cost per wash for the Economy package is $2.50; this cost increases based on the time required to provide additional washing and cleaning services, such as those for the basic and deluxe offerings.) UNIT VARIABLE COSTS: Economy = $5.00; Basic = $9.50; Deluxe = $13. The sales price per wash will depend on the level of service desired. Joe is thinking about offering three "packages " for customers: Economy wash at $15.00 per wash Basic wash at $25.00 per wash Deluxe wash at $40.00 per wash It is expected that 65% of all sales will be for the economy package ; 20 % for the basic package and 15 % for the deluxe treatment. Required: Prepare a financial model in Excel to evaluate and model the profitability of this business. |