The Tub in Your Home Company (TIYHC) builds the highest quality bathrooms in the homebuilding business. TIHYC is a subcontractor that usually works “underneath” the general contractor. TIYHC constructs bathrooms components purchased outside. The costs (value) added by TIYHC are indirect costs (which include assembly labor), storage, and transportation. TIYHC constructs two types of bathrooms: standard and deluxe. The cost behavior of TIYHC is as follows:
Fixed Variable
Purchased Components
Deluxe bathroom $800 per component
Standard bathroom $400 per component
Indirect Costs $40,000 $16 per component
Storage $ 8,000 $4 per bathroom
Transportation $ 8,000 2 per bathroom
Both the standard and deluxe bathrooms require 5 components: toilets, bathtubs, sinks, showers, and cabinets. Therefore, the total cost of deluxe bathrooms is $4,000 and total cost of standard bathrooms is $2,000. TYHC uses a six-month continuous budget that is revised monthly. Sales forecasts for the next eight months are as follows:
Deluxe Standard
October 320 bathrooms 400 bathrooms
November 240 300
December 560 700
January 320 400
February 320 400
March 240 300
April 240 300
May 280 350
Using Excel prepare a table of budgeting information and an operating expense budget for TYHC for October through March. Incorporate the expectation that sales of the standard bathrooms will be 125% of deluxe bathrooms. Prepare a spreadsheet that can be revised easily for succeeding months.