A Schedule of Cash Payments

The Elliot Corporation has forecast the following sales for the first seven months of the year:
January $12,000
February $16,000
March $18,000
April $24,000
May $12,000
June $20,000
July $22,000

Monthly material purchases are set equal to 20 percent or forecasted sales for the next month. Of the total material costs, 40 percent are paid in the month of purchase and 60 percent in the following month. Labor costs will run $6,000 per month, and fixed overhead is $3,000 per month. Interest payments on the debt will be $4,500 for both March and June. Finally, Elliot's sales force will receive a 3 percent commission on total sales for the first six months of the year, to be paid on June 30.
Prepare a monthly summary of cash payments for the six-month period from Jan-June. (Note: Compute prior Dec purchases to help get total material payments for Jan).

How do I set up the summary for this problem and compute prior Dec. purchases?

Solution Summary

This post contains a formatted MS Excel file which provides the reader with an example on how to complete a cash payment schedule.