e o 30 percent are paid in the following month. Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer d. Reset the payments expectations to the original 90 percent and 10 percent values. Use goal seek to determine what cost of expected sales percentage would cause the operating cash payments to be $500,000 for the quarter. Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer. Chapter 6 Case Problem 1: KELLY'S BOUTIQUE 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 l-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 inven- tory 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, pay- roll 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 pur- chase 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. Pur- chases in the previous year's fourth quarter were $20,000. Purchases in the pre- vious 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. art 1 Excel for Accounting 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 col- umns together. Save your file as ch6-04, student name (replacing student name with your name). Define names as appropriate. a. Print the newly completed worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer
