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, 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 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 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. 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.
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.
b. Collapse rows to level 2 and columns to level 1, and then print the worksheet in Value view with your name and date printed in the lower left footer and the file name in the lower right footer. Print cash budget only, no assumptions.
c. Expand rows to level 3 and columns to level 2, and then use what-if analysis to calculate end-of-year cash if the sales growth each quarter were 4 percent and payroll expense were $18,000 per 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. Print cash budget only, no assumptions.
d. Undo the what-if analysis performed in part c. Use goal seek to deter-mine what sales growth would be needed to produce an ending cash balance of $50,000. 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. Print cash budget and assumptions.