I'm very bad at accounting and need help with this assignment. It is to be made into an excel document.
REVI pual product that hes pe sales in the third quakes. The fol- lowing information concame operations for Yes ed for the but two quarters of Year 3: The company's single product sells for $3 per unit. Budgeted unit sales for the next six quarters are as Follows (all calss are on credit) Budgeted unit sales . . .. . ... 40,000 80,000 100,000 50,000 70,000 80,000 Sales me collected in the following pattern: 75% in the quarter the sales are made, and the remaining 25% in the following quarter. On January 1, Year 2, the company's balance sheet showed $65,000 in accounts receivable, all of which will be collected in the first quarter of the year. Bad debts are negligible and can be ignored. C. The company desires an ending finished goods inventory at the end of each quarter equal to 30% of the budgeted unit sales for the next quarter. On December 31, Year 1, the company had 12,000 units on hand. Five pounds of raw materials are required to complete one unit of product. The company requires ending raw materials inventory at the end of each quarter equal to 10% of the following quarter's production needs. On December 31, Year 1, the company had 23,000 pounds of raw materials on band. C. The raw material costs $0.80 per pound. Raw material purchases are paid for in the following pattern: 60% paid in the quarter the purchases are made, and the remaining 40% paid in the following quarter, On January 1, Year 2, the company's balance sheet showed $81,500 in accounts payable for raw material purchases, all of which will be paid for in the first quarter of the year. Required: Prepare the following budgets and schedules for the year, showing both quarterly and total figures: 1. A sales budget and a schedule of expected cash collections. 2. A production budget. A direct materials budget and a schedule of expected cash payments for purchases of materials. Solution to Review Problem 1. The sales budget is prepared as follows: Year 2 Quart Budgeted unit sales .. 40,000 60,000 100.000 50,000 250,000 Selling price per unit x $8 x $8 x 50 x 58 x $8 Total sales ... $320.000 $480,000 $400,COO $2,000,000 Based on the budgeted sales above, the schedule of expected cash collections is prepared as follows: Year. 2 Quarter Year Beginning accounts receivable. . ..... $ 65,000 85,060 First-quarter sales ($320,000 x 75%, 25%) 240.C00 5 80,000 320,000 Second-quarter sales ($480.000 x 75%, 25%) 360.000 $120,000 480,000 Third-quarter sales ($800,000 x 75%, 25%) 600,000 $200.000 800,000 Fourth quarter sales ($400,000 x 75%) 300.000 300,000 Total cash collections $440.000 $720,000 $500.000 $1,965,000REVI pual product that hes pe sales in the third quakes. The fol- lowing information concame operations for Yes ed for the but two quarters of Year 3: The company's single product sells for $3 per unit. Budgeted unit sales for the next six quarters are as Follows (all calss are on credit) Budgeted unit sales . . .. . ... 40,000 80,000 100,000 50,000 70,000 80,000 Sales me collected in the following pattern: 75% in the quarter the sales are made, and the remaining 25% in the following quarter. On January 1, Year 2, the company's balance sheet showed $65,000 in accounts receivable, all of which will be collected in the first quarter of the year. Bad debts are negligible and can be ignored. C. The company desires an ending finished goods inventory at the end of each quarter equal to 30% of the budgeted unit sales for the next quarter. On December 31, Year 1, the company had 12,000 units on hand. Five pounds of raw materials are required to complete one unit of product. The company requires ending raw materials inventory at the end of each quarter equal to 10% of the following quarter's production needs. On December 31, Year 1, the company had 23,000 pounds of raw materials on band. C. The raw material costs $0.80 per pound. Raw material purchases are paid for in the following pattern: 60% paid in the quarter the purchases are made, and the remaining 40% paid in the following quarter, On January 1, Year 2, the company's balance sheet showed $81,500 in accounts payable for raw material purchases, all of which will be paid for in the first quarter of the year. Required: Prepare the following budgets and schedules for the year, showing both quarterly and total figures: 1. A sales budget and a schedule of expected cash collections. 2. A production budget. A direct materials budget and a schedule of expected cash payments for purchases of materials. Solution to Review Problem 1. The sales budget is prepared as follows: Year 2 Quart Budgeted unit sales .. 40,000 60,000 100.000 50,000 250,000 Selling price per unit x $8 x $8 x 50 x 58 x $8 Total sales ... $320.000 $480,000 $400,COO $2,000,000 Based on the budgeted sales above, the schedule of expected cash collections is prepared as follows: Year. 2 Quarter Year Beginning accounts receivable. . ..... $ 65,000 85,060 First-quarter sales ($320,000 x 75%, 25%) 240.C00 5 80,000 320,000 Second-quarter sales ($480.000 x 75%, 25%) 360.000 $120,000 480,000 Third-quarter sales ($800,000 x 75%, 25%) 600,000 $200.000 800,000 Fourth quarter sales ($400,000 x 75%) 300.000 300,000 Total cash collections $440.000 $720,000 $500.000 $1,965,000REVI pual product that hes pe sales in the third quakes. The fol- lowing information concame operations for Yes ed for the but two quarters of Year 3: The company's single product sells for $3 per unit. Budgeted unit sales for the next six quarters are as Follows (all calss are on credit) Budgeted unit sales . . .. . ... 40,000 80,000 100,000 50,000 70,000 80,000 Sales me collected in the following pattern: 75% in the quarter the sales are made, and the remaining 25% in the following quarter. On January 1, Year 2, the company's balance sheet showed $65,000 in accounts receivable, all of which will be collected in the first quarter of the year. Bad debts are negligible and can be ignored. C. The company desires an ending finished goods inventory at the end of each quarter equal to 30% of the budgeted unit sales for the next quarter. On December 31, Year 1, the company had 12,000 units on hand. Five pounds of raw materials are required to complete one unit of product. The company requires ending raw materials inventory at the end of each quarter equal to 10% of the following quarter's production needs. On December 31, Year 1, the company had 23,000 pounds of raw materials on band. C. The raw material costs $0.80 per pound. Raw material purchases are paid for in the following pattern: 60% paid in the quarter the purchases are made, and the remaining 40% paid in the following quarter, On January 1, Year 2, the company's balance sheet showed $81,500 in accounts payable for raw material purchases, all of which will be paid for in the first quarter of the year. Required: Prepare the following budgets and schedules for the year, showing both quarterly and total figures: 1. A sales budget and a schedule of expected cash collections. 2. A production budget. A direct materials budget and a schedule of expected cash payments for purchases of materials. Solution to Review Problem 1. The sales budget is prepared as follows: Year 2 Quart Budgeted unit sales .. 40,000 60,000 100.000 50,000 250,000 Selling price per unit x $8 x $8 x 50 x 58 x $8 Total sales ... $320.000 $480,000 $400,COO $2,000,000 Based on the budgeted sales above, the schedule of expected cash collections is prepared as follows: Year. 2 Quarter Year Beginning accounts receivable. . ..... $ 65,000 85,060 First-quarter sales ($320,000 x 75%, 25%) 240.C00 5 80,000 320,000 Second-quarter sales ($480.000 x 75%, 25%) 360.000 $120,000 480,000 Third-quarter sales ($800,000 x 75%, 25%) 600,000 $200.000 800,000 Fourth quarter sales ($400,000 x 75%) 300.000 300,000 Total cash collections $440.000 $720,000 $500.000 $1,965,000B 2 3 Data Year 2 Quarter Year 3 Quarter 4 2 3 4 1 2 5 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,COO 6 7 - Selling price per unit $8 per unit 8 . Accounts receivable, beginning balance $65,000 9 - Sales collected in the quarter sales are made 75% 10 - Sales collected in the quarter after sales are made 25% 11 - Desired ending finished goods inventory is 30% of the budgeted unit sales of the next quarter 12 - Finished goods inventory, beginning 12,000 units 13 - Raw materials required to produce one unit 5 pounds 14 - Desired ending inventory of raw materials is 10% of the next quarter's production needs 15 - Raw materials inventory, beginning 23,000 pounds 16 - Raw material costs $0.80 per pound 17 - Raw materials purchases are paid 60% in the quarter the purchases are made 18 and 40% in the quarter following purchase 19 - Accounts payable for raw materials, beginning balance $81,500 20 21 For Requirement #1, enter a cell reference or formula into each of t 22 cells marked with a ? below. Do NOT just enter a number.B 2 3 Data Year 2 Quarter Year 3 Quarter 4 2 3 4 1 2 5 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,COO 6 7 - Selling price per unit $8 per unit 8 . Accounts receivable, beginning balance $65,000 9 - Sales collected in the quarter sales are made 75% 10 - Sales collected in the quarter after sales are made 25% 11 - Desired ending finished goods inventory is 30% of the budgeted unit sales of the next quarter 12 - Finished goods inventory, beginning 12,000 units 13 - Raw materials required to produce one unit 5 pounds 14 - Desired ending inventory of raw materials is 10% of the next quarter's production needs 15 - Raw materials inventory, beginning 23,000 pounds 16 - Raw material costs $0.80 per pound 17 - Raw materials purchases are paid 60% in the quarter the purchases are made 18 and 40% in the quarter following purchase 19 - Accounts payable for raw materials, beginning balance $81,500 20 21 For Requirement #1, enter a cell reference or formula into each of t 22 cells marked with a ? below. Do NOT just enter a number
