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 $15,000.
- Dividends of $40,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
|
|
|
$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 31st ?
b. Prepare a Merchandise Purchases Budget and a Schedule of Expected Cash Disbursements
- What is the budgeted accounts payable at March 31st ?
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