Question: For the problem below, A cash budget has been created using the data from the problem. Please explain which formulas were used in excel to create the cash budget for this problem. Please show formulas in excel for the cash budget.

Westex Products is a wholesale distributor of industrial cleaning products. When the treasurer of Westex Products approached the companys bank late in the current year seeking short-term financing, he was told that money was very tight and that any borrowing over the next year would have to be supported by a detailed statement of cash collections and disbursements. The treasurer also was told that it would be very helpful to the bank if borrowers would indicate the quarters in which they would be needing funds, as well as the amounts that would be needed, and the quarters in which repayments could be made.

Because the treasurer is unsure as to the particular quarters in which bank financing will be needed, he has assembled the following information to assist in preparing a detailed cash budget:

a.Budgeted sales and merchandise purchases for next year, as well as actual sales and purchases for the last quarter of the current year, are:

Sales Merchandise Purchases Current Year: Fourth quarter actual $200,000 $126,000 Next year: First quarter estimated $300,000 $186,000 Second quarter estimated $400,000 $246,000 Third quarter estimated $500,000 $305,000 Fourth quarter estimated $200,000 $126,000

b. All sales are on account. The company normally collects 65% of a quarters sales before the quarter ends and another 33% in the following quarter. The remainder is uncollectible. This pattern of collections is now being experienced in the current years fourth-quarter actual data.

c.Eighty percent of a quarters merchandise purchases are paid for within the quarter. The remainder is paid for in the following quarter.

d.Selling and administrative expenses for next year are budgeted at $50,000 per quarter plus 15% of sales. Of the fixed amount, $20,000 each quarter is depreciation.

e. The company will pay $10,000 in dividends each quarter. f.Land purchases of $75,000 will be made in the second quarter, and purchases of $48,000 will be made in the third quarter. These purchases will be for cash.

g.The Cash account contained $10,000 at the end of the current year. The treasurer feels that this represents a minimum balance that must be maintained.h.The company has an agreement with a local bank that allows the company to borrow in increments of $1,000 at the beginning of each quarter, up to a total loan balance of $100,000. The interest rate on these loans is 2.5% per quarter and for simplicity,we will assume that interest is not compounded. The company would, as far as it is able, repay the loan plus accumulated interest at the end of the year.

i. At present, the company has no loans outstanding.

Third Fourth WESTEX PRODUCTS Schedule of Expected Cash Collections First Second 66000 195000 99000 260000 Current Year-Fourth quarter sales Next Year-First quarter sales Next Year-Second quarter sales Next Year-Third quarter sales Next Year-Fourth quarter sales Total cash collections 132000 325000 Total 66000 294000 392000 490000 130000 1372000 165000 130000 295000 261000 359000 457000 Answer 2. WESTEX PRODUCTS Schedule of Budgeted Cash Disbursements for Merchandise Purchases First Second Third Fourth Current Year-Fourth quarter purchases 25200 Next Year-First quarter purchases 148800 37200 Next Year-Second quarter purchases 196800 49200 Next Year-Third quarter purchases 244000 61000 Next Year-Fourth quarter purchases 100800 Total cash disbursements 174000 234000 293 200 161800 Total 25200 186000 246000 305000 100800 863000 Answer 3. Expected Cash Payments for Operating Expenses First Second Third Budgeted sales in dollars 300000 400000 500 000 Variable selling and administrative expense rate 15% 15% 15% Variable selling and administrative expense 45000 60000 75000 Fixed selling and administrative expense 50000 50000 50000 Total selling and administrative expense 95000 110000 125000 Less: Depreciation 20000 20000 20000 Cash disbursements for Selling and administrative 75000 90000 105000 Fourth 200000 15% 30000 50000 80000 20000 60000 Year 1400000 15% 210000 200000 410000 80000 330000 Answer 4. WEST EX PRODUCTS Cash Budget First Second 10000 1 2000 261000 359000 271000 371000 Third 10000 457000 467000 Fourth 10800 295000 305800 Year 10000 1372000 1382000 863000 174000 75000 10000 Beginning cash balance Add collections from customers Total cash available Less disbursements: Merchandise purchases Selling and administrative expenses Dividends Equipment Total Cash disbursements Excess (deficiency) of cash available over disbursements Financing: Borrowing Repayments Interest Total Financing Ending cash balance 234000 90000 10000 75000 409000 293 200 105000 10000 48000 456200 161800 60000 10000 330000 40000 123000 1356000 259000 231800 12000 -38000 10800 74000 26000 48000 -48000 48000 48000 -3600 -3600 0 48000 0 -3600 -51600 22400 12000 10000 10800 22400 - Interest Expense = $48,000 * 2.50% *3 Interest Expense = $3,600