The management estimates total sales for the period January through July based on actual

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.     

Bad debts are insignificant.     

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