The objective is to construct a cash budget for a firm.

We learned that just because a firm generates sales and earns a profit does not necessarily ensure there will be adequate cash available in the bank to meet the financial obligations of the firm when the bills are due. This is true of any organization whether it be a for-profit or non-profit organization. This is why in any business venture whether large or small cash is king. Much of what financial managers do is manage the cash of an organization. Therefore, a good working knowledge of cash budgeting is essential for ensuring enough cash is always available to the firm to guarantee it can meet its financial obligations.

• What are the formulas for 4-26 for the three the Excel templates provided below, please explain?

Archer Electronics Cash Budget June July August September Cash receipts Cash payments Net cash flow FORMULA FORMULA FORMULA FORMULA Beginning cash balance FORMULA FORMULA FORMULA Cumulative cash balance FORMULA FORMULA FORMULA FORMULA Monthly borrowing or (repayment) FORMULA Cumulative loan balance FORMULA Marketable securities purchased FORMULA FORMULA FORMULA (sold) FORMULA Cumulative marketable securities FORMULA FORMULA FORMULA Ending cash balance FORMULA FORMULA FORMULA FORMULAArcher Electronics Company's actual sales and purchases for April and May are shown here along with forecasted sales and purchases for June through September. Sales Purchases April (actual) $370,000 $155.000 May (actual) 350,000 145.000 June (forecast) 325,000 145.000 July (forecast) 325,000 205.000 August (forecast) 340.000 225.000 Soplombor (forecast) 380,000 220.000 The company makes 20 percent of its sales for cash and 80 percent on credit. Of the credit sales, 50 percent are collected in the month after the sale and 50 percent are collected two months later. Archer pays for 20 percent of its purchases in the month after purchase and 80 percent two months after. Labor expense equals 15 percent of the current month's sales. Overhead expense equals $12,500 per month, Interest payments of $32,500 are due in June and September. A cash dividend of $52,500 is scheduled to be paid in June. Tax payments of $25,500 are due in June and September. There Is a scheduled capital outlay of $350,000 In September. Archer Electronics's ending cash balance in May is $22,500. The minimum desired cash balance is $10,500. Prepare a schedule of monthly cash receipts, monthly cash payments, and a complete monthly cash budget with borrowing and repayments for June through September. The maximum desired cash balance is $50,500. Excess cash (above $50,500) is used to buy marketable securities. Marketable securities are sold before borrowing funds in case of a cash shortfall (less than $10,500)Solution Problem 4-26 Instructions Archer Electronics Cash Receipts Schedule April May June July Aug Sept Sales Credit sales (80%) FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA Cash sales (20%) FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA Collections (month after sale) 50% FORMULA FORMULA FORMULA FORMULA FORMULA Collections (second month after sale) 50% FORMULA FORMULA FORMULA FORMULA Total cash receipts FORMULA FORMULA FORMULA FORMULA Archer Electronics Cash Payments Schedule April May June July Aug Sept Purchases Payments (month after purchase-20%) FORMULA FORMULA FORMULA FORMULA FORMULA Payments (second month after purchase-80%) FORMULA FORMULA FORMULA FORMULA Labor expense (15% of sales) FORMULA FORMULA FORMULA FORMULA Overhead Interest payments Cash dividend Taxes Capital outlay Total cash payments FORMULA FORMULA FORMULA FORMULA