The company has just hired a new marketing manager who insists that unit sales c
ID: 2557649 • Letter: T
Question
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:
1. What are the total expected cash collections for the year under this revised budget?
2. What is the total required production for the year under this revised budget?
3. What is the total cost of raw materials to be purchased for the year under this revised budget?
4. What are the total expected cash disbursements for raw materials for the year under this revised budget?
5. 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 (yes/no)?
Answering using excel would be so helpful too, thanks!
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:
Data Year 2 Quarter Year 3 Quarter 2 50,000 70,000 105,000 60,000 85,00090,000 Budgeted unit sales Selling price per unit $7 per unit 1 Chapter 7: Applying Excel 3 Data Year 2 Quarter Year 3 Quarter 5 Budgeted unit sales 50,000 70,000 105,000 60,000 85,000 90,000 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 16Raw material costs $8 per unit $65,000 ?5°10 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 Raw materials purchases are paid 18 and 60% in the quarter the purchases are made 40% in the quarter following purchase $81,500 19 Accounts payable for raw materials, beginning balance 20Explanation / Answer
Solution:
Part 1 --- Schedule of Expected Cash Collection
Sales Budget
Year 2 Quarter
Year 3 Quarter
1
2
3
4
1
2
Budgeted Unit Sales
50000
70000
105000
60000
85000
90000
Selling Price per unit
$8
$8
$8
$8
$8
$8
Total Sales
$400,000
$560,000
$840,000
$480,000
$680,000
$720,000
Schedule of Expected Cash Collections
Year 2 Quarter
1
2
3
4
Year
Accounts receivable, beginning balance
$65,000
First quarter sales
$300,000
100000
Second Quarter Sales
420000
$140,000
3 Quarter Sales
$630,000
$210,000
4 Quarter Sales
$360,000
Total Cash Collections
$365,000
$520,000
$770,000
$570,000
$2,225,000
Part 2 -- total required production for the year under this revised budget
Production Budget
Year 2 Quarter
Year 3 Quarter
1
2
3
4
Year
1
2
Budgeted Unit Sales
50000
70000
105000
60000
285000
85000
90000
Add: Desired finished goods inventory (30% of the next quarter sales units)
21000
31500
18000
25500
25500
27000
Total needs
71000
101500
123000
85500
310500
112000
Less: beginning inventory (beginning inventory of last month)
12000
21000
31500
18000
12000
25500
Required Production
59000
80500
91500
67500
298500
86500
Part 3 -- total cost of raw materials to be purchased for the year under this revised budget
Raw materials purchases budget
Year 2 Quarter
Year 3 Quarter
1
2
3
4
Year
1
Required Production (units)
59000
80500
91500
67500
298500
86500
Raw material required to produce one unit
5
5
5
5
5
5
Production Needs (pounds)
295000
402500
457500
337500
1492500
432500
Add: Desired Ending Raw Material Inventory (10% of next quarter's production need)
40250
45750
33750
43250
43250
Total Needs (Pounds)
335250
448250
491250
380750
1535750
Less: Beginning Raw material inventory (ending inventory of last month)
23000
40250
45750
33750
23000
Raw materials to be purchased
312250
408000
445500
347000
1512750
Cost of raw materials per pound
$0.80
$0.80
$0.80
$0.80
$0.80
Cost of raw materials to be purchased
$249,800
$326,400
$356,400
$277,600
$1,210,200
Part 4 -- total expected cash disbursements for raw materials for the year under this revised budget
Schedule of expected cash disbursements for raw materials
Year 2 Quarter
1
2
3
4
Year
Accounts Payable, beginning balance
$81,500
1-quarter purchases
$149,880
$99,920
2-quarter purchases
$195,840
$130,560
3-quarter purchases
$213,840
$142,560
4-quarter purchases
$166,560
Total Cash disbursements
$231,380
$295,760
$344,400
$309,120
$1,180,660
Part 5 – Yes, it is a potential problem. Since as per revised budget company is required to produce 80,500 Units in Quarter 2 and 91,500 Units in Quarter 3 to meet the budgeted sales demand. If the production is not happen as anticipated, there will be a problem to company.
Hope the above calculations, working and explanations are clear to you and help you in understanding the concept of question.... please rate my answer...in case any doubt, post a comment and I will try to resolve the doubt ASAP…thank you
Sales Budget
Year 2 Quarter
Year 3 Quarter
1
2
3
4
1
2
Budgeted Unit Sales
50000
70000
105000
60000
85000
90000
Selling Price per unit
$8
$8
$8
$8
$8
$8
Total Sales
$400,000
$560,000
$840,000
$480,000
$680,000
$720,000
Schedule of Expected Cash Collections
Year 2 Quarter
1
2
3
4
Year
Accounts receivable, beginning balance
$65,000
First quarter sales
$300,000
100000
Second Quarter Sales
420000
$140,000
3 Quarter Sales
$630,000
$210,000
4 Quarter Sales
$360,000
Total Cash Collections
$365,000
$520,000
$770,000
$570,000
$2,225,000