1
answer
0
watching
1,285
views

Requirement 2:

The company has just hired a new marketing manager who insiststhat unit sales can be dramatically increased by dropping theselling price from $8 to $7. The marketing manager would like touse the following projections in the budget:

Check your worksheet by changing the budgeted unit sales inQuarter 2 of Year 2 in cell C5 to 75,000 units. The total expectedcash collections for the year should now be $2,085,000. If you donot get this answer, find the errors in your worksheet and correctthem.

Data Year 2 Quarter Year 3 Quarter
1 2 3 4 1 2
Budgeted unitsales 50,000 70,000 115,000 60,000 80,000 95,000
Selling price perunit $7 per unit

A

B

C

D

E

F

F

1 Chapter 9: Applying Excel
2
3 Data Year 2 Quarter Year 3 Quarter
4 1 2 3 4 1 2
5 Budgeted unit sales 50,000 70,000 115,000 60,000 80,000 95,000
6
7 • Selling price per unit $7 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

c. What is the total cost of raw materials to be purchased forthe year under this revised budget?

d. What are the total expected cash disbursements for rawmaterials for the year under this revised budget?

Here is what I got... but my answers are wrong... pleasehelp

Chapter 9: ApplyingExcel
Data Year 2 Quarter Year 3 Quarter
1 2 3 4 1 2
Budgeted unit sales 50,000 70,000 115,000 60,000 80,000 95,000
• Selling price per unit $7 per unit
• Accounts receivable, beginningbalance $65,000
• Sales collected in the quarter salesare made 75%
• Sales collected in the quarter aftersales are made 25%
• Desired ending finished goodsinventory is 30% of the budgeted unit sales of the next quarter
• Finished goods inventory,beginning 12,000 units
• Raw materials required to produce oneunit 5 pounds
• Desired ending inventory of rawmaterials 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 40% in the quarter following purchase
• Accounts payable for raw materials,beginning balance $81,500
Enter a formula into eachof the cells marked with a ? below
Review Problem: Budget Schedules
Construct the sales budget Year 2 Quarter Year 3 Quarter
1 2 3 4 1 2
Budgeted unit sales 50,000 70,000 115,000 60,000 80,000 95,000
Selling price per unit $7 $7 $7 $7 $7 $7
Total sales $350,000 $490,000 $805,000 $420,000 $560,000 $665,000
Construct the schedule of expected cashcollections Year 2 Quarter
1 2 3 4 Year
Accounts receivable, beginningbalance $ 65,000 $ 65,000
First-quarter sales 262,500 $ 87,500 $ 350,000
Second-quarter sales 367,500 $ 122,500 $ 490,000
Third-quarter sales 603,750 $ 201,250 $ 805,000
Fourth-quarter sales 315,000 $ 315,000
Total cash collections $ 327,500 $ 455,000 $ 726,250 $ 516,250 $ 2,025,000
Construct the production budget Year 2 Quarter Year 3 Quarter
1 2 3 4 Year 1 2
Budgeted unit sales 50,000 70,000 115,000 60,000 295,000 80,000 95,000
Add desired finished goodsinventory 21,000 34,500 18,000 24,000 24,000 28,500
Total needs 71,000 104,500 133,000 84,000 319,000 108,500
Less beginning inventory 12,000 21,000 34,500 18,000 12,000 24,000
Required production 59,000 83,500 98,500 66,000 307,000 84,500
Construct the raw materials purchasesbudget Year 2 Quarter Year 3 Quarter
1 2 3 4 Year 1
Required production (units) 59,000 83,500 98,500 66,000 307,000 84,500
Raw materials required to produce oneunit 5 5 5 5 5 5
Production needs (pounds) 295,000 417,500 492,500 330,000 1,535,000 422,500
Add desired ending inventory of rawmaterials (pounds) 41,750 42,500 28,000 36,500 36,500
Total needs (pounds) 336,750 460,000 520,500 366,500 1,571,500
Less beginning inventory of rawmaterials (pounds) 23,000 41,750 42,500 28,000 23,000
Raw materials to be purchased 313,750 418,250 478,000 338,500 1,548,500
Cost of raw materials per pound $0.80 $0.80 $0.80 $0.80 $0.80
Cost of raw materials to bepurchased $251,000 $334,600 $382,400 $270,800 $1,238,800
Construct the schedule of expected cashpayments Year 2 Quarter
1 2 3 4 Year
Accounts payable, beginningbalance $ 81,500 $ 81,500
First-quarter purchases 150,600 $ 100,400 251,000
Second-quarter purchases 200,760 $ 133,840 $ 334,600
Third-quarter purchases 229,440 $ 152,960 382,400
Fourth-quarter purchases 162,480 $ 162,480
Total cash disbursements $ 232,100 $ 301,160 $ 363,280 $ 315,440 $ 1,211,980

For unlimited access to Homework Help, a Homework+ subscription is required.

Nestor Rutherford
Nestor RutherfordLv2
28 Sep 2019

Unlock all answers

Get 1 free homework help answer.
Already have an account? Log in

Related questions

Weekly leaderboard

Start filling in the gaps now
Log in