Chapter 8 Applying Excel Data Year 2 Quarter Year 3 Quarter

Chapter 8: Applying Excel Data Year 2 Quarter Year 3 Quarter Budgeted unit sales 100 70,000 80,000 $8 . Selling price per unit Accounts receivable, beginning balance Sales collected in the quarter sales are made Sales collected in the quarter after sales are made Desired ending finished goods inventory is Finished goods inventory, beginning Raw materials required to produce one unit $65,000 75% 25% 30% of the budgeted unit sales of the next quarter 12,000 units 5 pounds 10% of the next quarter\'s production needs Desired ending inventory of raw materials is Raw materials inventory, beginning Raw material costs 23,000 pounds 50.80 per pound 60% in the quarter the purchases are made 40% 1 in the quarter following purchase 7Raw materials purchases are paid and Accounts payable for raw materials, beginning bala 81.500 ? below :1 Enter a formula into each of the cells marked with 2 Review Problem: Budget Schedules 23 24 Construct the sales budget Year 2 Quarter Year 3 Quarter 26 Budgeted unit sales 27 Selling price per unit 28 Total sales 29 30 Construct the schedule of expected cash collecti 31 32 Beginning balance accounts receivable 33 First-quarter sales 34 Second-quarter sales 35 Third-quarter sales 36 Fourth-quarter sales Year 2 Quarter Year

Solution

SALES BUDGET Q1 Q2 Q3 Q4 YEAR Q1 YR3 Q2 YR3 Budgeted Sales units 40,000 60,000 100,000 50,000 250,000 70,000 80,000 Selling price per unit 8 8 8 8 8 8 8 Total Sales 320,000 480,000 800,000 400,000 2,000,000 560,000 640,000 EXPECTED CASH COLLECTIONS Q1 Q2 Q3 Q4 YEAR Beginning Accounts receivable 65,000 65000 Q-1 sales 240,000 80000 320000 Q-2 sales 360000 120000 480000 Q-3 sales 600,000 200,000 800000 Q-4 sales 300,000 300000 Total Cash Collections 305,000 440,000 720,000 500,000 1965000