Reference no: EM131584710

Budget Assignment

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

Prepare the following budgets for each month January, February, March and Total for the quarterin 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

Extra credit -

• Prepare a Budgeted Balance Sheet - it must balance.
• Prepare a letter to the CEO identifying three items of interest on the budget and provide recommendations.