\begin{tabular}{|c|c|c|c|c|c|c|c|} \hline \multicolumn{8}{|l|}{ Chapter 8: Applying Excel } \\ \hline \multirow[t]{2}{*}{ Data } & \multicolumn{4}{|c|}{ Yoar 2 Quarter } & \multicolumn{2}{|c|}{ Yoar 3 Quarter } & \\ \hline & 1 & 2 & 3 & 4 & 1 & 2 & \\ \hline Budgoted unit sales & 40,000 & 60,000 & 100,000 & 50,000 & 70,000 & 80,000 & \\ \hline - Selling price per unit & \multicolumn{2}{|c|}{$8 per unit } & & & & & \\ \hline - Accounts receivable, beginning balance & \multicolumn{2}{|c|}{$65,000} & & & & & \\ \hline - Sales collected in the cuarter sales are made. & \multicolumn{2}{|l|}{75%} & & & & & \\ \hline - Sales collected in the quarter after sales are made & \multicolumn{2}{|l|}{25%} & & & & & \\ \hline - Desired ending finished poods inventory is & \multicolumn{5}{|c|}{30% of the budgeted unit sales of the next quarter } & & \\ \hline - Finished goods inventory, beginning & \multicolumn{2}{|c|}{12,000 units } & & & & & \\ \hline - Raw materials required to produce one unit & \multicolumn{2}{|c|}{5 pounds } & & & & & \\ \hline - Desired ending imventory ot raw materials is & \multicolumn{2}{|c|}{10% of the next quar } & fs procuction & needs & & & \\ \hline - Raw materials inventory, beginning & \multicolumn{2}{|c|}{23,000 pounds } & & & & & \\ \hline - Raw material costs & $0.80p & per pound & & & & & \\ \hline - Raw materials purchases are paid & 60% ir & in the quarter the & purchases ar & made & & & \\ \hline and & \multirow{2}{*}{\begin{tabular}{c} 40% \\ $81.500 \end{tabular}} & in the quarter fol & wing purchas & & & & \\ \hline - Accounts payabie for raw materials, beginning balance & & & & & & & \\ \hline Enter a formuls into each of the cells marked with a ? belo & & & & & & & \\ \hline Review Problem: Budget Schedules & & & & & & & \\ \hline Construct the sales budget & & Var2O & & & var 30 & inter & \\ \hline & 1 & 2 & 3 & 4 & 1 & 2 & \\ \hline Budgoted unit sales & 40,000 & 60.000 & 100,000 & 50,000 & 70.000 & 80,000 & \\ \hline Selling price per unit & \$8 & so & 83 & so & 38 & 53 & \\ \hline Total sales & $320,000 & $480.000 & 5800,000 & $400,000 & $560.000 & $640.000 & \\ \hline Construct the schedule of expected cash colloctions & & Year 20 & & & & & \\ \hline & 1 & 2 & 3 & 4 & Yeor & & \\ \hline Accounts recelvable, beginning bolance & 65,000 & & & & 65,000 & & \\ \hline Finst-quarter soles & 240,000 & 80,000 & & & 320,000 & & \\ \hline Second-quarter sales & & 360,000 & 120,000 & & 430,000 & & \\ \hline Third-quarter sales & & & 600,000 & 200,000 & 800,000 & & \\ \hline Fourth-quarter sales & & & & 300,000 & 300,000 & & \\ \hline Total cash collections & 305,000 & 440.000 & 720.000 & 500,000 & 1,965.000 & 1,985,000 & \\ \hline Construct the production budget & & Year 20 & & & & Yoar 3C & \\ \hline & 1 & 2 & 3 & 4 & Yoor & 1 & 2 \\ \hline Budgeted unit sales & 40,000 & 60,000 & 100,000 & 80,000 & 250,000 & 70,000 & 80,000 \\ \hline Add desired finished goods inventory & 18,000 & 30.000 & 15,000 & 21,000 & 21,000 & 24.000 & \\ \hline Total needs & 58,000 & 90,000 & 115,000 & 71,000 & ? & ? & \\ \hline Less beginning imventary & 12,000 & 18,000 & 30,000 & 15,000 & ? & ? & \\ \hline Required production & ?] & a) & 2] & ?1 & 2] & 3] & \\ \hline Construct the raw materlals purchases budget & & Yoar 20 & & & & 3 Ouarter & \\ \hline & 1 & 2 & 3 & 4 & Your & 1 & \\ \hline Required production (units) & ? & ? & ? & ? & ? & ? & \\ \hline Raw materials required to produce one unt & ?) & 2] & 2) & 2] & 3) & 2] & \\ \hline Production needs (pounds) & ? & ? & ? & ? & ? & ? & \\ \hline Add desired ending inventory of rew materials (pounds) & ?] & 2] & ?] & ?1 & 3] & & \\ \hline Total needs (pounds) & ? & 71 & ?1 & ?1 & ? & & \\ \hline Less beginning inventory of rew materials (pounds) & ? & ?1 & ? & ?1 & 2] & & \\ \hline Raw materiats to be purchased & ?] & 3 & 3 & 2 & 2] & & \\ \hline Cost of raw maserlab per pound & ? & ? & ? & ? & ? & & \\ \hline Cost of raw materials io be purchased & ? & 2] & 2) & ?1 & 2] & & \\ \hline Construct the schedule of expected eash payments & & Vear2O & fer & & & & \\ \hline & 1 & 2 & 3 & 4 & Year & & \\ \hline Accounts payable, beginning balance & ? & & & & ? & & \\ \hline First-quarter purchases & ? & ? & & & ? & & \\ \hline Second-quarter purchases & & ? & ? & & ? & & \\ \hline Third-quarter purchases & & & ? & ? & ? & & \\ \hline Fourth-quarter purchases & & & & ? & ? & & \\ \hline Total cash disbursements & ?] & 2] & ?] & ?] & 2] & & \\ \hline \end{tabular}
