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 31^st:

Kline Sisters Company
Balance Sheet
12/31/14
Assets:
Cash				$22,000
Net Accounts Receivable				$83,000
Merchandise Inventory				$36,000
Property Plant and Equipment			$1,600,000
Less: accumulated depreciation			$588,000	$1,012,000
Total Assets				$1,153,000
Liabilities & Stockholder's Equity
Accounts Payable				$190,000
Common Stock				$350,000
Retained Earnings				$613,000
Total liabilities and stockholder's equity				$1,153,000

Prepare the following budgets for each month January, February, March and Total for the quarter in good form in excel with proper use of formulas and formatting:

a.   Prepare a Schedule of Expected Cash Collections

• What is the budgeted accounts receivable at March 31^st ?
  

b.   Prepare a Merchandise Purchases Budget and a Schedule of Expected Cash Disbursements

• What is the budgeted accounts payable at March 31^st ?

c.   Prepare a Cash Budget

• How much does the company need to borrow for the quarter?
• How much can the company repay for the quarter?

d.   Prepare a Budgeted Income Statement

Formatting is as important as the correct answer for this project. Your spreadsheet should be in good form, presented and printed clearly, and labeled appropriately. Your print out should be in report format and clearly presented. Each budget should not span more than one page, i.e. pay attention to what prints out on each page

Answer 1

Calculation Jan Feb Mar Feb 320,000 Mar 250,000 Quarter 930,000 Kline Sisters Company Schedule of Expected Cash Collections Req A Jan Sales 360,000 Cash Collections Accounts Receivable from December 31 83,000 Collections in the month of sale 108,000 Collections in month following the sale Collections in second month following the sale Total cash collections 191,000 =360,000*30% 96,000 234,000 83,000 279,000 442,000 10,800 814,800 75,000 208,000 10,800 293,800 =320,000*30% =360,000*65% =250,000*30% =320,000*65% =360,000*3% 330,000 Budgeted accounts receivable at March 31st 179,600 =(320,000*3%)+(250,000*(65%+3%)) Merchandise Purchases Budget Req B1 Budgeted cost of goods sold Add Desired Ending Inventory Inventory Needed Less Beginning Inventory Required Purchases Jan =360,000*76% = 243, 200*13% Feb =320,000*76% =190,000*13% Mar =250,000*76% =240,000*76% 13% Jan 273,600 31,616 305,216 36,000 269,216 Feb Mar 243,200 190,000 24,700 23,712 267,900 213,712 31,616 24,700 236,284 189,012 Quarter 706,800 23,712 730,512 36,000 694,512 End Inv of Jan End Inv of Feb Feb Mar Jan Feb Mar Schedule of Expected Cash Disbursements Req B2 Accounts payable at December 31 Payment in the month of purchase Payment in the month following the purchase Total cash disbursements Jan 190,000 134,608 118,142 134,608 252,750 = 269,216*50% Quarter 190,000 347,256 252,750 790,006 94,506 118,142 212,648 =236,284*50% =269,216*50% =189,012*50% =236,284*50% 324,608 Budgeted accounts payable at March 31st 94,506 =189,012*50%

Jan Feb Mar Jan 22,000 191,000 213,000 Feb Mar 30,392 30,622 330,000 293,800 360,392 324,422 Quarter 22,000 814,800 836,800 324,608 12,000 252,750 12,000 40,000 212,648 12,000 30,000 Cash Budget ReqC Beginning cash balance Add: Cash collections Total cash available Less: Cash payments Merchandise purchases Operating expenses Equipment purchases Dividends Interest expense Total cash payments Excess (deficiency) of cash Financing Borrowings Repayments Total Financing Ending Cash balance 48,000 790,006 36,000 70,000 48,000 3,810 947,816 (111,016) =202,000*12%*1/12 =(202,000-23,000) *12%*1/12 384,608 (171,608) 2,020 306,770 53,622 1,790 256,438 67,984 202,000 202,000 =172,000+30,000 (60,000) Rounded off deficit of 171,608 to 172,000 for borrowings 142,000 30,984 (23,000) (37,000) (23,000) (37,000) 30,622 30,984 202,000 30,392 How much does the company need to borrow for the quarter? How much can the company repay for the quarter? 202,000 60,000 Jan Feb Mar Budgeted Income Statement ReqD Sales Cost of goods sold Gross Profit Operating expenses Bad debt expense Depreciation expense Operating income Interest expense Net income Jan 360,000 273,600 86,400 12,000 7,200 13,000 54,200 Feb 320,000 243,200 76,800 12,000 6,400 13,000 45,400 2,020 43,380 Mar 250,000 190,000 60,000 12,000 5,000 13,000 30,000 1,790 28,210 Quarter 930,000 706,800 223,200 36,000 18,600 39,000 129,600 3,810 125,790 =360,000*2% =320,000*2% =250,000*2% From cashflow 54,200

30,984 179,600 23,712 Balance Sheet ReqE Assets Cash Net Accounts Receivable Merchandise Inventory Total Current Assets Property Plant and Equipment Property Plant and Equipment Less: accumulated depreciation Net Property Plant and Equipment Total Assets 234,296 1,670,000 627,000 =1,600,000+70,000 =588,000+39,000 1,043,000 1,277,296 94,506 142,000 Liabilities & Stockholder's Equity Liabilities Accounts Payable Notes Payable Total Liabilities Stockholder's Equity Common Stock Retained Earnings Total Stockholder's Equity Total Liabilities & Stockholder's Equity 236,506 350,000 690,790 =613,000+125,790-48,000 1,040,790 1,277,296