Use Excel to prepare the following budgets for March 2020: 1. Sales budget 2. Production budget...

 

Transcribed Image Text:

Use Excel to prepare the following budgets for March 2020: 1. Sales budget 2. Production budget 3. Direct materials purchases budget 4. Direct labor cost budget 5. Factory overhead cost budget 6. Costs of goods sold budget. Work in process at the beginning of March is estimated to be $11,000, and work in process at the ending of March is desired to be $12,500. 7. Selling and administrative expenses budget 8. Budgeted income statement The budget director of Athletic Supplies Inc., with the assistance of the controller, treasurer. production manager, and sales manager, has gathered the following data for use in developing the budgeted income statement for March: a. Estimated sales for March: Batting helmet Football helmet b. Estimated inventories at March 1: Direct materials: Plastic Foam lining Finished products: Batting helmet Football helmet c. Desired inventories at March 31:1 Direct materials: Plastic Foam lining Finished products: Batting helmet Football helmet Plastic Foam lining 1,100 units at $42 per unit 6,400 units at $146 per unit In manufacture of football helmet: Foam lining f. Direct labor requirements: Batting helmet: d. Direct materials used in production: In manufacture of batting helmet: Molding Department Assembly Department Football helmet: 55 lbs. 65 lbs. Molding Department Assembly Department 75 units at $28 per unit 220 units at $75 per unit 70 lbs. 74 lbs. Plastic Foam lining 3.5 lbs. per unit of product 1.75 lbs. per unit of product e. Anticipated cost of purchases and beginning and ending inventory of direct materials: Plastic $5.4 per lb. $3.6 per lb. 50 units at $27 per unit 225 units at $72 per unit 1.5 lbs. per unit of product 0.75 lb. per unit of product 0.3 hr. at $21 per hr. 0.6 hr. at $15 per hr. 0.8 hr. at $21 per hr. 1.6 hrs. at $15 per hr. g. Estimated factory overhead costs for March: Indirect factory wages Depreciation of plant and equipment Power and light Insurance and property tax h. Estimated operating expenses for March: Sales salaries expense Advertising expense Office salaries expense Depreciation expense-office equipment Telephone expense-selling Telephone expense-administrative Travel expense-selling Office supplies expense Miscellaneous administrative expense i. Estimated other income and expense for March: Interest revenue Interest expense J. Estimated tax rate: 30% $75,000 14,000 3,000 3,700 $186,200 88,200 33,500 5,000 3,900 1,500 10,000 1,500 2,200 800 875 Use Excel to prepare the following budgets for March 2020: 1. Sales budget 2. Production budget 3. Direct materials purchases budget 4. Direct labor cost budget 5. Factory overhead cost budget 6. Costs of goods sold budget. Work in process at the beginning of March is estimated to be $11,000, and work in process at the ending of March is desired to be $12,500. 7. Selling and administrative expenses budget 8. Budgeted income statement The budget director of Athletic Supplies Inc., with the assistance of the controller, treasurer. production manager, and sales manager, has gathered the following data for use in developing the budgeted income statement for March: a. Estimated sales for March: Batting helmet Football helmet b. Estimated inventories at March 1: Direct materials: Plastic Foam lining Finished products: Batting helmet Football helmet c. Desired inventories at March 31:1 Direct materials: Plastic Foam lining Finished products: Batting helmet Football helmet Plastic Foam lining 1,100 units at $42 per unit 6,400 units at $146 per unit In manufacture of football helmet: Foam lining f. Direct labor requirements: Batting helmet: d. Direct materials used in production: In manufacture of batting helmet: Molding Department Assembly Department Football helmet: 55 lbs. 65 lbs. Molding Department Assembly Department 75 units at $28 per unit 220 units at $75 per unit 70 lbs. 74 lbs. Plastic Foam lining 3.5 lbs. per unit of product 1.75 lbs. per unit of product e. Anticipated cost of purchases and beginning and ending inventory of direct materials: Plastic $5.4 per lb. $3.6 per lb. 50 units at $27 per unit 225 units at $72 per unit 1.5 lbs. per unit of product 0.75 lb. per unit of product 0.3 hr. at $21 per hr. 0.6 hr. at $15 per hr. 0.8 hr. at $21 per hr. 1.6 hrs. at $15 per hr. g. Estimated factory overhead costs for March: Indirect factory wages Depreciation of plant and equipment Power and light Insurance and property tax h. Estimated operating expenses for March: Sales salaries expense Advertising expense Office salaries expense Depreciation expense-office equipment Telephone expense-selling Telephone expense-administrative Travel expense-selling Office supplies expense Miscellaneous administrative expense i. Estimated other income and expense for March: Interest revenue Interest expense J. Estimated tax rate: 30% $75,000 14,000 3,000 3,700 $186,200 88,200 33,500 5,000 3,900 1,500 10,000 1,500 2,200 800 875