It is expected that this problem will be complete using an Excel spreadsheet using formulas. Please see

Question:

It is expected that this problem will be complete using an Excel spreadsheet using formulas. Please see the Excel Tutorial that is available under the course home tab. The Hale Company is currently working on its cash budget for the coming year. The following information is available:

Projected sales for the coming year:

MonthProjected Sales

January ...............................$850,000

February ...............................750,000

March....................................730,000

April ......................................850,000

May ........................................830,000

June .......................................750,000

July .......................................900,000

The collection history of the Hale Company has been as follows:

20% of sales are collected in the month of the sale.

60% of the sales are collected in the month following the sale.

12% of the sales are collected in the 2nd month following the sale.

5% of the sales are collected in the 3rd month following the sale.

The following information regarding costs is available:

The cost of goods sold is 54% of sales

Items for sale are purchased in the month of the sale.

80% of accounts payable are paid in the month following when the cost is incurred.

20% of accounts payable are paid in the 2nd month following when the cost is incurred.

Wages are 28% of sales and are paid currently

Annual general and administrative costs are $1,411,200 and are incurred evenly throughout the year.

Annual property taxes are $14,000 and are paid semi annually in June and October.

A $10,000 cash capital purchase will be made in April.

The beginning cash balance in April is expected to be $47,000. The Hale Company has a policy of maintaining a minimum cash balance of $45,000. The Company has an arrangement with a local bank for a line of credit that carries a 10% annual interest rate. If the ending monthly balance falls below $45,000, the company will borrow against the line of credit so that the minimum balance can be maintained. If the company has borrowed against the line of credit and a cash balance is expected to be above $45,000 at the end of a particular month, then repayments will be made bringing the cash balance down to $45,000. Interest on the line of credit is paid monthly. Assume that all line of credit transactions occur on the last day of the month.

Required:

Prepare a cash budget for the Hale Company for the 2nd quarter of the year. Include April, May, June, and a quarter total in your budget.

Accounts Payable
Accounts payable (AP) are bills to be paid as part of the normal course of business.This is a standard accounting term, one of the most common liabilities, which normally appears in the balance sheet listing of liabilities. Businesses receive... Cash Budget
A cash budget is an estimation of the cash flows for a business over a specific period of time. These cash inflows and outflows include revenues collected, expenses paid, and loans receipts and payment. Its primary purpose is to provide the... Line of Credit
A line of credit (LOC) is a preset borrowing limit that can be used at any time. The borrower can take money out as needed until the limit is reached, and as money is repaid, it can be borrowed again in the case of an open line of credit. A LOC is...