Make a cash budget, cash inflows outflows from the information I've attached and analysis of the problem and recommendations. The case is stated below:

Carnevale Winery Part 2

Philip and Brenda Carnevale were doing something that they had never done before, creating a cash budget. This process was something their banker had asked them to do before coming in to talk to her about their current line of credit with the bank. Current Situation

Last year had been both a good and bad year for the Carnevale family winery. 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, their banker set up a line of credit of $75,000 which the Carnevale Winery used up within three months. Fortunately, that was enough to get the winery to the point where it was generating sufficient cash, but the Carnevales did not want to be surprised like that again.

The banker, Cindy Gonzalez, had suggested that the Carnevales wait to start paying back the line of credit until the upcoming year, to make sure the winery didnt run into another cash shortage before Christmas. She also suggested that the couple should create a cash budget for the year so they could sit down together and see how best to address the winerys cash needs. She even gave them a nice pamphlet showing the basics of how to create a cash budget.

Creating the Cash Budget

Brenda, the financial manager for the winery, gathered up everything she would need, looked over her records, and starting putting together the cash budget. Probably the most important thing was estimating the sales for the year as that determined how much cash she could expect to have coming into the winery each month. From her records she 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 $479,780 and for December were $577,362.

As far as expenses for the winery, Brenda 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. The only thing not included in the previous year was the line of credit since that was new. Since this was new and she wasnt sure how to deal with it, Brenda split the amount due on the line of credit into six payments, including interest, so the line of credit would be paid off in June. Cindy had told Brenda that the line of credit had to be paid off within a year after it was started and couldnt be used again for at least 30 days after it was paid off and would probably require the bank to write a new agreement as this was the first time the winery had acquired one.

Determining Cash Needs

Brenda liked to have $120,000 in the bank as a cash balance and she had just a little bit more than that to start the year ($124,987). She also knew that she had $197,000 in outstanding bills for the month of December which needed to be paid in January as all purchases are paid for within thirty days. She and Philip were hoping to see what caused the cash problem last year as the income statement showed the firm had a nice 6.5% net profit. This figure is a little lower than industry average, but they felt with the new pricing structure they were developing for the wines that this should improve. But Cindy told them 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 firms borrowing needs reasonable.

6% January $271,062 5% February $225,885 4% March $180,708 Carnevale Estimates for Next Year 8% 7% 7% 7% 9% April May June July August $361,416 $316,239 $316,239 $316,239 $406,593 10% September $451,770 15% 14% 8% 100.0% October November December TOTAL $677,655 $632,478 $361,416 $4,517,700 Sales Monthly Purchases $165,000 $195,000 $185,000 $170,000 $115,000 $140,000 $148,000 $145,000 $365,000 $310,000 $254,000 $205,000 $2,397,000 Utilities $3,900 $3,900 $4,000 $4,200 $4,200 $4,200 $4,400 $4,400 $4,300 $4,200 $4,000 $4,100 $49,800 Marketing $23,000 $27,000 $25,000 $25,000 $29,000 $32,000 $33,000 $33,000 $29,000 $34,000 $36,000 $39,000 $365,000 Insurance $8,300 $8,300 $8,300 $8,300 $8,300 $8,300 $8,300 $8,300 $8,300 $8,300 $99,600 $8,300 $39,000 $8,300 $36,000 Salaries and Benefits $24,000 $25,500 $21,000 $28,000 $32,000 $37,000 $38,000 $38,000 $34,000 $40,000 $392,500 E-commerce $4,000 $5,000 $4,500 $4 $4,500 $4,500 $5,000 $5,000 $5,000 $5,000 $4,500 $5,000 $7,000 $7,500 $61,500 Repairs and Maintenance $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $72,000 Travel $4,100 $5,000 $3,000 $7,000 $6,000 $2,500 $2,500 $3,000 $2,500 $5,500 $6,500 $5,000 $52,600 Supplies $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $48,000 Interest $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $222,000 Loan Payments $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $273,000 Line of Credit Payment $83,750 $15,125 $3,000 $14,687 $3,000 $14,250 $3,000 $13,813 $3,000 $13,375 $3,000 $12,500 $3,000 Other $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $36,000 6% January $271,062 5% February $225,885 4% March $180,708 Carnevale Estimates for Next Year 8% 7% 7% 7% 9% April May June July August $361,416 $316,239 $316,239 $316,239 $406,593 10% September $451,770 15% 14% 8% 100.0% October November December TOTAL $677,655 $632,478 $361,416 $4,517,700 Sales Monthly Purchases $165,000 $195,000 $185,000 $170,000 $115,000 $140,000 $148,000 $145,000 $365,000 $310,000 $254,000 $205,000 $2,397,000 Utilities $3,900 $3,900 $4,000 $4,200 $4,200 $4,200 $4,400 $4,400 $4,300 $4,200 $4,000 $4,100 $49,800 Marketing $23,000 $27,000 $25,000 $25,000 $29,000 $32,000 $33,000 $33,000 $29,000 $34,000 $36,000 $39,000 $365,000 Insurance $8,300 $8,300 $8,300 $8,300 $8,300 $8,300 $8,300 $8,300 $8,300 $8,300 $99,600 $8,300 $39,000 $8,300 $36,000 Salaries and Benefits $24,000 $25,500 $21,000 $28,000 $32,000 $37,000 $38,000 $38,000 $34,000 $40,000 $392,500 E-commerce $4,000 $5,000 $4,500 $4 $4,500 $4,500 $5,000 $5,000 $5,000 $5,000 $4,500 $5,000 $7,000 $7,500 $61,500 Repairs and Maintenance $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $72,000 Travel $4,100 $5,000 $3,000 $7,000 $6,000 $2,500 $2,500 $3,000 $2,500 $5,500 $6,500 $5,000 $52,600 Supplies $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $48,000 Interest $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $18,500 $222,000 Loan Payments $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $22,750 $273,000 Line of Credit Payment $83,750 $15,125 $3,000 $14,687 $3,000 $14,250 $3,000 $13,813 $3,000 $13,375 $3,000 $12,500 $3,000 Other $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $36,000