# The management estimates

The management estimates total sales for the period January through July based on actual sales from the immediate past quarter. The following assumptions are made:

Sales: PAST AND EXPECTED:

HISTORICAL:

October \$300,000

November \$350,000

December \$400,000

FORECAST:

January \$150,000

February \$200,000

March \$200,000

April \$300,000

May \$250,000

June \$200,000

July \$300,000

a. 25% of the Sales will be collected in the same month of the Sale. 60% of the Sales will be collected one month following the sale. 10% will be collected two months following the sale. The remainder will be collected three months following the sale.

b. Purchases are 80% of sales and are paid in the same month.

c. Wages and salaries are as follows:

January \$30,000

February \$40,000

March \$50,000

April \$50,000

May \$40,000

June \$35,000

Wages and salaries are paid in the same month.
d. Rent is \$10,000 per month. Rent is paid in the same month.
e. The company issued \$500,000 bonds, paying annual coupon rate of 5%. Interest on these bonds is paid at the end of each calendar quarter. Hint: Firsts, calculate the coupon interest amount for each quarter. Then, this quarterly interest amount will be paid at the end of each calendar quarter.
f. A tax prepayment of \$75,000 is paid in April.
g. Machinery worth \$35,000 will be purchased in March.
h. Cash on hand on January 1st will amount to \$100,000 and a minimum cash balance of \$100,000 each month will be maintained throughout the period.

EXCEL MUST BE USED TO SHOW WORK FOR THE FOLLOWING:

1. Prepare cash budget from January to June.
2. Determine the cash surplus and shortages for each month from January to June