Question

Using the below information open an excel file and create a Raw Materials Budget, and a Schedule of Cash Dispersments for Raw Materials for Fiwrt for the months of Oct, Nov, and Dec including the quarterly totals. Make sure to use proper format including dollar signs and headers - this will count in the grade. Upload the excel file as your response.

Fiwrt Corporation manufactures and sells stainless steel coffee mugs. Expected mug sales (in units) are expected to be as follows: September – 32,000, October – 30,000, November – 36,000, December – 34,000, and January 35,000.

Selling Price is expected to be $8 per mug. 40% of sales are on account. All sales on account are collected in the following month. Fiwrt likes to maintain a finished goods inventory equal to 30% of the next month's estimated sales. Fiwrt uses 1.5 lb of ceramic for each mug which costs an average of $2 per lb. History has shown they should maintain a raw material inventory equal to 25% of the next month's needs. They pay for their purchases 50% in the current month and 50% in the month following purchase.

Answer 1

1. Production Budget:

	September	October	November	December	January
a. Sales (units)	32,000.00	30,000.00	36,000.00	34,000.00	35,000.00
b. Add: Ending finished good inventory (units)	9,000.00	10,800.00	10,200.00	10,500.00	9,900.00
(30% of next month's sales)	(30,000 * 30%)	(36,000 * 30%)	(34,000 * 30%)	(35,000 * 30%)	(33,000 * 30%)
c. Less: Beginning finished good inventory (units)	9,600.00	9,000.00	10,800.00	10,200.00	10,500.00
	(32,000 * 30%)	(30,000 * 30%)	(36,000 * 30%)	(34,000 * 30%)	(35,000 * 30%)
d. Production (units) (a+b-c)	31,400.00	31,800.00	35,400.00	34,300.00	34,400.00

Wherever required September and January month data has been provided for working purposes only.

2. Raw material purchase Budget (in Quantity)

	September	October	November	December	January	Quarterly Total
a. Production (units)	31,400.00	31,800.00	35,400.00	34,300.00	34,400.00	1,01,500.00
b. Raw material required per unit	1.50 lb	1.50 lb	1.50 lb	1.50 lb	1.50 lb	1.5lb
c. Total raw material required (a * b)	47,100.00	47,700.00	53,100.00	51,450.00	51,600.00	1,52,250.00
d. Add: Ending raw material inventory	11,925.00	13,275.00	12,862.50	12,900.00		39,037.50
(25% of next month's needs)	(47,700 * 25%)	(53,100 * 25%)	(51,450 * 25%)	(53,100 * 25%)
e. Less: Beginning raw material inventory	11775	11,925.00	13,275.00	12,862.50		38,062.50
	(47,100 * 25%)	(47,700 * 25%)	(53,100 * 25%)	(51,450 * 25%)
f. Raw material to be purchased (lb) (c+d-e)	47,250.00	49,050.00	52,687.50	51,487.50		1,53,225.00

Quarterly total represents the months of October, November and December. Other months data has been provided for showing workings only.

3. Raw material purchase Budget (in value):

	September	October	November	December	Quarterly Total
a. Raw material to be purchase (in Quantity)	47,250.00	49,050.00	52,687.50	51,487.50	1,53,225.00
b. Raw material price per lb ($)	2	2	2	2	2
c. Total Raw material purchase cost ($) (a * b)	94,500.00	98,100.00	1,05,375.00	1,02,975.00	3,06,450.00

Quarterly total represents the months of October, November and December.

4. Schedule showing the cash disbursement for raw materials:

	September ($)	October ($)	November ($)	December ($)	Total ($)
a. Total Raw material purchase cost ($)	94,500.00	98,100.00	1,05,375.00	1,02,975.00
b. Paid in the same month ($) (a * 50%)	47,250.00	49,050.00	52,687.50	51,487.50	1,53,225.00
(50% of purchases made during the month)	($94,500 * 50%)	($98,100 * 50%)	($105,375 * 50%)	($102,975 * 50%)
c. Paid for September month ($94,500 * 50%)		47,250.00			47,250.00
d. Paid for October month ($98,100 * 50%)			49,050.00		49,050.00
e. Paid for November month ($105,375 * 50%)				52,687.50	52,687.50
f. Total paid ($) (b+c+d+e)	47,250.00	96,300.00	1,01,737.50	1,04,175.00	3,02,212.50

Quarterly total represents the months of October, November and December.