using excel

Mr. Edward Henderson began sweeping floors at Warren Molded Plastics, Inc., located in Salem, Ohio in 1968. Warren Molded Plastics used injection molds to make specialized plastic parts that were utilized by the automobile industry, namely General Motors, Inc., hereinafter referred to as "GM." While Mr. Henderson did not have a college degree, he garnered valuable knowledge while cleaning up the scrap dropped by the line workers. He offered cost savings suggestions to management based on what he had observed. Impressed, management promoted him and by 1985, he became the plant manager of the Salem facility. Over the years, competition intensified and GM was able to dictate the prices they were willing to pay suppliers. Currently, Warren Molded Plastics made two parts for GM-center consoles and right door panels for the Cadillac Sedan and Buick LaCrosse, respectively. GM would pay no more than $2,500 for center consoles and $2,000 for right door panels Warren Molded Plastics began forecasting 2020 sales and associated costs in mid-September of 2019 based on assumptions that GM would order 4,320 center consoles for its Cadillac Sedan and 6,480 right door panels for its Buick LaCrosse. Mr Henderson knew that he had to control variable costs because Warren Molded Plastics could no longer raise their prices Please prepare the following: MASTER BUDGET: 25 Points Sales Budget (2 points) o Warren Molded Plastics projects the following sales forecast for Center Consoles and Right Door Panels. below, is what GM has agreed to pay. "Sales Price", as noted Product Forecasted Sales Sales VolumePrice Center Consoles for Cadillac Sedan Right Door Panel for Buick LaCrosse 4,320 6,480 $2,500 $2,000 Warren Molded Plastics has a corporate policy that it must maintain some safety stock to error on the side of conservatism Desired Ending Beginning Finished Finished Goods Goods Inventory Product Inventory 24 36 Center Consoles for Cadillac Sedan 16 28 Right Door Panel for Buick LaCrosse The products made by Warren Molded polypropylene. Warren Molded has a corporate policy that it must maintain some safety stock to error on the side of conservatism. Center Consoles and Right Door Panels contain both polystyrene and polypropylene in varying amounts is reflected in its cost. The desired inventory levels and respective costs per polymer per product are noted below. Plastics, Inc. are largely synthetic and made from two polymers: polystyrene and and this Cost of Beginning Cost of Desired Product Ending Inventory'Polystyrene Polypropylene Inventory $550 $580 $650 $700 Center Consoles for Cadillac Sedan Right Door Panel for Buick LaCrosse 12 20 16 28 for both polymers Warren Molded Plastics has two Departments: Injection Molding and the Quality Control/Packaging Department. Workers in the Injection Molding Department are paid $25 per hour. Workers in the Quality Control/Packaging Department are paid $20 per hour. Center Consoles and Right Door Panels spend the following amount of time in each department. Product Injection Molding Quality Control and Packaging Department Department (in hours)(in hours) Center Consoles for Cadillac Sedan Right Door Panel for Buick LaCrosse Hint: You will need the TOTAL direct labor hours to allocate variable manufacturing overhead in the Manufacturing Overhead 14 Budget o Manufacturing Overhead Budget (8 points In order to facilitate better cost control, Mr. Henderson has broken out Manufacturing Overhead into its variable and fixed cost components. He contacted the accounting department and learned that his area was being charged the following fixed expenses: 1. Lease Expense-$200,000 2. Insurance-$136,000 3. Property Taxes-$24,000 4. Depreciation-$90,200 Variable expenses were allocated per direct labor hour in the following manner Indirect Material-$.05 per DHL Indirect Labor-$.10 per DHL Factory Utilities-$.40 per DHL Other-$.20 per DHL 1. 2. 3. 4. Hint: This budget will yield o variable and fixed component total based on the PROOUCTION SCHEDULE in oddition, the manufacturing overhead cost based on PRODUCTIO wil be higher than the Soles Budget becouse forecasted soles are usuolly different thon whet needs to be produced becouse of safety stock. Remember becouse of Generally Accepted Accounting Principles, safety stock remains on the balance sheet as port of inventory and would not be included on the Income Statement Warren Molded Plastics, Inc. uses flexible budgeting. While the company was forecasted to sell 4,320 Center Consoles for GM's Cadillac Sedan, actual sales were 5,000-slightly higher thon expected Center Consoles Actual Cost Static Budget based on based on 4,320 Units $5,184,000 $1,533,600 $48,600 $180,080 $6,946,280 5,000 Units $6,100,000 $1,477,440 Direct Materials Direct Labor Variable Overhead Fixed Overhead TOTAL $47,200 $145,000 $7,769,640 Determine the price/rate and efficiency variances for Center Consoles and provide an interpretation for Mr. Henderson. Actual sales were 6,300-a Warren Molded Plastics was forecasted to sell 6,480 Right Door Panels for GM's Buick LaCrosse. decline and 180 units lower than expected. Actual Cost Static Budget based on 6,300 Units 6,480 Units Right Door Panels based on $7,900,000 $8,294,400 $2,850,000 $2,786,400 $270,120 Direct Materials Direct Labor Variable Overhead Fixed Overhead TOTAL $84,200 $281,000 $11,115,200 $11,438,400 $87,480 Determine the price/rate and efficiency variances for Right Door Panels and provide an interpretation for Mr. Henderson. REMEMBER: THE TEXTBOOK IS YOUR BEST FRIEND. DESIGN YOUR SPREADSHEET AND THAT WILL GUIDE YOU TOWARD SOLVING THE NUMBERS. GOOD LUCK. DUE: THURSDAY 04/18/19 Mr. Edward Henderson began sweeping floors at Warren Molded Plastics, Inc., located in Salem, Ohio in 1968. Warren Molded Plastics used injection molds to make specialized plastic parts that were utilized by the automobile industry, namely General Motors, Inc., hereinafter referred to as "GM." While Mr. Henderson did not have a college degree, he garnered valuable knowledge while cleaning up the scrap dropped by the line workers. He offered cost savings suggestions to management based on what he had observed. Impressed, management promoted him and by 1985, he became the plant manager of the Salem facility. Over the years, competition intensified and GM was able to dictate the prices they were willing to pay suppliers. Currently, Warren Molded Plastics made two parts for GM-center consoles and right door panels for the Cadillac Sedan and Buick LaCrosse, respectively. GM would pay no more than $2,500 for center consoles and $2,000 for right door panels Warren Molded Plastics began forecasting 2020 sales and associated costs in mid-September of 2019 based on assumptions that GM would order 4,320 center consoles for its Cadillac Sedan and 6,480 right door panels for its Buick LaCrosse. Mr Henderson knew that he had to control variable costs because Warren Molded Plastics could no longer raise their prices Please prepare the following: MASTER BUDGET: 25 Points Sales Budget (2 points) o Warren Molded Plastics projects the following sales forecast for Center Consoles and Right Door Panels. below, is what GM has agreed to pay. "Sales Price", as noted Product Forecasted Sales Sales VolumePrice Center Consoles for Cadillac Sedan Right Door Panel for Buick LaCrosse 4,320 6,480 $2,500 $2,000 Warren Molded Plastics has a corporate policy that it must maintain some safety stock to error on the side of conservatism Desired Ending Beginning Finished Finished Goods Goods Inventory Product Inventory 24 36 Center Consoles for Cadillac Sedan 16 28 Right Door Panel for Buick LaCrosse The products made by Warren Molded polypropylene. Warren Molded has a corporate policy that it must maintain some safety stock to error on the side of conservatism. Center Consoles and Right Door Panels contain both polystyrene and polypropylene in varying amounts is reflected in its cost. The desired inventory levels and respective costs per polymer per product are noted below. Plastics, Inc. are largely synthetic and made from two polymers: polystyrene and and this Cost of Beginning Cost of Desired Product Ending Inventory'Polystyrene Polypropylene Inventory $550 $580 $650 $700 Center Consoles for Cadillac Sedan Right Door Panel for Buick LaCrosse 12 20 16 28 for both polymers Warren Molded Plastics has two Departments: Injection Molding and the Quality Control/Packaging Department. Workers in the Injection Molding Department are paid $25 per hour. Workers in the Quality Control/Packaging Department are paid $20 per hour. Center Consoles and Right Door Panels spend the following amount of time in each department. Product Injection Molding Quality Control and Packaging Department Department (in hours)(in hours) Center Consoles for Cadillac Sedan Right Door Panel for Buick LaCrosse Hint: You will need the TOTAL direct labor hours to allocate variable manufacturing overhead in the Manufacturing Overhead 14 Budget o Manufacturing Overhead Budget (8 points In order to facilitate better cost control, Mr. Henderson has broken out Manufacturing Overhead into its variable and fixed cost components. He contacted the accounting department and learned that his area was being charged the following fixed expenses: 1. Lease Expense-$200,000 2. Insurance-$136,000 3. Property Taxes-$24,000 4. Depreciation-$90,200 Variable expenses were allocated per direct labor hour in the following manner Indirect Material-$.05 per DHL Indirect Labor-$.10 per DHL Factory Utilities-$.40 per DHL Other-$.20 per DHL 1. 2. 3. 4. Hint: This budget will yield o variable and fixed component total based on the PROOUCTION SCHEDULE in oddition, the manufacturing overhead cost based on PRODUCTIO wil be higher than the Soles Budget becouse forecasted soles are usuolly different thon whet needs to be produced becouse of safety stock. Remember becouse of Generally Accepted Accounting Principles, safety stock remains on the balance sheet as port of inventory and would not be included on the Income Statement Warren Molded Plastics, Inc. uses flexible budgeting. While the company was forecasted to sell 4,320 Center Consoles for GM's Cadillac Sedan, actual sales were 5,000-slightly higher thon expected Center Consoles Actual Cost Static Budget based on based on 4,320 Units $5,184,000 $1,533,600 $48,600 $180,080 $6,946,280 5,000 Units $6,100,000 $1,477,440 Direct Materials Direct Labor Variable Overhead Fixed Overhead TOTAL $47,200 $145,000 $7,769,640 Determine the price/rate and efficiency variances for Center Consoles and provide an interpretation for Mr. Henderson. Actual sales were 6,300-a Warren Molded Plastics was forecasted to sell 6,480 Right Door Panels for GM's Buick LaCrosse. decline and 180 units lower than expected. Actual Cost Static Budget based on 6,300 Units 6,480 Units Right Door Panels based on $7,900,000 $8,294,400 $2,850,000 $2,786,400 $270,120 Direct Materials Direct Labor Variable Overhead Fixed Overhead TOTAL $84,200 $281,000 $11,115,200 $11,438,400 $87,480 Determine the price/rate and efficiency variances for Right Door Panels and provide an interpretation for Mr. Henderson. REMEMBER: THE TEXTBOOK IS YOUR BEST FRIEND. DESIGN YOUR SPREADSHEET AND THAT WILL GUIDE YOU TOWARD SOLVING THE NUMBERS. GOOD LUCK. DUE: THURSDAY 04/18/19