Question

Edmonds Company, a sports specialty store, prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparing the master budget for the first quarter:

Edmonds Company Balance Sheet December 31, 2016
Cash	$48,000
Accounts receivable	$84,000
Inventory	$80,000
Buildings and equipment (net)	$490,000	$702,000
Accounts payable	$90,000
Common stock	$500,000
Retained earnings	$112,000	$702,000

Information from marketing regarding sales:

Actual Sales for December were $280,000. Sales Projections for January through April are:

• January: $400,000
• February: $500,000
• March: $300,000
• April: $200,000

Sales are 70% for cash and 30% on credit. All payments on credit sales are collected in the month following sale. The accounts receivable at December 31 are a result of December credit sales. The company’s gross margin is 50% of sales. (In other words, cost of goods sold is 50% of sales.) Monthly expenses are budgeted as follows:

• salaries and wages, $27,000 per month
• advertising, $70,000 per month
• shipping, 5% of sales
• other expenses, 3% of sales

Depreciation, including depreciation on new assets acquired during the quarter, will be $42,000 for the quarter. Each month’s ending inventory should equal 40% of the following month’s cost of goods sold. The December 31 ending inventory can be found in the financial statement information above. One-half of a month’s inventory purchases is paid for in the month of purchase; the other half is paid the following month. The December purchases are the amount of Accounts Payable shown in the financial statement information above.

During February, the company will purchase a new t-shirt making machine for $150,000 cash. During March, other equipment will be purchased for cash at a cost of $90,000. During January, the company will declare and pay $55,000 in cash dividends. Management MUST maintain a minimum cash balance of $30,000. The company has an agreement with a local bank that allows the company to borrow in increments of $1,000 at the beginning of each month. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. The company - if it is able will repay loans in total.

Prepare the following in good form (meaning clearly labeled) for January, February and March using Excel.

1. Schedule of Cash Collections (5 points)
2. A Schedule of Merchandise Purchases (also called Merchandise Purchase Budget) (10 points)
3. A Schedule of Cash Disbursements for Inventory Purchases (10 points)
4. A Schedule of Cash Disbursements for Expenses (10 points)
5. Cash Budget (10 points)
6. Indicate the amount borrowed and load balance ignoring interest (5 points)

Answer 1

Answer is given below