Homework Help is Here – Start Your Trial Now!
Business
Accounting
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 2 Quarter 2 Year 3 Quarter 1 2 Data 3 Budgeted unit sales Selling price per unit 45,000 $7 65,000 110,00e 65,000 85,000 95,000 A B. F 1 Chapter 8: Applying Excel 2 3 Data Year 3 Quarter 4 2 3 4 5 Budgeted unit sales 45,000 65,000 110,000 65,000 85,000 95,000 6 7 - Selling price per unit 8 Accounts receivable, beginning balance 9 · Sales collected in the quarter sales are made 10 · Sales collected in the quarter after sales are made 11 - Desired ending finished goods inventory is 12 · Finished goods inventory, beginning 13 · Raw materials required to produce one unit 14 · Desired ending inventory of raw materials is 15 · Raw materials inventory, beginning 16 · Raw material costs 17 · Raw materials purchases are paid and 7 per unit 65,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 81,500 18 19 · Accounts payable for raw materials, beginning balance 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?

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 2 Quarter 2 Year 3 Quarter 1 2 Data 3 Budgeted unit sales Selling price per unit 45,000 $7 65,000 110,00e 65,000 85,000 95,000 A B. F 1 Chapter 8: Applying Excel 2 3 Data Year 3 Quarter 4 2 3 4 5 Budgeted unit sales 45,000 65,000 110,000 65,000 85,000 95,000 6 7 - Selling price per unit 8 Accounts receivable, beginning balance 9 · Sales collected in the quarter sales are made 10 · Sales collected in the quarter after sales are made 11 - Desired ending finished goods inventory is 12 · Finished goods inventory, beginning 13 · Raw materials required to produce one unit 14 · Desired ending inventory of raw materials is 15 · Raw materials inventory, beginning 16 · Raw material costs 17 · Raw materials purchases are paid and 7 per unit 65,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 81,500 18 19 · Accounts payable for raw materials, beginning balance 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?

Financial & Managerial Accounting
Financial & Managerial Accounting
14th Edition
ISBN:9781337119207
Author:Carl Warren, James M. Reeve, Jonathan Duchac
Publisher:Carl Warren, James M. Reeve, Jonathan Duchac
Chapter21: Budgeting
Section: Chapter Questions
Problem 21.5BPR: Budgeted income statement and balance sheet As a preliminary to requesting budget estimates of...
See similar textbooks
icon
Related questions
Question
100%

Info in images

A1 fx Chapter 8: Applying Excel A B D E G H 1 Chapter 8: Applying Excel 2 3 Data Year 2 Quarter Year 3 Quarter 4 1 3 4. 1 2 5 Budgeted unit sales 40,000 75,000 100.000 50,000 70,000 80.000 6 $8 per unit $65,000 75% 7· Selling price per unit 8 · Accounts receivable, beginning balance 9 · Sales collected in the quarter sales are made 10 · Sales collected in the quarter after sales are made 11 · Desired ending finished goods inventory is 12 • Finished goods inventory, beginning 13 · Raw materials required to produce one unit 14 • Desired ending inventory of raw materials is 15 · Raw materials inventory, beginning 16 · Raw material costs 17 · Raw materials purchases are paid 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 $81,500 18 and 19 · Accounts payable for raw materials, beginning balance 20 21 Enter a formula into each of the cells marked with a ? below 22 Review Problem: Budget Schedules 23 24 Construct the sales budget Year 2 Quarter Year 3 Quarter 25 1 2 3 1 26 Budgeted unit sales 27 Selling price per unit 28 Total sales 40,000 $8 $320,000 50,000 $8 $800,000 $400,000 70.000 $8 80,000 $8 $640,000 75,000 100,000 $8 $8 $600,000 $560,000 29 Year 2 Quarter 30 Construct the schedule of expected cash collections 31 1 2 3 4 Year $ 65,000 $ 32 Accounts receivable, beginning balance 65,000 33 First-quarter sales 34 Second-quarter sales 35 Third-quarter sales 36 Fourth-quarter sales 37 Total cash collections 240,000 80,000 320,000 450,000 $ 150,000 600,000 600,000 $200,000 800,000 300,000 $500,000 300,000 $2,085,000 $ 305,000 $ 530.000 $ 750,000 38 39 Construct the production budget Year 2 Quarter Year 3 Quarter 40 1 2 3 4 Year 2 100,000 50,000 265,000 70,000 41 Budgeted unit sales 42 Add desired finished goods inventory 43 Total needs 44 Less beginning inventory 45 Required production 40,000 75,000 30.000 80,000 22,500 15,000 21,000 21,000 24.000 62,500 12,000 50,500 115,000 30,000 105,000 71,000 286,000 94,000 15,000 56,000 22,500 12,000 21,000 82,500 85,000 274,000 73,000 46 47 Construct the raw materials purchases budget Year 2 Quarter Year 3 Quarter 48 1 3 4 Year 49 Required production (units) 50 Raw materials required to produce one unit 51 Production needs (pounds) 52 Add desired ending inventory of raw materials (pounds) 53 Total needs (pounds) 54 Less beginning inventory of raw materials (pounds) 55 Raw materials to be purchased 56 Cost of raw materials per pound 57 Cost of raw materials to be purchased 50,500 82,500 85,000 56,000 274,000 73,000 252,500 412.500 425.000 280,000 1,370,000 365,000 41.250 42,500 28,000 36.500 36.500 316,500 293,750 23,000 270,750 $0.80 455,000 41,250 413,750 $0.80 $331,000 453.000 1,406,500 42,500 410,500 $0.80 $328.400 28,000 288,500 $0.80 $230,800 $1,106,800 23,000 1,383,500 $0.80 $216,600 58 59 Construct the schedule of expected cash payments Year 2 Quarter 60 3 4 Year $ 81,500 129,960 $ 86,640 61 Accounts payable, beginning balance 62 First-quarter purchases 63 Second-quarter purchases 64 Third-quarter purchases $ 81,500 216,600 198,600 $ 132,400 331.000 197,040 $131,360 328,400 cr Couurth quortor purohooo 120 400
Transcribed Image Text:A1 fx Chapter 8: Applying Excel A B D E G H 1 Chapter 8: Applying Excel 2 3 Data Year 2 Quarter Year 3 Quarter 4 1 3 4. 1 2 5 Budgeted unit sales 40,000 75,000 100.000 50,000 70,000 80.000 6 $8 per unit $65,000 75% 7· Selling price per unit 8 · Accounts receivable, beginning balance 9 · Sales collected in the quarter sales are made 10 · Sales collected in the quarter after sales are made 11 · Desired ending finished goods inventory is 12 • Finished goods inventory, beginning 13 · Raw materials required to produce one unit 14 • Desired ending inventory of raw materials is 15 · Raw materials inventory, beginning 16 · Raw material costs 17 · Raw materials purchases are paid 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 $81,500 18 and 19 · Accounts payable for raw materials, beginning balance 20 21 Enter a formula into each of the cells marked with a ? below 22 Review Problem: Budget Schedules 23 24 Construct the sales budget Year 2 Quarter Year 3 Quarter 25 1 2 3 1 26 Budgeted unit sales 27 Selling price per unit 28 Total sales 40,000 $8 $320,000 50,000 $8 $800,000 $400,000 70.000 $8 80,000 $8 $640,000 75,000 100,000 $8 $8 $600,000 $560,000 29 Year 2 Quarter 30 Construct the schedule of expected cash collections 31 1 2 3 4 Year $ 65,000 $ 32 Accounts receivable, beginning balance 65,000 33 First-quarter sales 34 Second-quarter sales 35 Third-quarter sales 36 Fourth-quarter sales 37 Total cash collections 240,000 80,000 320,000 450,000 $ 150,000 600,000 600,000 $200,000 800,000 300,000 $500,000 300,000 $2,085,000 $ 305,000 $ 530.000 $ 750,000 38 39 Construct the production budget Year 2 Quarter Year 3 Quarter 40 1 2 3 4 Year 2 100,000 50,000 265,000 70,000 41 Budgeted unit sales 42 Add desired finished goods inventory 43 Total needs 44 Less beginning inventory 45 Required production 40,000 75,000 30.000 80,000 22,500 15,000 21,000 21,000 24.000 62,500 12,000 50,500 115,000 30,000 105,000 71,000 286,000 94,000 15,000 56,000 22,500 12,000 21,000 82,500 85,000 274,000 73,000 46 47 Construct the raw materials purchases budget Year 2 Quarter Year 3 Quarter 48 1 3 4 Year 49 Required production (units) 50 Raw materials required to produce one unit 51 Production needs (pounds) 52 Add desired ending inventory of raw materials (pounds) 53 Total needs (pounds) 54 Less beginning inventory of raw materials (pounds) 55 Raw materials to be purchased 56 Cost of raw materials per pound 57 Cost of raw materials to be purchased 50,500 82,500 85,000 56,000 274,000 73,000 252,500 412.500 425.000 280,000 1,370,000 365,000 41.250 42,500 28,000 36.500 36.500 316,500 293,750 23,000 270,750 $0.80 455,000 41,250 413,750 $0.80 $331,000 453.000 1,406,500 42,500 410,500 $0.80 $328.400 28,000 288,500 $0.80 $230,800 $1,106,800 23,000 1,383,500 $0.80 $216,600 58 59 Construct the schedule of expected cash payments Year 2 Quarter 60 3 4 Year $ 81,500 129,960 $ 86,640 61 Accounts payable, beginning balance 62 First-quarter purchases 63 Second-quarter purchases 64 Third-quarter purchases $ 81,500 216,600 198,600 $ 132,400 331.000 197,040 $131,360 328,400 cr Couurth quortor purohooo 120 400
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 2 Quarter Year 3 Quarter Data 1 3 1 Budgeted unit sales Selling price per unit 45,000 $7 65,000 110,000 65,000 85,000 95,000 A B D F G 1 Chapter 8: Applying Excel 2 3 Data Year 3 Quarter 4. 2 3 4 1 2 5 Budgeted unit sales 45,000 65,000 110,000 65,000 85,000 95,000 6 7 - · Accounts receivable, beginning balance • Sales collected in the quarter sales are made • Selling price per unit 7 per unit 8. 65,000 9 · 75% 10 · Sales collected in the quarter after sales are made 25% 11 · Desired ending finished goods inventory is 12 · Finished goods inventory, beginning 13 · Raw materials required to produce one unit 30% of the budgeted unit sales of the next quarter 12,000 units 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 60% in the quarter the purchases are made 40% in the quarter following purchase 81,500 17 · Raw materials purchases are paid 18 and 19 · Accounts payable for raw materials, beginning balance 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 materials for the year e. After seeing this revised budget, the production manager cautioned that due to the current production constraint, a complex milling machine, the plant can produce no more than 80,000 units in any one quarter. Is this a potential problem? No O Yes
Transcribed Image Text: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 2 Quarter Year 3 Quarter Data 1 3 1 Budgeted unit sales Selling price per unit 45,000 $7 65,000 110,000 65,000 85,000 95,000 A B D F G 1 Chapter 8: Applying Excel 2 3 Data Year 3 Quarter 4. 2 3 4 1 2 5 Budgeted unit sales 45,000 65,000 110,000 65,000 85,000 95,000 6 7 - · Accounts receivable, beginning balance • Sales collected in the quarter sales are made • Selling price per unit 7 per unit 8. 65,000 9 · 75% 10 · Sales collected in the quarter after sales are made 25% 11 · Desired ending finished goods inventory is 12 · Finished goods inventory, beginning 13 · Raw materials required to produce one unit 30% of the budgeted unit sales of the next quarter 12,000 units 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 60% in the quarter the purchases are made 40% in the quarter following purchase 81,500 17 · Raw materials purchases are paid 18 and 19 · Accounts payable for raw materials, beginning balance 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 materials for the year e. After seeing this revised budget, the production manager cautioned that due to the current production constraint, a complex milling machine, the plant can produce no more than 80,000 units in any one quarter. Is this a potential problem? No O Yes
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps with 4 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Database design
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Financial & Managerial Accounting
Financial & Managerial Accounting
Accounting
ISBN:
9781337119207
Author:
Carl Warren, James M. Reeve, Jonathan Duchac
Publisher:
Cengage Learning
SEE MORE TEXTBOOKS