Need help filling highlighted parts of excel spreadsheet
The following table lists the data from the budget of Lewellen Products. 35% the company's sales will be collected in the month of the sale; the other 65% will be collected in the following month. The company pays all its credit purchase with a one-month delay. Credit purchases in June were $206,300, and total sales in June were $419,000. Lewellen Products Sales, purchase, and other budgets June July August September Total sales $419,000 $485,000 $510,000 $565,000 Purchases of material For cash 135,000 103,000 95,000 For credit 206,300 210,800 278,300 296,700 Selling and admin. expenses 42,700 56,500 63,800 Income taxes 20,000 20,000 20,000 Dividends 15,000 15,000 15,000 Equipment purchase 108,000 83,800 0 We make these assumptions to simplify the analysis: * Lewellen Products has an open line of credit with its bank, which can be used as needed to bolster the cash position. * The company desires to maintain a $70,000 minimum cash balance at the end of each month. Therefore, borrowing must be sufficient to cover the cash shortfall and to provide for the minimum cash balance of $70,000. The beginning cash balance for July is $100,000. * All borrowings and repayments must be in multiples of $1,000 amounts, and interest is 3 percent per annum. Interest is computed and paid on the principal during the borrowing period. * All borrowings take place at the beginning of a month, and all repayments are made at the end of a month. The company will take a loan at the beginning of July, repay some portion of the loan at the end of August, and should be able to pay off the remaining balance of the loan at the end of September. You have to figure out the amount of loan taken at the beginning of July. * The company pays the selling and administrative expenses, income taxes, dividends, and pays for equipment purchase at the end of a month. Lewellen Products Sales, purchase, and other budgets June July 419,000 485,000 August 510,000 September 565,000 Lewellen Products Cash Budget For the Month Ended September 30, 2020 Month July August September $100,000 206,300 Total sales Purchases of material For cash For credit Selling and admin. expenses Income taxes Dividends Equipment purchase 135,000 210,800 42,700 20,000 15,000 108,000 103,000 278,300 56,500 20,000 15,000 83,800 95,000 296,700 63,800 20,000 15,000 0 Cash balance, beginning Add: Receipts: Collections from customers Total cash available (a) 442,100 $542,100 Less: $341,300 42,700 108,000 15,000 20,000 $527,000 September Accounts receivable, 6/30/2020 July sales ($485,000) August sales ($510,000) September sales ($565,000) Total cash collections Schedule of Expected Cash Collections July August 272,350 169,750 315,250 178,500 Disbursements: Direct materials Selling and admin. Equipment purchase Dividends Income tax Total disbursements (b) Minimum cash balance Total cash needed [c] Cash surplus (deficit) (a) - (c) Finance: Borrowing Repayment Interest Total effect of financing (d) Cash balance, ending [(a)-(b) + (d)] 331,500 197,750 529,250 442,100 493,750 September Accounts payable, 6/30/2020 July purchases August purchases September purchases Total cash disbursement Schedule of Expected Cash Payments July August 206,300 135,000 210,800 103,000 278,300 95,000 373,300 341,300 313,800
