The Wine Depot is contemplating several alternative means of financing their annual acquisition of $70,000 in equipment. One option is to borrow $300,000 from a local bank for 5 years at 11 percent per annum. The bank has asked them to produce a 4-year cash budget broken down by year (Year 1 through Year 4). Sales of $650,000 are expected in year 1, with sales increasing each year thereafter by 10 percent. Sales in the previous year were $600,000. Purchases are based on an expected cost of sales of 30 percent and a required ending inventory of 10 percent of next years sales. Purchases in the previous year were $200,000, and beginning inventory was $32,000. Annual expenses include advertising expense of $10,000, marketing expense of $6,000, depreciation expense of $8,000, interest expense of $35,000, salaries expense of $250,000, wages expense of $65,000, supplies expense of $7,500, and utilities expense of $10,000. All expenses except depreciation are paid in the year in which they are incurred and are expected to increase 5 percent each year. Collections in the year of sale are expected to be 92 percent, with the remaining 8 percent collected in the next year. Payments in the year of purchase are expected to be 93 percent, with the remaining 7 percent paid in the next year. Proceeds from the $300,000 loan are expected in year 1 and $75,000 of facilities will be purchased each year. Proceeds from expected equipment sales each year are expected to amount to $10,000. Annual payments of $81,171 on the loan also begin in year 1.

The beginning cash balance in year 1 was $20,000.

Using the ch6-05 file to start your work, create a cash budget (as you did in the chapter) based on the assumptions just provided. Use Excels grouping feature to group operating cash receipts, operating cash payment, cash from (to) operating activities, cash from (to) investing activities, and cash from (to) financing activities.

Wine Depot Cash Budget 2015 2016 2017 2018 Operatings Operating cash recept Product sales revenue Collections in the year of wale Collection in the year following sale Operating cash receipts Operating cash payments Purchases Cost of expected wales Required ending inventory Beginning tory Total Purchase Payments in the year of purchase Payment in the year following purchase Cash payments for purchases 10.500 6.300 36.750 10.000 6.000 35.000 250.000 65.000 7500 10.000 11.025 6.615 38.568 275 625 71.663 8.269 11025 422.809 11.576 6946 40.517 269 406 75.246 262.500 68.250 7.875 10.500 8.682 11.576 443949 383 500 402.675 Advertising expense Marketing expende Interest expense Series expense Wages expense Supplies expense Us expense Total Expenses Operating cosh payments Cash from (te) perting activities Investing activities Facility purchases Equipment les Cash from (te) investing activities Financing activities La precede Leon payments Cash from (te) financing activities Change in cash Begge h Ending cash Assumptions First year wales Sales growth each year Sales in 2014 Purchases in 2014 650.000 10% 600 DOO 200.000 93% 10% 32.000 30% 20.000 10.000 Expense growth each year Payments in the year of purchase Payments in the year following purchase Collections in the year of sole Collections in the year following sele Required ending inventory Beginning inventory Cost of expected sales Beginning och Advertising expense Marketing expense Depreciation expense Latest expense Series expense Wapende Sus expense Unexpense Leon prevede Annullon payment Anul fost purchase Annelementales 6.000 8.000 35.000 250.000 65.000 7.500 10.000 300.000 81.171 75.000 10.000