Question

• Cash flow statements are the most complicated of the statements small businesses must create, but they are very important to help identify if and when a firm may need a loan.
• 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.

Viggio Wines
Part 2

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.

Viggio Estimates for Next Year January $73,305 February $58,644 March $58,644 April $87,966 May $97,740 June $112,401 TOTAL $488,700 Sales Monthly Purchases $46,182 $36.946 $36,946 $55.419 $61.576 $70.813 $307,881 Mortgage payment $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $60,000 Rent for storage area $2,000 $2,000 $2,000 $2,000 $2,000 $2,000 $12,000 Utilities $2,000 $2,200 $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 $4,200 $700 $6,400 Salaries and Benefits $5,000 $5,600 $5,000 $5,600 $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 $2,400 Supplies $400 $400 $400 $400 $400 $400 Loan Payments $2,275 $2,275 $2,275 $2,275 $2,275 $13,650 $2,275 $560 Tax Payments $500 $500 $560 $640 $690 $3,450 Annual Tax Payment $35,000 $35,000 Misc. $300 $300 $300 $300 $300 $300 $1,800

Answer 1

Viggio
Sales collections	Jan.	Feb.	Mar.	Apr.	May	Jun	Total
Sales budgeted	73305	58644	58644	87966	97740	112401	488700
Cash sales(15%*sales)	10995.75	8796.6	8796.6	13194.9	14661	16860.15	73305
Credit sales of:
Nov.(158780*85%*42%)	56684.46						56684.46
Dec (205362*85*49%;42%)	85533.27	73314.23					158847.5
Jan (73305*85%*5%;49%;42%)	3115.46	30531.53	26169.89				59816.88
Feb.(58644*85%*5%;49%;42%)		2492.37	24425.23	20935.91			47853.5
Mar.(58644*85%*5%;49%;42%)			2492.37	24425.23	20935.91		47853.5
Apr.(87966*85%*5%;49%;42%)				3738.56	36637.84	31403.86	71780.26
May(97740*85%*5%;49%)					4153.95	40708.71	44862.66
Jun. (112401*85%*5%)						4777.043	4777.043
Total sales collections	156328.9	115134.7	61884.08	62294.59	76388.7	93749.76	565780.8

Cash Budget for the six months
	Jan.	Feb.	Mar.	Apr.	May	Jun	Total
Beg.Balance	64987	63463	104481	101944	64757	56212	64987
Add:sales collections	156329	115135	61884	62295	76389	93750	565781
Total cash available	221316	178598	166365	164238	141146	149962	630768
Less: Payments for:
Purchases(1mth credit)	129378	46182	36946	36946	55419	61576	366447
Mortgage pmt.	10000	10000	10000	10000	10000	10000	60000
Rent for storage area	2000	2000	2000	2000	2000	2000	12000
Utilities	2000	2200	1800	1500	2000	2300	11800
Marketing	2500	2100	2000	2200	3000	2500	14300
Insurance	700	700	700	700	700	700	4200
Salaries & benefits	5000	5600	5000	5600	6400	6900	34500
Internet	500	500	500	500	500	500	3000
Rep.& maint.	500	500	500	500	500	500	3000
Travel	1800	800	1500	1000	800	800	6700
Supplies	400	400	400	400	400	400	2400
Loan pmt.	2275	2275	2275	2275	2275	2275	13650
Tax pmt.	500	560	500	560	640	690	3450
Annual tax pmt.				35000			35000
Misc.	300	300	300	300	300	300	1800
Total cash disbursements	157853	74117	64421	99481	84934	91441	572247
Surplus/(Deficit)--End. Balance	63463	104481	101944	64757	56212	58521	58521