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 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 Fourth-quarter sales Total cash collections Construct the production budget Budgeted unit sales Add desired finished goods inventory Total needs Less beginning inventory Required production \begin{tabular}{|c|c|c|c|c|c|c|c|} \hline \multicolumn{5}{|c|}{ Year 2 Quarter } & \multicolumn{2}{|c|}{ Year 3 Quarter } & \\ \hline 1 & F & 2 & & 4 & 1 & 2 & \\ \hline 40,000 & & 60,000 & 100,000 & 50,000 & 70,000 & 80,000 & \\ \hline$8 & per & unit & & & & & \\ \hline$65,000 & & & & & & & \\ \hline 75% & & & & & & & \\ \hline 25% & & & & & & & \\ \hline 30% & \multicolumn{5}{|c|}{ of the budgeted unit sales of the next quarter } & & \\ \hline 12,000 & unit & & & & & & \\ \hline 5 & pou & unds & & & & & \\ \hline 10% & \multicolumn{4}{|c|}{ of the next quarter's production needs } & & & \\ \hline 23,000 & pou & unds & & & & & \\ \hline$0.80 & per & pound & & & & & \\ \hline 60% & \multicolumn{4}{|c|}{ in the quarter the purchases are made } & & & \\ \hline 40% & \multicolumn{4}{|c|}{ in the quarter following purchase } & & & \\ \hline \multirow{4}{*}{$81,500} & & & & & & & \\ \hline & & & & & & & \\ \hline & & & & & & & \\ \hline & & & & & & & \\ \hline \multicolumn{5}{|c|}{ Year 2 Quarter } & \multicolumn{2}{|c|}{ Year 3 Quarter } & \\ \hline 1 & F & 2 & & 4 & 1 & 2 & \\ \hline 40,000 & & 60,000 & 100,000 & 50,000 & 70,000 & 80,000 & \\ \hline$8 & & $8 & $8 & $8 & $8 & $8 & \\ \hline$320,000 & & $480,000 & $800,000 & $400,000 & $560,000 & $640,000 & \\ \hline \multicolumn{5}{|c|}{ Year 2 Quarter } & & & \\ \hline 1 & F & 2 & 3 & 4 & Year & & \\ \hline 65,000 & & & & & 65,000 & & \\ \hline \multirow[t]{4}{*}{$240,000} & $ & 120,000 & & & $360,000 & & \\ \hline & & $360,000 & $200,000 & & $560,000 & & \\ \hline & & & $600,000 & $100,000 & $700,000 & & \\ \hline & & & & $300,000 & $300,000 & & \\ \hline$305,000 & $ & 480,000 & $800,000 & $400,000 & $1,985,000 & & \\ \hline \multicolumn{5}{|c|}{ Year 2 Quarter } & & \multicolumn{2}{|c|}{ Year 3 Quarter } \\ \hline 1 & F & 2 & 3 & 4 & Year & F & 2 \\ \hline 40,000 & & 60,000 & 100,000 & 50,000 & 250,000 & 70,000 & 80,000 \\ \hline 18,000 & & 30,000 & 15,000 & 21,000 & 21,000 & 24,000 & \\ \hline 58,000 & & 90,000 & 115,000 & 71,000 & 271,000 & 94,000 & \\ \hline 12,000 & & 18,000 & 30,000 & 15,000 & 12,000 & 21,000 & \\ \hline 46,000 & & 72,000 & 85,000 & 56,000 & 259,000 & 73,000 & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline \multirow[t]{2}{*}{ Construct the raw materials purchases budget } & \multicolumn{6}{|c|}{ Year 2 Quarter } & \multicolumn{2}{|r|}{ Year 3 Quarter } \\ \hline & & 1 & & 2 & 3 & 4 & Year & 1 \\ \hline Required production (units) & & 46,000 & & 72,000 & 85,000 & 56,000 & 259,000 & 73,000 \\ \hline Raw materials required to produce one unit & & 5 & & 5 & 5 & 5 & 5 & 5 \\ \hline Production needs (pounds) & & 230,000 & & 360,000 & 425,000 & 280,000 & 1,295,000 & 365,000 \\ \hline Add desired ending inventory of raw materials (pounds) & & 36,000 & & 42,500 & 28,000 & 36,500 & 36,500 & \\ \hline Total needs (pounds) & & 266,000 & & 402,500 & 453,000 & 316,500 & 1,331,500 & \\ \hline Less beginning inventory of raw materials (pounds) & & 23,000 & & 36,000 & 42,500 & 28,000 & 23,000 & \\ \hline Raw materials to be purchased & & 243,000 & & 366,500 & 410,500 & 288,500 & 1,308,500 & \\ \hline Cost of raw materials per pound & & $0.80 & & $0.80 & $0.80 & $0.80 & $0.80 & \\ \hline Cost of raw materials to be purchased & & $194,400 & & $293,200 & $328,400 & $230,800 & $1,046,800 & \\ \hline \multirow[t]{2}{*}{ Construct the schedule of expected cash payments } & \multicolumn{6}{|c|}{ Year 2 Quarter } & & \\ \hline & & 1 & F & 2 & 3 & 4 & Year & \\ \hline Accounts payable, beginning balance & $ & 81,500 & & & & & 81,500 & \\ \hline First-quarter purchases & & 116,640 & $ & 77,760 & & & 194,400 & \\ \hline Second-quarter purchases & & & & 175,920 & $117,280 & & 293,200 & \\ \hline Third-quarter purchases & & & & & 197,040 & $131,360 & 328,400 & \\ \hline Fourth-quarter purchases & & & & & & 138,480 & 138,480 & \\ \hline Total cash disbursements & $ & 198,140 & $ & 253,680 & $314,320 & $269,840 & $1,035,980 & \\ \hline \end{tabular}