Reference no: EM131240219

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.