Question

Using the below information open an excel file and create a Sales Budget, a Schedule of Cash Receipts, a Production Budget, 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. I need help with this part below. I think I have everything else right. 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

Answer
1. Schedule of Expected Cash Collection
	Oct	Nov	Dec	Total
Sales (in units)	30,000	36,000	34,000	1,00,000
Sale price p.u	$                 8	$                    8	$                 8
Budgeted Sales	$   2,40,000	$       2,88,000	$   2,72,000	$       8,00,000
Collection
60% cash sale realised in the month of Sale	$   1,44,000	$       1,72,800	$   1,63,200	$       4,80,000
40% credit sale realised in the following month		$          96,000	$   1,15,200	$       2,11,200
Opening accounts receivable	$   1,02,400			$       1,02,400
Total Cash Collection	$   2,46,400	$       2,68,800	$   2,78,400	$       7,93,600
2. Production budget
	Oct	Nov	Dec	Total
Budgeted Sales (in units)	30,000	36,000	34,000	1,00,000
Add: Ending Inventory
30% of the following month Sales	10,800	10,200	10,500	31,500
Total Requirement	40,800	46,200	44,500	1,31,500
Less: Opening Inventory	9,000	10,800	10,200	30,000
Total Production (in units)	31,800	35,400	34,300	1,01,500
3. Raw Material Purchase Budget
	Oct	Nov	Dec	Total
Total Production (in units)	31,800	35,400	34,300	1,01,500
Raw Material required p.u (in lbs)	1.5	1.5	1.5
Total material required	47,700	53,100	51,450	1,52,250
Add: Ending Inventory
(25% of next month production needs)	8,850	8575	6125	23,550
Less: opening Inventory	7,950	8850	8575	25,375
Total Raw material required to be purchased (in lbs)	48,600	52,825	49,000	1,50,425
Cost per lb	$                 2	$                    2	$                 2
Total Purchases	$ 97,200.00	$ 1,05,650.00	$ 98,000.00	$ 3,00,850.00
4. Schedule of cash Disbursements
	Oct	Nov	Dec	Total
Total Payables	$       97,200	$       1,05,650	$       98,000	$       3,00,850
50% paid in Current month	$       48,600	$          52,825	$       49,000	$       1,50,425
50% in following month		$          48,600	$       52,825	$       1,01,425
Total Cash paid	$       48,600	$       1,01,425	$   1,01,825	$       2,51,850
Note-
For Computing Ending Inventory of Raw Material for the month December, we shall need Production of Jan month
Sales (in units)	35,000
Add: Closing Inventory (30% of next month sale)	-
Less: Opening Inventory	10,500
Production for the month Jan	24,500
Feb month Budgeted sales is not provided , hence closing inventory for the month Jan is assumed to be zero.