You need to show your working notes for credit. You must submit your work using excel files (with .xls or .xlsx for credit).

December 2016 / $70,000

Forecast Sales

• January 2017 / $110,000
• February 2017 / $90,000
• March 2017 / 120,000
• April 2017 / 80,000
• May 2017 / 70,000
• June 2017 / 60,000

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

b. Purchases were $50,000 in July, 2016 and are expected to grow by 2% each month. The purchases will be paid in the same month.

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

d. Depreciation expenses are $20,000 each month.

e. Rent of $15,000 will be paid at the end of each calendar quarter.

f. Machinery worth $150,000 will be purchased in March.

Prepare the cash budget from January to June. Determine the cash surplus and shortages for each month from January to June. Provide your analysis.