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

Need a excel formula sheet

Answer 1