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
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 | $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
Need a excel formula sheet
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started