Prepare a cash budget for XYZ Company for the first three months of 2004 based on the following information:
Month Estimated Sales Estimated Factory Overhead Estimated Selling, General and Admin Expenses (SG&A)
December $ 2,800,000 $ 320,000 $ 625,000
January 4,000,000 325,000 637,500
February 5,600,000 335,000 642,500
March 4,200,000 335,000 655,000
April 3,500,000 340,000 650,000
Past experience has indicated that 25 percent of sales are collected during the month of the sale and the remaining 75 percent are collected in the month immediately following the sale. Material purchases are 30 percent of the next month's estimated sales and payments lag purchases by one month. Labor costs are 35 percent of next month's sales and are paid during the month incurred. Factory overhead and SG&A expenses are paid during the month they are incurred. The company has purchased a new piece of equipment for $600,000 and is planning to pay for the equipment in February. A tax payment of $750,000 to the IRS is due in January and a dividend payment of $400,000 is due in March.
The company's projected cash balance at the beginning of January is $1.25 million. XYZ Company wants to maintain a minimum cash balance of $750,000 as of the end of each month. As part of the cash budget, determine how much money Beta Company needs to borrow each month or if they will have excess cash to begin repaying their loan.