Will Sanders is preparing the City of Summerville's budget for the coming quarter. For the past five years, the town has used the following payment pattern when preparing the cash payments budget: 30% of payments made in the month of purchase, 60% of payments made in the month after purchase, and 10% of payments made two months after purchase. Will reviewed the past three quarters' actual cash payments against the cash payments budgets for those periods and discovered that actual payments were quite different from the budgeted amounts, even though the total amount of purchases was very dose to the budgeted amount each month. He is concerned that payment patterns may have changed as a result of the overall economic climate and a tightening of the town's available cash. He has asked you to review recent payments data and make a recommendation of a new payment pattern to use in preparing the budget. 

Required 

a. Using the supplied cash payments data, calculate the most recent fiscal year cash payment pattern that actually occurred. You will find Excel's MONTH and SUMICfrmditort helpful, 

b. Did the town actually pay its bills faster or slower than the budget specified? What effect did this actual timing have on the expected cash balance, 

c. Calculate the cash payment pattern for the Department of Public Works, School Administration and Treasury Department. Does it appear that all departments are following the same cash payment pattern? 

d. Assuming that all departments are not following the same cash payment pattern, how might Will choose to apply a cash payment pattern when preparing the cash payments budget?