Question

Completing a Master Budget [L02] The following data relate to the operations of Gaudreau Company, which distributes consumer goods Current assets as of December 31 Cash.... Accounts receivable. Inventory Buildings and equipment, net.. Accounts payable Common shares Retained earnings $6,000 $36,000 $9,800 $110,885 $32,550 $100,000 $30,135 a The gross margin is 30% of sales. (In other words, cost of goods sold is 70% of sales.) b. Actual and budgeted sales data are as follows: December (actual) January.. February $60,000 $70,000 $80,000 $85,000 $55,000 April.. C. Sales are 40% for cash and 60% on credit. Credit sales are collected in the month following sale. The accounts receivable at December 31 are the result of December credit sales. d. Each month's ending inventory should equal 20% of the following month's budgeted e. One-quarter of a month's inventory purchases is paid for in the month of purchase t Monthly expenses are as follows: commissions, $12,000, rent, $1,800, other cost of goods sold the other three-quarters is paid for in the following month. The accounts payable at December 31 are the result of December purchases of inventory expenses (excluding depreciation), 8% of sales. Assume that these expenses are paid monthly. Depreciation is $2,400 for the quarter and includes depreciation on new assets acquired during the quarter g Equipment will be acquired for cash $3,000 in January and $8,000 in February e English (Canada)

Answer 1

A	B	C	D	E	F	G	H	I	J
2		1)
3		Sales Budget		Dec	Jan	Feb	Mar	Apr
4		Cash Sales	40%	$24,000.00	$28,000.00	$32,000.00	$34,000.00	$22,000.00	=$D$4*I6
5		Credit Sales	60%	$36,000.00	$42,000.00	$48,000.00	$51,000.00	$33,000.00
6		Total Sales		$60,000.00	$70,000.00	$80,000.00	$85,000.00	$55,000.00
7
8		Thus the Expected Cash Collection will be as follows:
9
10			Jan	Feb	Mar	Quarter
11		Cash Sales	$28,000.00	$32,000.00	$34,000.00	$94,000.00
12		Credit Sales	$36,000.00	$42,000.00	$48,000.00	$126,000.00
13		Total Cash Collections	$64,000.00	$74,000.00	$82,000.00	$220,000.00
14
15		2)
16
17		Direct Materials Purchase Budget		Dec	Jan	Feb	Mar	Apr
18		Sales		$60,000.00	$70,000.00	$80,000.00	$85,000.00	$55,000.00
19		Cost of goods sold	70%	$42,000.00	$49,000.00	$56,000.00	$59,500.00	$38,500.00	=I18*$D$19
20		Desired Ending Inventory	20%	$9,800.00	$11,200.00	$11,900.00	$7,700.00
21		Total needed		$51,800.00	$60,200.00	$67,900.00	$67,200.00
22		Less Beginning Inventory			$9,800.00	$11,200.00	$11,900.00
23		Cost of Purchases			$50,400.00	$56,700.00	$55,300.00
24
25		Hence merchandise purchasing budget will be as follows:
26
27		Direct Materials Purchase Budget	Jan	Feb	Mar	Quarter
28		Sales	$70,000.00	$80,000.00	$85,000.00	$235,000.00
29		Cost of goods sold	$49,000.00	$56,000.00	$59,500.00	$164,500.00
30		Desired Ending Inventory	$11,200.00	$11,900.00	$7,700.00	$30,800.00
31		Total needed	$60,200.00	$67,900.00	$67,200.00	$195,300.00
32		Less Beginning Inventory	$9,800.00	$11,200.00	$11,900.00	$32,900.00
33		Cost of Purchases	$50,400.00	$56,700.00	$55,300.00	$162,400.00
34
35		3)
36
37			January	February	March	Quarter
38		December Purchases	$32,550.00			$32,550.00
39		January Purchases	$12,600.00	$37,800.00		$50,400.00
40		February Purchases		$14,175.00	$42,525.00	$56,700.00
41		March Purchases			$13,825.00	$13,825.00
42		Total Disbursement	$45,150.00	$51,975.00	$56,350.00
43
44		4)
45
46			January	February	March	Quarter
47		Commissions	$12,000.00	$12,000.00	$12,000.00	$36,000.00	=SUM(D47:F47)
48		Rent	$1,800.00	$1,800.00	$1,800.00	$5,400.00	=SUM(D48:F48)
49		Other Expenses	$5,600.00	$6,400.00	$6,800.00	$18,800.00	=SUM(D49:F49)
50		Total Disbursement	$19,400.00	$20,200.00	$20,600.00	$60,200.00
51
52		5)
53		Cash Budget
54			January	February	March	Quarter
55		Cash Balance, beginning	$6,000	$6,000	$6,000	$6,000
56		Add Cash Collections	$64,000	$74,000	$82,000	$220,000
57		Total cash available	$70,000	$80,000	$88,000	$226,000
58		Less cash disbursements
59		For Inventory	$45,150	$51,975	$56,350	$153,475
60		For Operating Expenses	$19,400	$20,200	$20,600	$60,200
61		For Equipment	$3,000	$8,000	$0	$11,000
62		Total cash disbursement	$67,550	$80,175	$76,950	$224,675
63		Excess (deficiency) of Cash	$2,450	($175)	$11,050	$1,325
64