Question

Kline Sisters Company operates a gift shop where peak sales and activity occur in the months of December and January. Data regarding the store's operations follow: Sales are budgeted at $360,000 for January, 320,000 for February, and $250,000 for March and $240,000 in April. Collections are expected to be 30% in the month of sale, 65% in the month following the sale, 3% in the second month following sale and 2% uncollectible. The cost of goods sold is 76% of sales. The company desires ending merchandise inventory to equal 13% of the following month's cost of goods sold. Payment for merchandise is made 50% in the month of purchase and 50% in the month following the purchase. Monthly operating expenses to be paid in cash are $12,000. Equipment purchases of $40,000 in February and $30,000 in March were paid in cash. Monthly depreciation is $13,000. Dividends of $48,000 were declared and paid in January. Any borrowings must be in $1,000 increments at 12% annual interest. Assume interest accrues at the beginning of the month and is paid at the end of the month. The company must maintain a minimum cash balance of $30,000. Ignore income taxes. All accounts receivable from December 31 will be collected in January and all accounts payable at December 31 will be paid in January. The balance sheet as of December 31st: Kline Sisters Company Balance Sheet 12/31/14 Assets: Cash Net Accounts Receivable Merchandise Inventory Property Plant and Equipment Less: accumulated depreciation Total Assets $22,000 $83,000 $36,000 $1,600,000 $588,000 $1,012,000 $1,153,000 Liabilities & Stockholder's Equity Accounts Payable Common Stock Retained Earnings $190,000 $350,000 $613,000

**** PLEASE INCLUDE EXCEL FORMULAS ON SPREADHEET*******

Answer 1

Answer to (a) Schedule of Expected Cash Collection Sales Jan $ 3,60,000 Feb Mar Total $3,20,000 $2,50,000 $ 9,30,000 $ 1,08,000 $ Collection 30% in the month of sale 65% in the following month 3% in the second month Opening accounts receivable Total Cash Collection $ 96,000 $ 75,000 $ 2,34,000 $2,08,000 $ 10,800 2,79,000 442000 10800 83000 8,14,800 83000 $ 1,91,000 $3,30,000 $ 2,93,800 $ Budgeted Accounts Receivable as at March 31 is $ 1,79,600 Working- Jan $ 3.60,000 Feb $3,20,000 Mar Total $2,50,000 $ 9,30,000 Sales Receivables 65% of Sale 3% of Sale Total Receivable $ $ $ $ 1,62,500 $ 7,500 170000 9,600 9600 1,62,500 17,100 1 79600 Answer to (b) Merchandise Purchase Budget Jan Feb Mar Total $ 2,73,600 $ 2,43,200 $ 1,90,000 $ 7,06,800 Cost of Goods sold (76% of Sales) Add: Ending Inventory 13% of the following month of COGS Less: Opening Inventory Total Purchases Cash Paid 50% in current month 50% in following month Opening Total Cash paid $ 31,616 $ 24,700 31200$ $ 36,000$ 31,616 $ 24,700$ $ 2,69,216 $2,36,284 $1,96,500 $ 87,516 92,316 7,02,000 $ 1,34,608 $ 1,18,142 $ 98,250 $ 1,34,608 $ 1,18,142 $ $ $ $ 3,51,000 2,52,750 1,90,000 7,93,750 $ 1,90,000 $ 3,24,608 $ 2,52,750 $ 2,16,392 Budgeted Accounts Payable as at March 31 is $ 98,250 i.e 50% of March Purchases i.e 50% of $ 1,96,500 Answer to (c) Cash Budget Jan Feb Mar Cash Receipts Opening cash Balance Cash Collection Total Cash Receipts (a) $ 22,000 $ 1,91,000 $ 2,13,000 $ 30,352 $ 30,000 $3,30,000 $2,93,800 $3,60,352 $ 3,23,800 Cash Disbursements Cash paid for inventory purch $ 3,24,608 $ 2,52,750 $ 2,16,392 Operating Expense $ 12,000 $ 12,000 $ 12,000 Equipment purchase $ 40,000 $30,000 Dividend paid $ 48,000 Interest payment $ 2,040 $ 2,040 $ 1,804 Total Disbursements (b) $ 3,86,648 $3,06,790 $ 2,60,196 Surplus//9Deficit) $-1,73,648 $ 53,562 $ 63,604 Minimum balance required to $ 30,000 $ 30,000 $ 30,000 Cash Borrowed $ 2,04,000 Borrowings repaid $ 23,562 $ 33,604 Cash at the end $ 30,352 $ 30,000 $30,000 Note-Assuming Cash is borrowed at the begigning of the month and repaid at the end of the month. The company need to borrow $2,04,000 at the begigning of the quarter The company can repay $ 57,166 ($23,562+ $ 33,604) in the quarter Amount $ 2,23,200 Answer to (d) Budgeted Income Statement Particulars Amount Sales $ 9,30,000 Less: Cost of goods sold $ 7,06,800 Gross Profit Less: Expenses Operating Expenses $ 36,000 Depreciation $ 39,000 Bad Debts (2% uncollectible) $ 18,600 Interest paid $ 5,884 Total Expenses $ 99,484 Net Profit $ 1,23,716