Excel doc covering calculations for a Sales Budget, Cash Budget and Budgeted Income Statement

PROJECT A - Case 9-30
Student Name:


SALES BUDGET:
April May June Quarter
Budgeted unit sales 65,000 100,000 50,000 215,000
Selling price per unit 10 10 10 10
Total Sales 650,000 1,000,000 500,000 2,150,000



SCHEDULE OF EXPECTED CASH COLLECTIONS:
April May June Quarter
February sales 26,000 26,000
March sales 280,000 40,000 320,000
April sales 130,000 455,000 65,000 650,000
May sales 200,000 700,000 900,000
June sales 100,000 100,000
Total Cash Collections 436,000 695,000 865,000 1,996,000



MERCHANDISE PURCHASES BUDGET:
April May June Quarter
Budgeted unit sales 65,000 100,000 50,000 215,000
Add desired ending inventory 40,000 20,000 12,000 12,000
Total needs 105,000 120,000 62,000 227,000
Less beginning inventory 26,000 40,000 20,000 26,000
Required purchases 79,000 80,000 42,000 201,000
Cost of purchases @ $4 per unit 316,000 320,000 168,000 804,000



BUDGETED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES:
April May June Quarter
Accounts payable 100,000 100,000
April purchases 158,000 158,000 316,000
May purchases 160,000 160,000 320,000
June purchases 84,000 84,000
Total cash payments 258,000 318,000 244,000 820,000



EARRINGS UNLIMITED
CASH BUDGET
FOR THE THREE MONTHS ENDING JUNE 30
April May June Quarter
Cash balance 74,000 50,000 50,000
Add collections from customers 436,000 695,000 865,000 1,996,000
Total cash available 510,000 745,000 915,000 2,070,000