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 year's 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 \begin{tabular}{|c|c|c|c|c|} \hline \multicolumn{5}{|l|}{ Wine Depot } \\ \hline Cash Budget & Year 1 & Year 2 & Year 3 & Year 4 \\ \hline & & & & \\ \hline Operating activities & & & & \\ \hline Operating cash receipts & & & & \\ \hline Product sales revenue & & & & \\ \hline Collections in the year of sale & & & & \\ \hline Collections in the year following sale & & & Hi & \\ \hline Operating cash receipts & & & & \\ \hline Operating cash payments & & & & \\ \hline Purchases & & & & \\ \hline Cost of expected sales & & & & \\ \hline Required ending inventory & & & & \\ \hline Beginning inventory & & & & \\ \hline Purchases & & & & \\ \hline Payments in the year of purchase & & & & \\ \hline Payments in the year following purchase & & & & \\ \hline Cosh payments for purchases & & & & \\ \hline Expenses & & & & \\ \hline Advertising expense & & & & \\ \hline Marketing expense & & & & \\ \hline Interest expense & & & & \\ \hline Salaries expense & & & & \\ \hline Woges expense & & & & \\ \hline Supplies expense & & & & \\ \hline Utilities expense & & & & \\ \hline Expenses & & & & \\ \hline 3 Operating cash payments & & & & \\ \hline Cosh from (to) operating activities & & & & \\ \hline Investing activities & & & & \\ \hline Facility purcheses & & & & \\ \hline Equipment sales & & & & \\ \hline Cosh from (to) investing octivities & & & & \\ \hline \end{tabular} 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 Excel's 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. Save your file as ch6-05_student_name (replacing student_ name with your name). Define names as appropriate. 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 year's 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 \begin{tabular}{|c|c|c|c|c|} \hline \multicolumn{5}{|l|}{ Wine Depot } \\ \hline Cash Budget & Year 1 & Year 2 & Year 3 & Year 4 \\ \hline & & & & \\ \hline Operating activities & & & & \\ \hline Operating cash receipts & & & & \\ \hline Product sales revenue & & & & \\ \hline Collections in the year of sale & & & & \\ \hline Collections in the year following sale & & & Hi & \\ \hline Operating cash receipts & & & & \\ \hline Operating cash payments & & & & \\ \hline Purchases & & & & \\ \hline Cost of expected sales & & & & \\ \hline Required ending inventory & & & & \\ \hline Beginning inventory & & & & \\ \hline Purchases & & & & \\ \hline Payments in the year of purchase & & & & \\ \hline Payments in the year following purchase & & & & \\ \hline Cosh payments for purchases & & & & \\ \hline Expenses & & & & \\ \hline Advertising expense & & & & \\ \hline Marketing expense & & & & \\ \hline Interest expense & & & & \\ \hline Salaries expense & & & & \\ \hline Woges expense & & & & \\ \hline Supplies expense & & & & \\ \hline Utilities expense & & & & \\ \hline Expenses & & & & \\ \hline 3 Operating cash payments & & & & \\ \hline Cosh from (to) operating activities & & & & \\ \hline Investing activities & & & & \\ \hline Facility purcheses & & & & \\ \hline Equipment sales & & & & \\ \hline Cosh from (to) investing octivities & & & & \\ \hline \end{tabular} 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 Excel's 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. Save your file as ch6-05_student_name (replacing student_ name with your name). Define names as appropriate