Question

 Kelly's Boutique is contemplating several means of financing their acquisition of $200,000 in special equipment. One alternative is to borrow S200,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 S30,000. Expenses include advertising expense of $900, depreciation expense of $800, interest expense of $1,000, pay-roll expense of S8,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 S17,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 S4,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.

Answer 1

	Particular	Explanation	Q1	Q2	Q3	Q4	Q1 next year
	Sales	2% growth	$   40,000.00	$      40,800.00	$       41,616.00	$   42,448.32	$   50,000.00
	Cost of Sales (A)	55% of sales	$   22,000.00	$      22,440.00	$       22,888.80	$   23,346.58	$   27,500.00
	Ending Inventory (B)	70% of next quarter cost of sales	$   15,708.00	$      16,022.16	$       16,342.60	$   19,250.00
	Opening Inventory (C)		$   11,000.00	$      15,708.00	$       16,022.16	$   16,342.60
	Purchases (A + B - C)		$   26,708.00	$      22,754.16	$       23,209.24	$   26,253.97
	Particular	Explanation	Q1	Q2	Q3	Q4
	Opening Balance	Previous closing	$   15,000.00	$      16,148.20	$    2,22,176.36	$   25,050.49
Receipts	From Sale (current quarter, 80%)		$   32,000.00	$      32,640.00	$       33,292.80	$   33,958.66
	From Sale (previous quarter, 15%)		$      4,200.00	$        6,000.00	$          6,120.00	$      6,242.40
	From Sale (previous quarter, 5%)		$      1,500.00	$        1,400.00	$          2,000.00	$      2,040.00
Payments	Purchases (current quarter, 85%)		$ (22,701.80)	$   (19,341.04)	$     (19,727.86)	$ (22,315.88)
	Purchases (previous quarter, 10%)		$   (2,000.00)	$      (2,670.80)	$       (2,275.42)	$   (2,320.92)
	Purchases (previous quarter, 5%)		$       (850.00)	$      (1,000.00)	$       (1,335.40)	$   (1,137.71)
	Advertising Expense		$       (900.00)	$         (900.00)	$           (900.00)	$       (900.00)
	Interest Expense		$   (1,000.00)	$      (1,000.00)	$       (1,000.00)	$   (1,000.00)
	Payroll Expense		$   (8,000.00)	$      (8,000.00)	$       (8,000.00)	$   (8,000.00)
	Supplies Expense		$       (500.00)	$         (500.00)	$           (500.00)	$       (500.00)
	Utilities Expense		$       (600.00)	$         (600.00)	$           (600.00)	$       (600.00)
Proceeds	From Loan			$ 2,00,000.00
Payment	Loan				$       (4,200.00)	$   (4,200.00)
Payments	Purchase of equipment				$ (2,00,000.00)
	Closing Cash Balance		$   16,148.20	$ 2,22,176.36	$       25,050.49	$   26,317.04