Kelly's Boutique is contemplating several means of financing their acquisition of $200,000 in special equipment. One alternative is to borrow $200,000 from a local bank for 10 years at 12 percent per annum. The bank has asked them to produce a 1-year cash budget broken down by quarters. Sales of $40,000 are expected in the first quarter, with each quarter thereafter increasing 2 percent. Purchases are based on an expected cost of sales of 55 percent and a required ending inventory of 70 percent of next quarter's cost of sales. Beginning inventory was $11,000. Sales for the first quarter next year are expected to be $50,000. Sales in the previous year's fourth quarter were $28,000. Sales in the previous year's third quarter were $30,000. Expenses include advertising expense of $900, depreciation expense of $800, interest expense of $1,000, payroll expense of $8,000, supplies expense of $500, and utilities expense of $600 per quarter throughout the year. All expenses except depreciation are paid in the quarter during which they are incurred. Collections in the quarter of sale are expected to be 80 percent, collections in the first quarter following a sale 15 percent, and in the second quarter 5 percent. Payments in the quarter of purchase are expected to be 85 percent, payments in the first quarter following a purchase 10 percent, and payments in the second quarter to be 5 percent. Purchases in the previous year's fourth quarter were $20,000. Purchases in the previous year's third quarter were $17,000. Proceeds from the $200,000 loan are expected in the second quarter and $200,000 of equipment will be purchased in the third quarter. Quarterly payments of $4,200 on the loan also begin in the third quarter. The beginning cash balance in the first quarter was $15,000. Collections in the 1st quarter following a sale Collections in the 2nd quarter following a sale Previous years 4 th quarter sales Previous years 3rd quarter sales Cost of expected sales Required ending inventory Beginning inventory Payments in the quarter of purchase Payments in the 1st quarter following a purchase Payments in the 2nd quarter following a purchase Purchases in the previous year's 4th quarter Purchases in the previous year's 3rd quarter Beginning cash Next year's 1st qtr. forecasted sales Advertising expense Depreciation expense Interest expense Payroll expense Supplies expense Utilities expense Loan proceeds Loan payment Equipment purchase Using the ch6-04 file to start your work, create a cash budget (as you did in the chapter) that is based on the assumptions listed in the previous paragraph. 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 and also to group the four quarterly columns together. Save your file as ch6-04_student_name (replacing student_name with your name). Define names as appropriate. Clipboard is A8 fx Collections in the 2 nd quarter following a sale A B c D F G H I J k Utilities expense Expenses Operating cash payments Cash from (to) operating activities Investing activities Equipment purchases Other Cash from (to) investing activities Financing activities Loan proceeds Loan payments Cash from (to) financing activities Change in cash Beginning cash Ending cash Assumptions First quarter sales Sales growth each quarter Collections in the quarter of sale Collections in the 1st quarter following a sale Collections in the 2nd quarter following a sale Previous years 4 th quarter sales Previous years 3 rd quarter sales Cash Budget Sheet2 Sheet3