Question

• You need to do a cash flow forecast for Viggio for the next six months
• From this cash flow see if Viggio may need to reuse the line of credit that the bank has set up for him.
• If he does need to use it, how much of it will he need to borrow
• If he does not need to use it, can you suggest a reason why he may have run out of cash the previous year.

Louis Viggio was doing something he had never done before, creating a cash budget. This process was something his banker had asked him to do before coming in to talk to her about his current line of credit with the bank.

Current Situation

Last year had been both a good and bad year for Louis. Sales were higher than they had ever been, but in spite of that the winery had run into a cash crunch in June. With the need for cash being immediate, his banker set up a line of credit of $25,000 which he used up within three months. Fortunately, that was enough to get the winery to the point where it was generating sufficient cash, but he did not want to be surprised like that again.

The banker, Cindy Gonzalez, had suggested Louis should create a cash budget for the next six months so they could sit down together and see if the winery would need more cash. She even gave him a nice pamphlet showing the basics of how to create a cash budget.

Creating the Cash Budget

Louis gathered up everything he would need, looked over the records, and starting putting together the cash budget. Probably the most important thing was estimating the sales as that determined how much cash he could expect to have coming into the winery each month. From his records he could see that only about 15% of all sales in any month were made through the tasting room and were essentially cash sales. Of all the sales made on credit, only 5% were paid for in the same month the sale was made, 49% were paid for the next month, and 42% were paid for during the following month. Total sales for the winery for November were $158,780 and for December were $205,362.

As far as expenses for the winery, Louis looked at the expenses for the previous year on a monthly basis and used those as a guide for what the upcoming expenses would be. (His pro-forma statement of sales and expenses can be found on the next page)

Determining Cash Needs

Louis liked to have $60,000 in the bank as a cash balance and he had just a little bit more than that to start the year ($64,987). He also knew that he had $129,378 in outstanding bills for the month of December which needed to be paid in January as all purchases are paid for within thirty days. He was hoping to see what caused the cash problem last year as the income statement showed the firm had a nice 6.5% net profit. But Cindy had told him that it is not unusual for firms to show a profit, but run out of cash at times, and it is was important to determine both why and how to try to deal with it so as to keep the firm’s borrowing needs reasonable.

January $73,305 February $58,644 Viggio Estimates for Next Year March April May $58,644 $87,966 $97,740 June $112,401 TOTAL $488,700 Sales Monthly Purchases $46,182 $36,946 $36,946 $55,419 $61,576 $307,881 $70,813 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $60,000 Mortgage payment Rent for storage area $2,000 $2,000 $2,000 $2,000 $12,000 $2,000 $2,000 $2,000 $2,200 Utilities $1,800 $1,500 $2,000 $2,300 $11,800 Marketing $2,500 $2,100 $2,000 $2,200 $3,000 $2,500 $14,300 Insurance $700 $700 $700 $700 $700 $700 $4,200 Salaries and Benefits $5,000 $5,600 $5,000 $5,600 $6,400 $6,900 $34,500 Internet Website Maintenance $500 $500 $500 $500 $500 $500 $3,000 Repairs and Maintenance $500 $500 $500 $500 $500 $500 $3,000 Travel $1,800 $800 $1,500 $1,000 $800 $800 $6,700 Supplies $400 $400 $400 $400 $400 $400 $2,400 $2,275 $2,275 $2,275 $2,275 $2,275 $2,275 $13,650 Loan Payments Tax Payments $500 $560 $500 $560 $640 $690 $3,450 Annual Tax Payment $35,000 $300 $35,000 $1,800 Misc. $300 $300 $300 $300 $300

Answer 1

Sales Realisation Budget
Month	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Total Sale	158,780.00	205,362.00	73,305.00	58,644.00	58,644.00	87,966.00	97,740.00	112,401.00
Cash Sales 15% of Current month sales	23,817.00	30,804.30	10,995.75	8,796.60	8,796.60	13,194.90	14,661.00	16,860.15
Credit Sales	134,963.00	174,557.70	62,309.25	49,847.40	49,847.40	74,771.10	83,079.00	95,540.85
Realisation of 5% in current month	6,748.15	8,727.89	3,115.46	2,492.37	2,492.37	3,738.56	4,153.95	4,777.04
Realisation of 49% in Next month		66,131.87	85,533.27	30,531.53	24,425.23	24,425.23	36,637.84	40,708.71
Realisation of 42% in Following month			56,684.46	73,314.23	26,169.89	20,935.91	20,935.91	31,403.86
Cash Budget
Particular	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Opening Cash Balance			64,987.00	60,000.00	60,000.00	60,000.00	60,000.00	60,000.00
Recipts
Cash Sales 15% of Current month sales	23,817.00	30,804.30	10,995.75	8,796.60	8,796.60	13,194.90	14,661.00	16,860.15
Realisation of 5% in current month	6,748.15	8,727.89	3,115.46	2,492.37	2,492.37	3,738.56	4,153.95	4,777.04
Realisation of 49% in Next month	-	66,131.87	85,533.27	30,531.53	24,425.23	24,425.23	36,637.84	40,708.71
Realisation of 42% in Following month	-	-	56,684.46	73,314.23	26,169.89	20,935.91	20,935.91	31,403.86
Total Receipts	30,565.15	105,664.06	156,328.95	115,134.74	61,884.08	62,294.59	76,388.70	93,749.76
Payments:
Outsatnding bills for purchase as in 30 days			129,378.00	46,182.00	36,946.00	36,946.00	55,419.00	61,576.00
Mortgage Payment			10,000.00	10,000.00	10,000.00	10,000.00	10,000.00	10,000.00
Rent for Storage			2,000.00	2,000.00	2,000.00	2,000.00	2,000.00	2,000.00
Utilities			2,000.00	2,200.00	1,800.00	1,500.00	2,000.00	2,300.00
Marketing			2,500.00	2,100.00	2,000.00	2,200.00	3,000.00	2,500.00
Insurance			700.00	700.00	700.00	700.00	700.00	700.00
Salaries and Benefits			5,000.00	5,600.00	5,000.00	5,600.00	6,400.00	6,900.00
Internet Website Maintenance			500.00	500.00	500.00	500.00	500.00	500.00
Repair and Maintenance			500.00	500.00	500.00	500.00	500.00	500.00
Travel			1,800.00	800.00	1,500.00	1,000.00	800.00	800.00
Supplies			400.00	400.00	400.00	400.00	400.00	400.00
Loan Paymet			2,275.00	2,275.00	2,275.00	2,275.00	2,275.00	2,275.00
Tax Payment			500.00	560.00	500.00	560.00	640.00	690.00
Annual Tax Payment						35,000.00
Misc			300.00	300.00	300.00	300.00	300.00	300.00
Total Payments			157,853.00	74,117.00	64,421.00	99,481.00	84,934.00	91,441.00
Closing Balance			60,000.00	60,000.00	60,000.00	60,000.00	60,000.00	60,000.00
Access/(Deficit)			3,462.95	41,017.74	(2,536.92)	(37,186.41)	(8,545.30)	2,308.76