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.