Preparing a Direct Materials Purchases Budget

Patrick Inc. makes industrial solvents sold in 5-gallon drums. Planned production in units for the first 3 months of the coming year is:

January	42,000
February	42,000
March	51,000

Each drum requires 5.5 gallons of chemicals and one plastic drum. Company policy requires that ending inventories of raw materials for each month be 25% of the next months production needs. That policy was met for the ending inventory of December in the prior year. The cost of one gallon of chemicals is $1.80. The cost of one drum is $1.40.

This information has been collected in the Microsoft Excel Online file. Open the spreadsheet, perform the required analysis, and input your answers in the questions below.

Open spreadsheet

Required:

1.	Calculate the ending inventory of chemicals in gallons for December of the prior year, and for January and February. What is the beginning inventory of chemicals for January? Round your answers to the nearest whole gallon.

	Ending inventory for December	fill in the blank 2 gallons
	Ending inventory for January	fill in the blank 3 gallons
	Ending inventory for February	fill in the blank 4 gallons
	Beginning inventory for January	fill in the blank 5 gallons

2.

Prepare a direct materials purchases budget for chemicals for the months of January and February. Do not round intermediate calculations. Round dollar purchases to the nearest dollar. Round gallons per unit to one decimal place. Round price per gallon to the nearest cent. Round all the other values to the nearest whole unit. Do not include a multiplication symbol as part of your answer.

	Patrick Inc. Direct Materials Purchases Budget - Chemicals in Gallons For the Months of January and February
		January	February
	Production in units	fill in the blank 6	fill in the blank 7
	Gallons per unit	fill in the blank 8	fill in the blank 9
	Gallons for production	fill in the blank 10	fill in the blank 11
	Desired ending inventory	fill in the blank 12	fill in the blank 13
	Needed	fill in the blank 14	fill in the blank 15
	Less: Beginning inventory	fill in the blank 16	fill in the blank 17
	Purchases	fill in the blank 18	fill in the blank 19
	Price per gallon	$fill in the blank 20	$fill in the blank 21
	Dollar purchases	$fill in the blank 22	$fill in the blank 23

3.	Calculate the ending inventory of drums for December of the prior year and for January and February. Round your answers to the nearest whole unit.

	Ending inventory for December	fill in the blank 24 drum
	Ending inventory for January	fill in the blank 25 drum
	Ending inventory for February	fill in the blank 26 drum

4.

Prepare a direct materials purchases budget for drums for the months of January and February. Do not round intermediate calculations. Round dollar purchases to the nearest dollar. Round price per drum to the nearest cent. Round all the other values to the nearest whole unit. Do not include a multiplication symbol as part of your answer.

	Patrick Inc. Direct Materials Purchases Budget - Drums For the Months of January and February
		January	February
	Production in units	fill in the blank 27	fill in the blank 28
	Drums per unit	fill in the blank 29	fill in the blank 30
	Drums for production	fill in the blank 31	fill in the blank 32
	Desired ending inventory	fill in the blank 33	fill in the blank 34
	Needed	fill in the blank 35	fill in the blank 36
	Less: Beginning inventory	fill in the blank 37	fill in the blank 38
	Purchases	fill in the blank 39	fill in the blank 40
	Price per drum	$fill in the blank 41	$fill in the blank 42
	Dollar purchases	$fill in the blank 43	$fill in the blank 44

Preparing a Direct Materials Purchases Budget Patrick Inc. makes industrial solvents sold in 5-gallon drums. Planned production in units for the first 3 months of the coming year is: DATA January February March 42,000 42,000 51,000 Direct materials needed per unit: Chemicals (in gallons) Plastic drums 5.5 1 Direct materials cost: Chemicals (per gallon) Plastic drums (per unit) $1.80 $1.40 Ending inventory requirements 25% of next month's production needs of raw materials That ending inventory policy was met for the ending inventory of December in the prior year. Using formulas and cell references, perform the required analysis, and input your answers into the Amount, January, and February columns. Transfer the numeric results for the green entry cells (C25:C28, B34:C42, C45:C47, B53:C61) into the appropriate fields in CNOWV2 for grading. Amount Formulas Required: 1. Ending inventory in gallons for December Ending inventory in gallons for January 1. Ending inventory in gallons for December Ending inventory in gallons for January Ending inventory in gallons for February Beginning inventory in gallons for January Formulas 2. Patrick Inc. Direct Materials Purchases Budget - Chemicals in Gallons For the Months of January and February January February Production in units Gallons per unit Gallons for production Desired ending inventory Needed Less: Beginning inventory Purchases Price per gallon Dollar purchases Amount Formulas 3. Ending inventory in drums for December Ending inventory in drums for January Ending inventory in drums for February 4. Patrick Inc. Direct Materials Purchases Budget - Drums For the Months of January and February January February Formulas Production in units Gallons per unit Gallons for production Desired ending inventory Needed Less: Beginning inventory Purchases Price per gallon Dollar purchases Amount Formulas 3. Ending inventory in drums for December Ending inventory in drums for January Ending inventory in drums for February Formulas 4. Patrick Inc. Direct Materials Purchases Budget - Drums For the Months of January and February January February Production in units Drums per unit Drums for production Desired ending inventory Needed Less: Beginning inventory Purchases Price per drum Dollar purchases