“ACCT556
Week 5 Problem
Cash Budget

Note: 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:

Month

Projected 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.

ACCT556Week 5 ProblemCash BudgetNote: 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 SalesJanuary$850,000February750,000March730,000April850,000May830,000June750,000July900,000The collection
history of the Hale Company has been as follows:The following
information regarding costs is available: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.”