P1. 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.
JAN FEB MAR APR MAY
Estimated cash balance at the beginning of the period
Estimated cash inflows:
Cash Sales (25%) 16,250 15,000 17,500 25,000 25,000
Collection on credit sales - 34,125 46,125 50,250 68,250
Bank loans 10,375 12,250 16,750
Total Inflows 16,250 49,125 74,000 87,500 110,000
Estimated cash outflows
Payment for inventories (75%) 48,750 45,000 52,500 75,000
Payment of sales and commissions (20%) 13,000 12,000 14,000 20,000 20,000
Payment of other cash outflows 10,000 ...
This solution is comprised of a detailed explanation to develop the cash budget for Longhorn Casing, Inc. in excel file.