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.© BrainMass Inc. brainmass.com June 3, 2020, 7:41 pm ad1c9bdddf
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.