Keggler's Supply is a merchandiser of three different products. The company's February 28 inventories are footwear, 21,000 units; sports equipment, 81,000 units; and apparel, 50,000 units. Management believes each of these inventories is too high. As a result, a new policy dictates that ending inventory in any month should equal 29% of the expected unit sales for the following month. Expected sales in units for March, April, May, and June follow. Footwear Sports equipment Apparel Budgeted Sales in Units March April May June 15,500 26,000 32,500 36,000 69,500 91,500 95,500 89,500 40, 500 37,500 32,500 23,000 Required: 1. Prepare a merchandise purchases budget (in units) for each product for each of the months of March, April, and May. KEGGLER'S SUPPLY Merchandise Purchases Budget For March, April, and May March April May KEGGLER'S SUPPLY Merchandise Purchases Budget For March, April, and May March April May FOOTWEAR 15,500 26,000 32,500 30% 30% 30% 7,950 7,800 9,750 Budgeted sales for next month Ratio of ending inventory to future sales Budgeted ending inventory Budgeted units sales for month Required units of available merchandise Actual (or estimated) beginning inventory Budgeted purchases SPORTS EQUIPMENT 26,500 33,500 15,000 22,950 43,250 34,300 7,950 18,500 10.050 4,450 28,600 34.400 Budgeted sales for next month 69.500 91 500 95,500 Ratio of ending inventory to future sales 30% 30% 30% 27,450 28,650 Budgeted ending inventory Budgeted units sales for month 27,000 69.000 90,000 96.000 Required units of available merchandise 96 000 117 450 124 650 78 500 27,000 28,800 Actual (or estimated) beginning inventory Budgeted purchases 17.500 91 800 94,050 APPAREL 27.600 32 600 SPORTS EQUIPMENT Budgeted sales for next month 69,500 91,500 95,500 Ratio of ending inventory to future sales 30% 30% 30% 27,450 28,650 Budgeted ending inventory Budgeted units sales for month 27,000 69,000 90,000 96,000 96,000 117,450 124,650 78.500 27,000 28.800 Required units of available merchandise Actual (or estimated) beginning inventory Budgeted purchases 17.500 91,800 94 050 APPAREL 40.500 37,500 32.500 30% 30% 30% Budgeted sales for next month Ratio of ending inventory to future sales Budgeted ending inventory Budgeted units sales for month 9.750 11.550 11.250 41 000 38.500 34.000 52.550 49.750 43.750 Required units of available merchandise 49.500 11550 10 200 37.150 30.400 Actual (or estimated) beginning inventory Budgeted purchases 3050 During the last week of August, Oneida Company's owner approaches the bank for a $102,500 loan to be made on September 2 and repaid on November 30 with annual interest of 9%, for an interest cost of $2,306. The owner plans to increase the store's inventory by $60,000 during September and needs the loan to pay for inventory acquisitions. The bank's loan officer needs more information about Oneida's ability to repay the loan and asks the owner to forecast the store's November 30 cash position. On September 1, Oneida is expected to have a $4,500 cash balance, $146,300 of net accounts receivable, and $100,000 of accounts payable. Its budgeted sales, merchandise purchases, and various cash payments for the next three months follow. September $ 250,000 225,000 October $ 465,000 210,000 November $ 410,000 197,000 Budgeted Figures Sales Merchandise purchases Cash payments Payroll Rent Other cash expenses Repayment of bank loan Interest on the bank loan 19,500 11,000 35,000 22,150 11,000 29,400 23,700 11,000 21,400 102,500 2,306 Operations began in August: August sales were $190,000 and purchases were $105,000. The budgeted September merchandise purchases include the inventory increase. All sales are on account. The company predicts that 23% of credit sales is collected in the month of the sale, 47% in the month following the sale, 19% in the second month, 7% in the third, and the remainder is uncollectible. Applying these percents to the August credit sales, for example, shows that $89,300 of the $190,000 will be collected in September, $36,100 in October, and $13,300 in November All merchandise is purchased on credit: 50% of the balance is paid in the month following a purchase, and the remaining 50% is paid in the second month. For example, of the $105,000 August purchases. $52,500 will be paid in September and $52,500 in October Required: Prepare a cash budget for September October, and November the remaining 50% is paid in the second month. For example, of the $105,000 August purchases, $52,500 will be paid in September and $52,500 in October. Required: Prepare a cash budget for September, October, and November. Complete this question by entering your answers in the tabs below. Calculation Cash Budget Prepare the calculation of cash receipts from sales and calculation of cash payments for merchandise. Calculation of cash receipts from sales -Collected in- November 30. Total Sales Uncollectible August September October November Accounts Rec. Credit sales from $ 190 000 August September October 250 000 465 000 November 410 000 Totals $ 1,315 000 Calculation of cash payments for merchandise -Paid in November 30. Calculation Cash Budget Prepare the calculation of cash receipts from sales and calculation of cash payments for merchandise. Calculation of cash receipts from sales -Collected in November 30. Total Sales Uncollectible August September October November Accounts Rec. Credit sales from $ 190.000 August September 250,000 s October 465.000 November 410,000 Totals $ 1.315.000 Calculation of cash payments for merchandise --Paid in- November 30, August September October Total Purchases November Accounts Pay. Purchases from: $ 0 105 000 August September 225 000 October 210.000 0 November 197 000 Totals $ 737 000 Cash Budget > Calculation Cash Budget Prepare a cash budget for September, October, and November. (Round your final answers to the nearest whole dollar.) ONEIDA COMPANY Cash Budget November For September, October, and November September October Beginning cash balance S 4,500 Cash receipts Total cash available Cash payments 0 0 Total cash payments Ending cash balance Calculation