Part A, B, C and some of D is alrady done can you please complate the next part.

GAP 3 B: Budgeting

Master Budget: XYZ Merchandising Company

XYZ Merchandising Company is preparing its master budget for year 20X2, please consider the balance sheet below prepared for the year 20X1.

XYZ Merchandising Balance Sheet December 31, 20X1
Cash	25,000
Accounts Receivable	52,600
Inventory	30,500
Long-Term Assets	50,000
Accounts Payable		27,420
Common Stock		75,000
Retained Earnings		55,680

a. Sales for the first quarter of 20X2 are expected to be $150,000 and sales are expected to increase each quarter by 10% through quarter four of 20X2. Sales for the first quarter of 20X3 are expected to be $180,000. All sales are initially on credit.

b. Credit sales are collected over two quarters. In the quarter of sale 60% of the credit sales are collected and the additional 40% is collected in the quarter following the sale.

c. Cost of Goods Sold equals 60% of Sales. Inventory purchases at ABC are made based on a desired ending inventory of 25% of the next quarters Cost of Goods Sold, beginning inventory is $30,500.

d. All inventory purchases are made on credit and the payment cycle is over two quarters with 50% of the purchases being paid in the quarter of purchase and the remainder paid in the following quarter. Inventory units cost $5.00 each.

e. Selling and administrative expenses include a variable and fixed portion. The variable portion is 25% of Sales and the fixed portion will be $17,000 for the 1^st quarter, $18,000 for the 2^nd quarter, $20,000 for the 3^rd quarter, and $22,000 for the 4^th quarter. All cash payments are made in the same quarter as the expense. The fixed portion of S & A Expenses includes $3,000 of depreciation in the 1^st quarter, 4,000 in the 2^nd, 6,000 in the 3^rd, and 8,000 in the 4^th.

f. The company plans on paying $2,000 in cash dividends each quarter and will make cash investments in equipment in the 2^nd quarter for $8,000, the 3^rd quarter for $12,000, and the 4^th quarters for $17,000.

Required:

Please complete the sales budget, cash receipts from sales, inventory purchases budget, cash payments for inventory, selling and administrative budget, cash payments for selling and administrative costs. Please clearly identify any amounts that are relevant for the cash budget or the pro-forma financial statements. Complete a Cash Budget with the information provided in letter f.

1 Sales Budget Summary 150000 -D3*0.1+D3 3 Sales -SUM (B3:E3) 5 Cash Recepts 6 Quarter of 60% -D3 0.6 EB3 0.4 EC3 0.4 ED 0.4 EE3 0.4 7 Quarter after 40% 52600 8 Total -SUM(E6: E7) SUM (B6:B7) JSUM (C6:C7) -SUM (D6:D7) -SUM (B8:E8) 0 Inventory Purchases Q3 EE 3 0.6 -B3 0.6 CGS SUM (B11:E11 -180000 0.6 0.25 12 2 Add: Ending Inventory -C11 0.25 D11 0.25 -E11 0.25 Esu MB11: B12) -suM (c11:c12) EsuM (D11:D12) ESUM(E11: E12) -su MM(F11: F12) 3 Inventory Needed EB12 EC12 ED12 EB14 4 Less: Beginning Inventory 30500 -D13-D14 >C13-C14 -E13-E14 F13-F14 -B13-B14 8 Cash Payments A/R Q1. Q2 Q3 Q4 0.5 27420 -B15 0.5 -C15 0.5 D15 0.5 0.5 -B15 0.5 EC15 0.5 ED15 0.5 -E15 0.5 -E15 0.5 ESUM (B19:B20) ES UMC19:C20) ESUM (D193D20) ESUM(E19: E20) ESUM(B21:E21) 9 0