Chapter 9 HW excel spreadsheet #2

1 Chapter 9: Applying Excel Year 2 Que Year 3 Quarter Data 5 Budgeted unt sales 40.000 60,000 100,000 50,000 70,000 80000 7 Selling price per unt 58 per unit Accounts receivable, beginning balance $65.000 75% 25% 30% of the budgeted unt sales of the next quarter . Sales collected in the quater sales are made 0 Sales collected in the quarter ater sales are made 1 Desined ending Snished goods inventory is 2 Finished geods inventory, beginning 13 Raw matenials required to produce one unt 4 Desired ending inventory of raw materials is 5 Raw matecials inventory, beginning 6 Raw material costs 17-Raw materals puchases are pad 18 and 12.000 unts 5 pounds 10% of the next quarters production needs 23,000 pounds 50 80 per pound 60% in the quarter the purchases art made % in the quarter following purchase 19 Accounts payable for raw matenials, beginning balance $81.500 ?below 21 Enter & formula tto each of the cela marked wth 22 Review Problem: Budget Schedules Year 2 Quater Year 3 Quarter 24 Construct the sales budger 25 26 Budgered unit sales 27 Selling price per un 8 Total sales 30 Construct the schedule of expected cash collections Year 2 Quarter Yea 32 Accounts receivable, beginning balance 3 First-quarter sales 34 Second-quarter sales S Third-quarter sales 6 Fouth-quarter sales 37 Total cash collections 38 9 Construct the production budget Year 2 Quarter Year 3 Quarder Year 41 Budgeted unit sales 42 Add desired finished goods inventory 43 Total needs 44 Less beginning inventory 45 Required production 46 7 Construct the raw materials purchases budge 48 49 Required production (unes) Year 2 Quater Year 3 Quarter Year 50 Raw materiais required to produce one unit 50 Raw matenals requred to produce one un 51 Preduction needs (pounds) 52 Add desired ending inventory of raw matenials (pounds) 53 Total needs (pounds) 54 Less beginning inventory of raw materials (pounds) 5 Raw materials to be purchased 56 Cost of raw mateials per pound 57 Cost of raw matecials to be purchased 58 59 Construct the schedule of expected cash payments Year 2 Queter 3 4 Year 61 Accounts payable, beginning balance 62 Fiest-quarter purchases 53 Sacond- quarter purchases 64 Third-quanter purchase 65 Fourth-quarter purchases 66 Total cash disbursements