Prepare a cash budget for 2016 based on the following information: Cash Receipts
ID: 2763326 • Letter: P
Question
Prepare a cash budget for 2016 based on the following information:
Cash Receipts
25% of sales are in cash and collected in the month the sale is made and 75% are on credit and collected in the month after the sale. December 2015 sales were $3 million. The sales forecast for 2016 is as follows:
January
February
March
April
May
June
2,800,000
2,500,000
3,200,000
3,400,000
3,600,000
3,600,000
July
August
September
October
November
December
3,800,000
4,000,000
3,600,000
3,400,000
2,800,000
2,600,000
Cash Disbursements
The disbursements for this firm are as follows:
Payments for raw materials are calculated based on 50% of current month sales.
Operating expenses are $700,000 per month.
Corporate overhad is $100,000 per month.
$1.5 million semi-annual bond interest payments are due in May and November.
$1.8 million in new equipment will arrive in May and must be paid for in July.
$1.2 million in new equipment will arrive in September and must be paid for in October.
A $1.4 million insurance premium is due in February.
$900,000 quarterly real estate tax payments are due in February, May, August, and November
Cash/Credit Line Information
The company must have a minimum cash balance of $200,000 at month-end and the cash balance at the end of December 2015 is $750,000. The company maintains a $1 million credit line for short-term liquidity purposes, none of which is outstanding at the end of December 2015. DO NOT include interest expense on the credit line in your calculations.
January
February
March
April
May
June
2,800,000
2,500,000
3,200,000
3,400,000
3,600,000
3,600,000
July
August
September
October
November
December
3,800,000
4,000,000
3,600,000
3,400,000
2,800,000
2,600,000
Explanation / Answer
January February March April May June July August September October November December Sales 28,00,000 25,00,000 32,00,000 34,00,000 36,00,000 36,00,000 38,00,000 40,00,000 36,00,000 34,00,000 28,00,000 26,00,000 Cash Budget January February March April May June July August September October November December Collection Cash Sales 7,00,000 6,25,000 8,00,000 8,50,000 9,00,000 9,00,000 9,50,000 10,00,000 9,00,000 8,50,000 7,00,000 6,50,000 (25% of Sales) Collection of Credit sale 22,50,000 21,00,000 18,75,000 24,00,000 25,50,000 27,00,000 27,00,000 28,50,000 30,00,000 27,00,000 25,50,000 21,00,000 (75% of Previous Month) Withdrwal from Credit Limit 3,25,000 8,50,000 7,00,000 7,50,000 29,50,000 30,50,000 26,75,000 32,50,000 43,00,000 36,00,000 43,50,000 38,50,000 39,00,000 35,50,000 40,00,000 27,50,000 Payment Payment for Raw material 14,00,000 12,50,000 16,00,000 17,00,000 18,00,000 18,00,000 19,00,000 20,00,000 18,00,000 17,00,000 14,00,000 13,00,000 Operating Exp 7,00,000 7,00,000 7,00,000 7,00,000 7,00,000 7,00,000 7,00,000 7,00,000 7,00,000 7,00,000 7,00,000 7,00,000 Coperate Overhead 1,00,000 1,00,000 1,00,000 1,00,000 1,00,000 1,00,000 1,00,000 1,00,000 1,00,000 1,00,000 1,00,000 1,00,000 interest on Bond 15,00,000 15,00,000 pur of equipment 18,00,000 12,00,000 insurance premium 14,00,000 Real estate payment tax 9,00,000 9,00,000 9,00,000 9,00,000 Payment of credit limit 2,75,000 50,000 8,50,000 1,50,000 5,50,000 6,50,000 22,00,000 43,50,000 26,75,000 25,50,000 50,00,000 34,50,000 45,00,000 38,50,000 31,50,000 37,00,000 46,00,000 27,50,000 Increase/(decrease) in Cash 7,50,000 -13,00,000 - 7,00,000 -7,00,000 1,50,000 -1,50,000 - 7,50,000 -1,50,000 -6,00,000 - Opening Cash Balance 7,50,000 15,00,000 2,00,000 2,00,000 9,00,000 2,00,000 3,50,000 2,00,000 2,00,000 9,50,000 8,00,000 2,00,000 Closing Cash balance 15,00,000 2,00,000 2,00,000 9,00,000 2,00,000 3,50,000 2,00,000 2,00,000 9,50,000 8,00,000 2,00,000 2,00,000