The management estimates total sales for the period January through July based on actualsales 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

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

FIN 5130 Mini-Case 2 Instructor: Dr. Palkar Late submissions will not be accepted so please plan accordingly. You need to show your working notes for credit. You must submit your work using excel files (with .xls or .xlsx for credit). You must upload your files on Blackboard under the Assignment Dropbox for credit. This assignment will require you to prepare the cash budget and determine the cash surplus and shortage each month. 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 September October November December $300,000 $250,000 $300,000 $200,000 Forecast January February March April May June July $150,000 $100,000 $200,000 $200,000 $225,000 $250,000 $300,000 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 three 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. Page 1 of 1