Problem:
Longhorn Casing, Inc. supplies pipe and other materials to energy companies. It has actual sales of $65,000 in January and $60,000 in February. It expects sales of $70,000 in March, $100,000 in April, and $100,000 in May. One quarter (25%) of these sales are cash.
Of the credit sales, 70% are collected one month after the sale. The remaining 30% are collected two months after the sale. Each month the firm purchases inventory equal to 75% of the expected sales and pays for the purchases the following month. Salaries and commissions equal 20% of sales and other cash outflows total $10,000 per month. The cash balance as of May 31 was $5,000. The firm wishes to maintain a $5,000 minimum balance. Develop the cash budget for the months of March, April, and May.
Note: To receive full credit, you must show your work in detail. You may perform your calculations in an Excel spreadsheet and attach the results below.