Question

Required information [The following information applies to the questions displayed below.) Franklin 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 a. October sales are estimated to be $180,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. b. 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. c. The cost of goods sold is 60 percent of sales. The company desires to maintain a minimum ending inventory equal to 10 percent of the next month's cost of goods sold. However, ending inventory of December is expected to be $12,600. Assume that all purchases are made on account. Prepare an inventory purchases budget. d. 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. e. Budgeted selling and administrative expenses per month follow:

Answer 1

Franklin Company Sales Budgets Cash Sales Credit Sales Total Budgeted Sales October November December 72000 86400 103680 108000 129600 155520 180000 216000 259200 Total 262080 393120 655200 Sales Collections Current Cash Sales Plus Collections from A/R Total Collections October November December Total 72000 86400 103680 262080 108000 129600 237600 72000194400 233280499680 Purchase Budget COGS Add:Ending Inventory October November December 108000 129600 155520 12960 15552 12600 120960 145152 168120 -12960 -15552 120960 132192 152568 Total 393120 12600 405720 0 405720 Less:Beg. Inventory Purchases Payables schedule October November December Current Accounts Payables 8467292534 106798 Prior Months Accounts Payables 36288 39658 Totals 84672 128822 146455 Total 284004 75946 359950

Selling and Administrative Expense Budget October November December Total Salary Expense 18600 18600 18600 55800 Sales Commission 7200 8640 10368 26208 Supplies Expense 3600 4320 5184 13104 Utilities 2000 2000 2000 6000 Depreciation on Stores Fixtures 4600 4600 4600 13800 Rent 5400 5400 5400 16200 Miscellaneous 1800 1800 1800 5400 Total S&A Expenses 4320045360 47952136512 Cash Budget For the Quarter ending December 31 October November December Total Opening Balance 18328 Sales Collections 720001 94400 233280 499680 Cash Available 72000 212728 251280.32 499680 84672 18600 3600 128822 18600 7200 4320 2000 5400 1800 146455 18600 8640 5184 2000 5400 1800 Payments: Purchase Payments Salary Expense Sales Commission Supplies Expense Utilities Rent Miscellaneous Store Fixtures Total Payments Excess Cash Financing Borrowing Repayment Interest Total Cash Balance 359950 55800 15840 13104 4000 16200 5400 191600 661894 -162214 5400 1800 191600 305672 -233672 168142 44586 188079 63201 252000 -26064 -521 -26585 18000 -43885 - 1317 -45202 18000 252000 -69949 -1838 180213 18000 252000 18328 44586 - X -0.02x = 18000 x = 26064 x 0.02 = 521 63201 - X -0.03x = 18000 x = 43885 x 0.03 = 1317

A 1 Franklin Company 3 Sales Budgets 5 Cash Sales 6 Credit Sales 7 Total Budgeted Sales October =B7*0.4 =B7-B5 180000 November =C7*0.4 C7-C5 =B7*1.2 December =D7*0.4 =D7-05 =C7.1.2 Total =SUM(B5:05) =SUM(B6:06) SUM(B7:07) 9 Sales Collections 10 October December =B5 =D5 11 Current Cash Sales 12 Plus Collections from A/R 13 Total Collections November =C5 =B6 ESUM(C11C12) Total =SUM(B11:011) =SUM(B12:012) =SUM(B13:013) =C6 =SUM(B11:B12) SUM(D11:D12) 14 15 Purchase Budget 16 17 COGS 18 Add:Ending Inventory 19 20 Less:Beg. Inventory 21 Purchases October =B7*0.6 =C17-0.1 =SUM(B17:B18) November =C7*0.6 =D17.0.1 SUM(C17:C18) --B18 =SUM(C19:C20) December =D7*0.6 12600 SUM(D17:018) --C18 =SUM(D19:D20) Total =E7*0.6 =D18 SUM(E17:E18) =B20 =SUM(E19:E20) =SUM(B19:B20) 23 Payables schedule 24 October =B21*0.7 25 Current Accounts Payables 26 Prior Months Accounts Payables 27 Totals 28 29 Selling and Administrative Expense Budget November =C21*0.7 =B21*0.3 =SUM(C25:026) December =D21*0.7 =C21*0.3 =SUM(D25:026) Total =SUM(B25:D25) =SUM(B26D26) ESUM(B27:D27) =SUM(B25:326) 31 Salary Expense 32 Sales Commission 33 Supplies Expense 34 Utilities 35 Depreciation on Stores Fixtures 36 Rent 37 Miscellaneous 38 Total S&A Expenses 39 October 18600 =B7*0.04 =B7*0.02 2000 4600 5400 1800 =SUM(B31:B37) November 18600 =C7*0.04 =C7*0.02 2000 4600 =B36 =B37 =SUM(C31:C37) December 18600 =D7*0.04 =D70.02 2000 4600 =B36 =C37 =SUM(D31:D37) Total =SUM(B31:D31) =SUM(B32:D32) =SUM(B33:D33) =SUM(B34:D34) =SUM(B35:D35) =SUM(B36:D36) =SUM(B37:037) ESUM(E31:E37)