9e0bde87-793b-45f1-9a95-26d4f96c12ec.xls Chapter 8: Applying Excel Data Budgeted unit sales • 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 • Desired ending inventory of raw materials is Raw materials inventory, beginning Raw material costs • Raw materials purchases are paid and • Accounts payable for raw materials, beginning balance Year 2 Quarter 1 2 3 4 40,000 60,000 100,000 50,000 $8 per unit $65,000 75% 25% 30% of the budgeted unit sales of the next qua 12,000 units 5 pounds 10% of the next quarter's production needs 23,000 pounds $0.80 per pound 60% in the quarter the purchases are made 40% in the quarter following purchase $81,500 Enter a formula into each of the cells marked with a ? below Review Problem: Budget Schedules Construct the sales budget Budgeted unit sales Selling price per unit Total sales Construct the schedule of expected cash collections Accounts receivable, beginning balance First-quarter sales Second-quarter sales Third-quarter sales Year 2 Quarter 1 2 3 4 ? ? ? ? ? ? ? ? ? ? ? ? Year 2 Quarter 1 2 3 4 ? ? ? ? ? ? ? ? ? ? Fourth-quarter sales Total cash collections ? ? Construct the production budget Year 2 Quarter 1 2 3 4 Budgeted unit sales ? ? ? ? Add desired finished goods inventory ? ? ? ? Total needs ? ? ? ? Less beginning inventory ? ? ? Required production ? ? ? 22 ? ? Construct the raw materials purchases budget Year 2 Quarter 1 2 3 4 Required production (units) ? Raw materials required to produce one unit Production needs (pounds) ? 222. ? ? ? ? ? ? ? ? ? ? Add desired ending inventory of raw materials (pounds) Total needs (pounds) ? ? ? ? ? ? ? ? Less beginning inventory of raw materials (pounds) ? ? ? ? Raw materials to be purchased ? ? ? ? Cost of raw materials per pound ? ? ? ? Cost of raw materials to be purchased Page 1 ? ? ? ?