Answered step by step
Verified Expert Solution
Question
1 Approved Answer
please answer with formulas Required information The Chapter 8 Form worksheet is to be used to create your own worksheet version of the Review Problem
please answer with formulas
Required information The Chapter 8 Form worksheet is to be used to create your own worksheet version of the Review Problem in the text. 8 Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell B26 enter the formula = BS Check your worksheet by changing the budgeted unit sales in Quarter 2 of Year 2 in cell C5 to 75,000 units. 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 Save your completed Applying Excel form to your computer and then upload it here by clicking "Browse." Next, click "Save." You will use this worksheet to answer the questions in Part 2 download reference file upload a response file (max) Choose Flo Nome chosen Chapter 0 Applying Excel Student Forms se a Log B D E F Data H Budgeted unit sales 1 40,000 Year 2 Quarter 2 2 50.000 100.000 + 50,000 Yoor 3 Quarter 1 2 70 000 80.000 S8 per unit 565.000 75% 25% 30% of the budgeted unit sales of the next quarter 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 $8.500 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 bolow Review Problem: Budget Schedules Construct the sales budget Budgeted unt sales 7 Stina pro per unit Total sales Construct the schedule of expected cash collections 2 counts receivable, tosining balance 13 First-ouarter sales 14 Second quarter sales 15 Third-quan 36 Fourth arteris 37 Total cash collections Your 2 yeter 2 4 1 2 2 2 Yew Quarter 2 > 2 2 ? ? 2 2 2 2 Year 2 Quarter 2 3 4 Ya 2 2 ? ? 2 22 12 2 2 22 Yon 2 Quw 2 2 39 Construct the production budget 10 11 Budgeted unit Sales 42 Add desired finished goods inventory 43 Total needs 14 Less beginning inventory 15 min Chapter 8 Form Year Outer 2 2 2 2 ? 2 2 Local backun on B E 2 2 Year 2 Quarter 3 4 Your 2 2 2 2 7 2 12 22 2 2 4 YOM > Year 2 Quarter 2 3 2 2 2 2 2 Year Quarter 2 2 ? 2 2 ? 7 ? 2 ? E2 ? ? 26 Budgeted unit sales 27 Seing price per unit 28 Total sales 29 30 Construct the schedule of expected cash collections 31 32 Accounts receivable beginning balance 33 First quarter sales 34 Second quarter sales 35 Third-quarter sales 36 Fourth quarter sales 37 Total cash collections 38 30 Construct the production budget 40 41 Badgeted unt sales 42 Aod desired finished goods inventory 63 Total needs 44 Less beginning inventory 45 Required production 46 47 Construct the raw materials purchases budget 48 49 Required production (unit) 50 Raw materials required to producent 51 Production needs pounds) 52 Add desired ending inventory of raw pounds) 53 Total needs pounds 54 Loss beginning inventory of raw materiais (pounds) 55 ww materials to be purchased 50 Cost of raw materials per pound 57 Cost of raw mateso be purchased be 59 Construct the schedule of expected cash payments BO 01 Accounts payable beginning 62 First quarter purchase 03 Second quarter purchases 04 Third-quarter purchases 65 Fourth quarter uchas 00 Total cash disburse 07 09 Chapter Year 3 Quarter Year 2 2 Year 2 arter 2 7 2 2 2 2 2 2 2 2 7 2 2 2 2 ? 2 2 Year 2 Quarter Year 7 7 2 Check my work formula Requirement 2 The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price from $8 to $7. The marketing manager would like to use the following projections in the budget Year Quarter Year Quarter DER 1 1 mated unit sales 45.000 65.000 120,000 60,000 90,000 Beli pice per unit 12 95.000 B Chapter 3: Applying Excel D E F G 1 2 3 Data 1 45.000 2 65.000 Year 3 1 60.000 00.000 3 120.000 5 Budged units 6 2 15.000 $ $ - Accounts, beginning to Sesotected in our made 10 Schwester where we 11 Ordning doo 12 13 14 Hawtoprocent 14 Desengry of rows 15 by beginning 36 min 17 wwweped T. 65.000 75% 25 30% of the budgeted as of the 12.000 Sound 10% of the monede 23.000 de pod son in the purchased 40th 500 1 19 Apollo $ Required information a. What are the total expected cash collections for the year under this revised budget? Expected cash collections for the year b. What is the total required production for the year under this revised budget? Total required production for the year c. What is the total cost of raw materials to be purchased for the year under this revised budget? Total cost of raw materials to be purchased for the year d. What are the total expected cash disbursements for raw materials for the year under this revised budget? Total expected cash disbursements for raw matorials for the year Required information The Chapter 8 Form worksheet is to be used to create your own worksheet version of the Review Problem in the text. 8 Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell B26 enter the formula = BS Check your worksheet by changing the budgeted unit sales in Quarter 2 of Year 2 in cell C5 to 75,000 units. 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 Save your completed Applying Excel form to your computer and then upload it here by clicking "Browse." Next, click "Save." You will use this worksheet to answer the questions in Part 2 download reference file upload a response file (max) Choose Flo Nome chosen Chapter 0 Applying Excel Student Forms se a Log B D E F Data H Budgeted unit sales 1 40,000 Year 2 Quarter 2 2 50.000 100.000 + 50,000 Yoor 3 Quarter 1 2 70 000 80.000 S8 per unit 565.000 75% 25% 30% of the budgeted unit sales of the next quarter 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 $8.500 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 bolow Review Problem: Budget Schedules Construct the sales budget Budgeted unt sales 7 Stina pro per unit Total sales Construct the schedule of expected cash collections 2 counts receivable, tosining balance 13 First-ouarter sales 14 Second quarter sales 15 Third-quan 36 Fourth arteris 37 Total cash collections Your 2 yeter 2 4 1 2 2 2 Yew Quarter 2 > 2 2 ? ? 2 2 2 2 Year 2 Quarter 2 3 4 Ya 2 2 ? ? 2 22 12 2 2 22 Yon 2 Quw 2 2 39 Construct the production budget 10 11 Budgeted unit Sales 42 Add desired finished goods inventory 43 Total needs 14 Less beginning inventory 15 min Chapter 8 Form Year Outer 2 2 2 2 ? 2 2 Local backun on B E 2 2 Year 2 Quarter 3 4 Your 2 2 2 2 7 2 12 22 2 2 4 YOM > Year 2 Quarter 2 3 2 2 2 2 2 Year Quarter 2 2 ? 2 2 ? 7 ? 2 ? E2 ? ? 26 Budgeted unit sales 27 Seing price per unit 28 Total sales 29 30 Construct the schedule of expected cash collections 31 32 Accounts receivable beginning balance 33 First quarter sales 34 Second quarter sales 35 Third-quarter sales 36 Fourth quarter sales 37 Total cash collections 38 30 Construct the production budget 40 41 Badgeted unt sales 42 Aod desired finished goods inventory 63 Total needs 44 Less beginning inventory 45 Required production 46 47 Construct the raw materials purchases budget 48 49 Required production (unit) 50 Raw materials required to producent 51 Production needs pounds) 52 Add desired ending inventory of raw pounds) 53 Total needs pounds 54 Loss beginning inventory of raw materiais (pounds) 55 ww materials to be purchased 50 Cost of raw materials per pound 57 Cost of raw mateso be purchased be 59 Construct the schedule of expected cash payments BO 01 Accounts payable beginning 62 First quarter purchase 03 Second quarter purchases 04 Third-quarter purchases 65 Fourth quarter uchas 00 Total cash disburse 07 09 Chapter Year 3 Quarter Year 2 2 Year 2 arter 2 7 2 2 2 2 2 2 2 2 7 2 2 2 2 ? 2 2 Year 2 Quarter Year 7 7 2 Check my work formula Requirement 2 The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price from $8 to $7. The marketing manager would like to use the following projections in the budget Year Quarter Year Quarter DER 1 1 mated unit sales 45.000 65.000 120,000 60,000 90,000 Beli pice per unit 12 95.000 B Chapter 3: Applying Excel D E F G 1 2 3 Data 1 45.000 2 65.000 Year 3 1 60.000 00.000 3 120.000 5 Budged units 6 2 15.000 $ $ - Accounts, beginning to Sesotected in our made 10 Schwester where we 11 Ordning doo 12 13 14 Hawtoprocent 14 Desengry of rows 15 by beginning 36 min 17 wwweped T. 65.000 75% 25 30% of the budgeted as of the 12.000 Sound 10% of the monede 23.000 de pod son in the purchased 40th 500 1 19 Apollo $ Required information a. What are the total expected cash collections for the year under this revised budget? Expected cash collections for the year b. What is the total required production for the year under this revised budget? Total required production for the year c. What is the total cost of raw materials to be purchased for the year under this revised budget? Total cost of raw materials to be purchased for the year d. What are the total expected cash disbursements for raw materials for the year under this revised budget? Total expected cash disbursements for raw matorials for the year Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started