Reference no: EM131473408

This assignment will require you to prepare the cash budget and determine the cash surplus and shortage each month. Submit in excel

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

September $300,000 January $150,000

October $250,000 February $100,000

November $300,000 March $200,000

December $200,000 April $200,000

May $225,000

June $250,000

July $300,000

Historical Forecast

a. 50% of the Sales are collected immediately. 25% of the Sales will be collected one month after the sale. 12% will be collected two months after the sale. 8% will be collected three months after the sale. The remainder will be collected four months after the sale. Bad debts are insignificant.

b. Purchases will be $80,000 in January, and will grow by 5% each month. The purchases will be paid in the same month.

c. Wages and salaries of $30,000 will be paid each month.

d. Rent paid will be $15,000 per month.

e. Interest expense is based on bonds valued $400,000. The annual coupon rate on these bonds is 8%. Interest will be paid every six months, at the end of June and December.

f. A tax prepayment of $45,000 is paid in April.

g. Machinery worth $150,000 will be purchased in February.

h. Cash on hand on January 1st will amount to $150,000.

i. A minimum cash balance of $150,000 each month (i.e., target cash balance) will be maintained throughout the period.

1. Prepare the cash budget from January to June.

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