Ch 8

Use the attached Excel sheet photo to solve the question asked under

Start with the Problem update

Check your worksheet by changing the budgeted unit sales in Quarter 2 of Year 2 in cell C5 to 75,000 units. The total expected cash collections for the year should now be $2,085,000. The required production for the year should be 274,000 units. The cost of raw materials to be purchased for the year should be $1,106,800, whereas the total cash disbursements for the year should be $1,095,980. If you do not get this answer, find the errors in your worksheet and correct them.

Solve -- Construct Sales Budget

Solve -Construct the schedule of cash flow collections

Solve- Construct the production budget

Solve - Construct raw materials construction budget

Solve- Construct the schedule of cash payments

1 Chapter 8: Applying Excel - Selling price per unit $8 per unit - Accounts receivable, beginning balance $65,000 - Sales collected in the quarter sales are made 75% - Sales collected in the quarter after sales are made 25% - Desired ending finished goods inventory is 30% of the budgeted unit sales of the next quarter 12 - Finished goods inventory, beginning 12,000 units - Raw materials required to produce one unit 5 pounds - Desired ending inventory of raw materials is 10% of the next quarter's production needs - Raw materials inventory, beginning 23,000 pounds - Raw material costs $0.80 per pound - Raw materials purchases are paid 60% in the quarter the purchases are made and - Accounts payable for raw materials, beginning balance $81,500 Enter a formula into each of the cells marked with a ? below Review Problem: Budget Schedules 24 Construct the sales budget 25 Budgeted unit sales Selling price per unit Total sales \begin{tabular}{|r|r|r|r|r|r|} \hline \multicolumn{3}{|c|}{ Year 2 Quarter } & \multicolumn{2}{l|}{ Year 3 Quarter } \\ \hline 1 & 2 & 3 & 4 & 1 & \multicolumn{1}{l|}{2} \\ \hline 40,000 & 75,000 & 100,000 & 50,000 & 70,000 & 80,000 \\ \hline$8 & per unit & $0 & $0 & $0 & $0 \\ \hline? & ? & ? & ? & ? & ? \\ \hline \hline \end{tabular} Construct the schedule of expected cash collections 31 Accounts receivable, beginning balance 33 First-quarter sales 34 Second-quarter sales 35 Third-quarter sales A B C F H 29 Construct the schedule of expected cash collections Year 2 Quarter 31 Accounts receivable, beginning balance 33 First-quarter sales 34 Second-quarter sales 35 Third-quarter sales 36 Fourth-quarter sales 37 Total cash collections