Question

In: Accounting

Kline Sisters Company operates a gift shop where peak sales and activity occur in the months...

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

Please highlight your answers.

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.

      Please name your uploaded file the following way:

                                          LAST name_First name_ACCT116_2017F

You will need to borrow and repay during the quarter.

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

Please highlight your answers.

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.

      Please name your uploaded file the following way:

                                          LAST name_First name_ACCT116_2017F

You will need to borrow and repay during the quarter.

Solutions

Expert Solution

Credit Sales $ 360,000.00 $ 320,000.00 $ 250,000.00
bug cash Receipts Jan Feb Mar Quarter
Accounts receivable $    83,000.00 $                   -   $                   -   $    83,000.00
Jan $ 108,000.00 $ 234,000.00 $    10,800.00 $ 352,800.00
Feb $    96,000.00 $ 208,000.00 $ 304,000.00
Mar $    75,000.00 $    75,000.00
Total Collection $ 191,000.00 $ 330,000.00 $ 293,800.00 $ 814,800.00
Purchase Budget Jan Feb Mar April
Total Sales $ 360,000.00 $ 320,000.00 $ 250,000.00 $ 240,000.00
COGS 76% $ 273,600.00 $ 243,200.00 $ 190,000.00 $ 182,400.00
Closing Inv 13% $    31,616.00 $    24,700.00 $    23,712.00
Beginning inv $ (36,000.00) $ (31,616.00) $ (24,700.00)
Purchases $ 269,216.00 $ 236,284.00 $ 189,012.00
Jan Feb Mar Quarter
Accounts Payable $ 190,000.00 $ 190,000.00
Jan $ 134,608.00 $ 134,608.00 $ 269,216.00
Feb $ 118,142.00 $ 118,142.00 $ 236,284.00
Mar $    94,506.00 $    94,506.00
Total Payment $ 324,608.00 $ 252,750.00 $ 212,648.00 $ 790,006.00

Related Solutions

Kline Sisters Company operates a gift shop where peak sales and activity occur in the months...
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...
Silver Company makes a product that is very popular as a Mother's Day gift. Thus, peak sales occur in May of each year.
 1. Schedule of expected cash collections Silver Company makes a product that is very popular as a Mother's Day gift. Thus, peak sales occur in May of each year. These peak sales are shown in the company's sales budget for the second quarter given below: From past experience, the company has learned that 15% of the company's sales are for cash. Credit sales are collected as follows: 20% are collected in the month of sale, another 70% are collected in the month following...
Silver Company makes a product that is very popular as a Mother's Day gift. Thus, peak sales occur in May of each year, as shown in the company's sales budget for the second quarter given below:
Silver Company makes a product that is very popular as a Mother's Day gift. Thus, peak sales occur in May of each year,as shown in the company's sales budget for the second quarter given below:AprilMay JuneTotalBudgeted sales (all on account300, 00$500, 006$200, 006$1, 090, 090From past experience, the company has learned that 20% of a month's sales are collected in the month of sale, another70% are collected in the month following sale, and the remaining 10% are collected in the...
Silver Company makes a product that is very popular as a Mother’s Day gift. Thus, peak sales occur in May of each year, as shown in the company’s sales budget for the second quarter given below:
Exercise 7-1 Schedule of Expected Cash Collections [LO7-2]Silver Company makes a product that is very popular as a Mother’s Day gift. Thus, peak sales occur in May of each year, as shown in the company’s sales budget for the second quarter given below: From past experience, the company has learned that 25% of a month's sales are collected in the month of sale, another 70% are collected in the month following sale, and the remaining 5% are collected in the second...
The Dubious Company operates in an industry where all sales are made on account. The company...
The Dubious Company operates in an industry where all sales are made on account. The company has experienced bad debt losses of 1.40% of credit sales in prior periods. Presented below is the company's forecast of sales and expenses over the next three years. Year 1 Year 2 Year 3 Sales Revenue $ 373,000 $ 379,000 $ 378,000 Bad Debt Expense Unknown Unknown Unknown Other Expenses 337,000 337,000 337,750 Net Income Unknown Unknown Unknown Required: Calculate Bad Debt Expense and...
PROBLEM 1 The owner of the Red Mill Inn reviews gift shop sales on a monthly...
PROBLEM 1 The owner of the Red Mill Inn reviews gift shop sales on a monthly basis. Over the years, certain items have been dropped from the shop because their sales provided insufficient profit relative to the other gift shop items. Information for the past six months on three gift shop items is in the table below. BAG OF SWEET STICKY MUFFINS BREAD BUNS Sales $2,160.00 $540.00 $2,880.00 Variable costs $1,170.00 $256.50 $1,260.00 Contribution Margin $990.00 $283.50 $1,620.00 Fixed costs...
Silver Company makes a product that is very popular as a Mother's Day gift. Thus, peak...
Silver Company makes a product that is very popular as a Mother's Day gift. Thus, peak sales occur in May of each year, as shown in the company's sales budget for the second quarter given below:April May June TotalBudgeted sales (all on account) $ 300,000 $ 500,000 $200,000 $ 1,000,000From past experience, the company has learned that 20% of a month's sales are collected in the month of sale, another 70% are collected in the month following sale, and the...
Silver Company makes a product that is very popular as a Mother’s Day gift. Thus, peak...
Silver Company makes a product that is very popular as a Mother’s Day gift. Thus, peak sales occur in May of each year, as shown in the company’s sales budget for the second quarter given below: April May June Total Budgeted sales (all on account) $450,000 $650,000 $250,000 $1,350,000 From past experience, the company has learned that 25% of a month’s sales are collected in the month of sale, another 60% are collected in the month following sale, and the...
Silver Company makes a product that is very popular as a Mother’s Day gift. Thus, peak...
Silver Company makes a product that is very popular as a Mother’s Day gift. Thus, peak sales occur in May of each year, as shown in the company’s sales budget for the second quarter given below: April May June Total Budgeted sales (all on account) $410,000 $610,000 $210,000 $1,230,000 From past experience, the company has learned that 25% of a month’s sales are collected in the month of sale, another 60% are collected in the month following sale, and the...
Silver Company makes a product that is very popular as a Mother’s Day gift. Thus, peak...
Silver Company makes a product that is very popular as a Mother’s Day gift. Thus, peak sales occur in May of each year, as shown in the company’s sales budget for the second quarter given below: April May June Total Budgeted sales (all on account) $330,000 $530,000 $210,000 $1,070,000 From past experience, the company has learned that 25% of a month’s sales are collected in the month of sale, another 60% are collected in the month following sale, and the...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT