Use the below data to create a cash budget showing expected cash receipts and disbursements for the month of May, and cash balance expected as of May 31, 2017.

1. Planned cash balance, April 30, 2017: $100,000

2.Customer receivables as of April 30: $530,000 total, $80,000 from March sales, $450,000 from April sales

3.Accounts Payable, April 30: $460,000

4.Merchandise purchases for May: $450,000, 40% paid in month of purchase, 60% paid in next month

5.Payrolls due in May: $90,000

6.Other expenses for May, payable in July: $7,500

7.Bank note due May 15: $90,000 plus $7,200 interest

8.Depreciation for May: $2,100

9.Three-year insurance policy due May 10 for renewal: $1,500, to be paid in cash

10.Sales for May: $1,000,000, half collected in month of sale, 40% in next month, 10% in third month

Question:

1) Prepare the cash budget for the month ending May 31, 2017.

2) Provide an analysis of the cash budget you just created. Must include a mention of the at least: The A/R terms? What are the A/P terms? What impact do these terms have on the ending cash balance?

Requirement:Using Excel and coculate the data by correct excel fomula

Please post the answer with a excel formation.