Question

Munoz Company is a retail company that specializes in selling outdoor camping equipment. The company is considering opening a new store on October 1, 2019. The company president formed a planning committee to prepare a master budget for the first three months of operation. As budget coordinator, you have been assigned the following tasks:

Required

1. October sales are estimated to be $340,000, of which 40 percent will be cash and 60 percent will be credit. The company expects sales to increase at the rate of 20 percent per month. Prepare a sales budget.

2. The company expects to collect 100 percent of the accounts receivable generated by credit sales in the month following the sale. Prepare a schedule of cash receipts.

3. The cost of goods sold is 70 percent of sales. The company desires to maintain a minimum ending inventory equal to 20 percent of the next month’s cost of goods sold. However, ending inventory of December is expected to be $12,900. Assume that all purchases are made on account. Prepare an inventory purchases budget.

4. The company pays 70 percent of accounts payable in the month of purchase and the remaining 30 percent in the following month. Prepare a cash payments budget for inventory purchases.

5. Budgeted selling and administrative expenses per month follow:

Salary expense (fixed)	$	18,900
Sales commissions		4	% of Sales
Supplies expense		2	% of Sales
Utilities (fixed)	$	2,300
Depreciation on store fixtures (fixed)*	$	4,900
Rent (fixed)	$	5,700
Miscellaneous (fixed)	$	2,100

1. *The capital expenditures budget indicates that Munoz will spend $146,600 on October 1 for store fixtures, which are expected to have a $29,000 salvage value and a two-year (24-month) useful life.

Use this information to prepare a selling and administrative expenses budget.

6. Utilities and sales commissions are paid the month after they are incurred; all other expenses are paid in the month in which they are incurred. Prepare a cash payments budget for selling and administrative expenses.

7. Munoz borrows funds, in increments of $1,000, and repays them on the last day of the month. Repayments may be made in any amount available. The company also pays its vendors on the last day of the month. It pays interest of 2 percent per month in cash on the last day of the month. To be prudent, the company desires to maintain a $21,000 cash cushion. Prepare a cash budget.

Answer 1

sales budget October November December 340,000 408,000 S136,000 $ 163,200$ 195,840 $204,000 $ $ 489,600 Estimated Total Sales Estimated Cash Sales Estimated Credit Sales schedule of cash receipts October November December S136,000 163,200 195,840 204,000 244,800 367,200 440,640 From Cash Sales From Credit Sales Total Cash Receipts $136,000 S 13 inventory purchases budget 14 October November December $ 238,000 285,600 $ 342,720 $ 57,12068,544 $ 12,900 S295,120 354,144 355,620 cost of goods sold add: desired ending invento Total needs less: beginning invento inventorv to be purchased 16 17 18 57,120 68,544 $295,120 $ 20 21 cash payments budget for inventory purchases October November December $ 206,584 207,917 $ 200,953 -$88,536 $ 89,107 $ 290,060 For current month purchases For previous month purchases total cash payments for inventory purchases $206,584 296,453 27

B28 27 selling and administrative expenses budget October November December $ 18,90018,900 18,900 $6,800$8,160$ 9,792 $ 6,800$8,160$ 9,792 2,300 4,900 5,700 $ 2,1002,100$ 2,100 47,500 50,22053,484 29 Salary expense (fixed Sales commissions Supplies expense Utilities (fixed Depreciation on store fixtures (fixed Rent (fixed Miscellaneous (fixed total selling and administrative expenses 31 32 S 2,300 2,300 4.900|S 5,700|S 4.900 IS 5,700| S 34 37 38 39 40 41 cash payments budget for selling and administrative expenses October November December $ 18,9001 18,900 18,900 6,800 8,1 Salary expense (fixed Sales commissions Supplies expense Utilities (fixed Rent (fixed Miscellaneous (fixed cash payments for for selling and administrative expenses $ 33,50043,960$46,952 $6,800$8,160$ 9,792 43 $ 2,300$ 2,300 5,700 5,700 | S 5,700|S 45 $ 2,1002,100$ 2,100 47 48

B49 48 49 50 51 52 53 54 cash budget October November December 916$ 21,000 $ 440,640 461,640 beginning balance add: cash receipts total cash available less: cash payments total cash payments for inventory purchases cash payments for for selling and administrative expenses $ 33,50043,960$46,952 total cash payments net cash available add: loan taken less: loan repaid less: interest paid en $136,000 367,200 S 136,000 368,116 461 584 296,453 S 290,0 413 337,012 $ (104,084) $27,703 $ 124,628 ,084 340,4 57 58 59 60 61 62 63 $ 105,000 $ 100,397 $ 2,100 2,008 $91621,000 22,223 4,603 ding cash balance

for formulas and calculations, refer to the images below -

sales budget October November D4 1.2 E4*40% E4*60% December E4*1.2 F4*40% F4 * 60% Estimated Total Sales Estimated Cash Sales Estimated Credit Sales D4*40% D4*60% 6 schedule of cash receipts October November E5 December F5 E6 F10+F11 10 From Cash Sales From Credit Sales Total Cash Receipts D5 D10+D1.1 E10+E11 13 14 15 16 17 18 inventory purchases budget October November E4*70% F16*20% E16+E17 D17 E18-E19 December F4*70% cost of goods sold add: desired ending invento Total needs less: beginning invento inventory to be purchased D4*70% E16*20% D16+D17 12900 F16+F17 E17 F18-F19 D18-D19 21 cash payments budget for inventory purchases October November E20*70% D20*30% E24+E25 December F 20*70% E20*30% F24+F25 For current month purchases For previous month purchases total cash payments for inventory purchases D20* 70% 26 27 D24+D25

B28 selling and administrative expenses budget October November December 29 30 31 32 Salary expense (fixed Sales commissions Supplies expense Utilities (fixed) Depreciation on store fixtures (fixed Rent (fixed Miscellaneous (fixed total selling and administrative expenses 18900 18900 18900 D4*2% E4*2% F4*2% F4*2% 2300 4900 5700 2100 SUM(F30:F36 D4*2% 2300 4900 5700 2100 -SUM(D30:D36) E4*2% 2300 4900 5700 2100 -SUM(E30:E36) 34 35 37 cash pavments budget for selling and administrative expenses 39 40 October November E30 D31 E32 D33 E35 E36 SUM(E41:E46) December F30 E31 F32 E33 F35 F36 SUM(F41:F46) C30 -C31 -C32 C33 C35 -C36 cash payments for for selling and administrative expensesSUM( D41:D46 D30 0 43 D32 0 45 D35 -D36 47

B49 48 49 50 51 52 53 54 cash budget October November D62 E12 E51+E52 December E62 F12 F51+F52 0 beginning balance add: cash receipts total cash available less: cash payments -C26 C47 total cash payments net cash available add: loan taken less: loan repaid less: interest paid en D12 D51+D52 F26 F47 F55+F56 F53-F57 E26 E47 E55+E56 E53-E57 -D26 D47 D55+D56 D53-D57 57 58 59 105000 4603 D59-E60 61 62 63 D59*2% :( D59-E60) *2% ding cash balance D58+D59-D60-D61 E58+E59-E60-E61 -F58+F59-F60-F61

kindly give a thumbs up if satisfied with the solution. It will be highly appreciated.