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 |
||||
1 | 2 | 3 | 4 | 1 | 2 | |
Budgeted unit sales | 50,000 | 70,000 | 120,000 | 75,000 | 80,000 | 100,000 |
Selling price per unit | $7 | per unit | ||||
a. |
What are the total expected cash collections for the year under this revised budget? |
b. |
What is the total required production for the year under this revised budget? |
c. |
What is the total cost of raw materials to be purchased for the year under this revised budget? |
d. |
What are the total expected cash disbursements for raw materials for the year under this revised budget? |
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 90,000 units in any one quarter. Is this a potential problem? |
||||
A B C D E F F 1Chapter 8: Applying Excel 2 3DataYear 2 QuarterYear 3 Quarter4 1234125Budgeted unit sales50,000 70,000 120,000 75,000 80,000 100,000 6 7 • Selling price per unit$7per unit 8 • Accounts receivable, beginning balance$65,000 9 • Sales collected in the quarter sales are made75% 10 • Sales collected in the quarter after sales are made25% 11 • Desired ending finished goods inventory is30%of the budgeted unit sales of the next quarter 12 • Finished goods inventory, beginning12,000units 13 • Raw materials required to produce one unit5pounds 14 • Desired ending inventory of raw materials is10%of the next quarter's production needs 15 • Raw materials inventory, beginning23,000pounds 16 • Raw material costs$0.80per pound 17 • Raw materials purchases are paid60%in the quarter the purchases are made 18 and40%in the quarter following purchase 19 • Accounts payable for raw materials, beginning balance$81,500 20 |
Question 1
Since 75% of AR is collected in the quarter of the sale and the remaining in the subsequent quarter, the opening AR of $65,500 is assumed to be 25% of the sales from Year 1-Quarter 4 fully collected in Year 2 Quarter 1
Sales of Quarter 1, Quarter 2 and Quarter 3 will be fully collected as well by Quarter 4 Year 2.
(50,000 + 70,000 + 120,000) X $7 = $1,680,000
75% of Quarter 4 sales will be collected in Quarter 4 itself with remaining being uncollected as of the end of Year 2.
75% X 75,000 X $7 = $393,750
The Total Collections is Year 2 is 65,500 + 1,680,000 + 393,750 = $2,139,250
Question 2
The Total Sales for the Year is 50000 + 70000 + 120000 + 75000 = 315,000
The Opening Inventory of Finished Goods is 12,000
The Desired Closing Inventory is 30% of Q1 Year 3 = 30% X 80000 = 24,000
The Production Required for the Year is therefore = Closing + Sales - Opening = 24,000 + 315,000 - 12,000 = 327,000 Units
Question 3
Raw Materials for the Production in 2017 is 327,000 X 5 Pounds = 1,635,000 Units
Opening Raw Materials = 23,000
Expected Closing Raw Materials = 10% X 86,000 X 5 = 43,000
Required of Cost of Raw Materisl to be Purchased = (43,000 + 1,635,000 - 23,000) X $0.80 = $1,324,000
Note: Production Requirement in Q1 Year 3
Opening Inventory from Q4 Year 2 = 24,000
Units Sales = 80,000
Desired Closing Inventory = 30% X 100,000 = 30,000
Units to be Produced in Q1 Year 3 = 30,000 + 80,000 - 24,000 = 86,000
Question 4
The Opening Accounts Payable of $81,500 is paid in Year 2
The Purchases from Q1 to Q3 is also expected to be paid.
Of the Total Purchases during the Year of $1,324,000, Q1 to Q3 purchase is 1,324,000 - 304,400 = 1,019,600
Of the Q4 Purchases of $304,400, 60% is paid in Q4 amounting to $152,500
Hence the Total Disbursements in the Year is $81,500 + $1,019,600 + $152,500 = $1,253,300
Note: Q4 Raw Material Stock Details
Production Requirement = 75,000 X 5 = 375,000
Opening Stock = 10% X 375,000 = 37,500
Closing Stock = 43,000
Total Purchase Requirement in Q4 = (43,000 + 375,000 - 37,500) X 0.8 = 380,500 X 0.8 = 304,400
Get Answers For Free
Most questions answered within 1 hours.