Question

In: Accounting

Chapter 9: Applying Excel Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1...

Chapter 9: Applying Excel
Data Year 2 Quarter Year 3 Quarter
1 2 3 4 1 2
Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000
• Selling price per unit $8 per unit
• Accounts receivable, beginning balance $65,000
• Sales collected in the quarter sales are made 75%
• Sales collected in the quarter after sales are made 25%
• Desired ending finished goods inventory is 30% of the budgeted unit sales of the next quarter
• Finished goods inventory, beginning 12,000 units
• Raw materials required to produce one unit 5 pounds
• Desired ending inventory of raw materials 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 each of 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 ? ? ? ? ? ?
Selling price per unit ? ? ? ? ? ?
Total sales ? ? ? ? ? ?
Construct the schedule of expected cash collections Year 2 Quarter
1 2 3 4 Year
Accounts receivable, beginning balance ? ?
First-quarter sales ? ? ?
Second-quarter sales ? ? ?
Third-quarter sales ? ? ?
Fourth-quarter sales ? ?
Total cash collections ? ? ? ? ?
Construct the production budget Year 2 Quarter Year 3 Quarter
1 2 3 4 Year 1 2
Budgeted unit sales ? ? ? ? ? ? ?
Add desired finished goods inventory ? ? ? ? ? ?
Total needs ? ? ? ? ? ?
Less beginning inventory ? ? ? ? ? ?
Required production ? ? ? ? ? ?
Construct the raw materials purchases budget Year 2 Quarter Year 3 Quarter
1 2 3 4 Year 1
Required production (units) ? ? ? ? ? ?
Raw materials required to produce one unit ? ? ? ? ? ?
Production needs (pounds) ? ? ? ? ? ?
Add desired ending inventory of raw materials (pounds) ? ? ? ? ?
Total needs (pounds) ? ? ? ? ?
Less beginning inventory of raw materials (pounds) ? ? ? ? ?
Raw materials to be purchased ? ? ? ? ?
Cost of raw materials per pound ? ? ? ? ?
Cost of raw materials to be purchased ? ? ? ? ?
Construct the schedule of expected cash payments Year 2 Quarter
1 2 3 4 Year
Accounts payable, beginning balance ? ?
First-quarter purchases ? ? ?
Second-quarter purchases ? ? ?
Third-quarter purchases ? ? ?
Fourth-quarter purchases ? ?
Total cash disbursements ? ? ? ? ?

Check your worksheet by changing the budgeted unit sales in Quarter 2 of Year 2 in cell C5 to 75,000 units. The total expected cash collections for the year should now be $2,085,000. If you do not get this answer, find the errors in your worksheet and correct them.

plz show the fomula for caculation

Solutions

Expert Solution

The following is the budgets of sales, production, materials purchase, schedule of cash inflows and outflows:

If the sales is Year 2 Quarter 2 changes, the following will be the budget:

Calculations:

Sales:

Total Sales = Sale units x Selling Price per unit;

Schedule of Cash collections:

3. Production:

Sales units are taken from the question,

Desired ending inventory = Next inventory sales x 30%

Beginning inventory for 1st quarter is given, for remaining quarters, beginning inventory is the ending inventory at the last quarter.

4. Raw Materials:

Production units is taken from Production Budget;

Raw Materials required for one unit is given in the question as 5;

Production needs = Production units x Raw materials required for one unit;

Desired ending inventory of raw materials = Production needs of next quarter x 10%

Beginning inventory for 1st quarter is given, for remaining quarters, beginning inventory is the ending inventory at the last quarter.

5. Schedule of Expected Cash Payments:

.

Hope this is helpful!!


Related Solutions

Chapter 8: Applying Excel Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1...
Chapter 8: Applying Excel Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales        40,000         60,000      100,000      50,000         70,000         80,000 • Selling price per unit $8 per unit • Accounts receivable, beginning balance $65,000 • Sales collected in the quarter sales are made 75% • Sales collected in the quarter after sales are made 25% • Desired ending finished goods inventory is 30% of the budgeted unit sales...
Chapter 9: Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying Excel form and...
Chapter 9: Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell B30 enter the formula "= B20". Notes: In the text, variances are always displayed as positive numbers. To accomplish this, you can use the ABS() function in Excel. For example, the formula in cell C31 would be "=ABS(E31?B31)". Cells D31 through D39 and G31 through G39 already contain formulas to compute...
A B C 1 Chapter 4: Applying Excel 2 3 Data 4 Beginning work in process...
A B C 1 Chapter 4: Applying Excel 2 3 Data 4 Beginning work in process inventory: 5    Units in process 200 6    Completion with respect to materials 10 % 7    Completion with respect to conversion 45 % 8    Costs in the beginning work in process inventory: 9       Materials cost $404 10       Conversion cost $6,210 11 Units started into production during the period 15,000 12 Costs added to production during the period: 13    Materials cost $245,921 14    Conversion cost $996,935...
A B C D E F 1 Chapter 8: Applying Excel 2 3 Data 4 Exhibit...
A B C D E F 1 Chapter 8: Applying Excel 2 3 Data 4 Exhibit 8-8: Standard Cost Card 5 Inputs Standard Quantity Standard Price 6 Direct materials 3.0 pounds $4.00 7 Direct labor 0.50 hours $22.00 per hour 8 Variable manufacturing overhead 0.50 hours $6.00 per hour 9 10 Actual results: 11     Actual output 2,090 units 12     Actual variable manufacturing overhead cost $6,174 13    Actual Quantity Actual price 14     Actual direct materials cost 6,115 pounds $3.9 pounds...
A B C D 1 Chapter 5: Applying Excel 2 3 Data 4 Selling price per...
A B C D 1 Chapter 5: Applying Excel 2 3 Data 4 Selling price per unit $321 5 Manufacturing costs: 6   Variable per unit produced: 7     Direct materials $141 8     Direct labor $69 9     Variable manufacturing overhead $40 10   Fixed manufacturing overhead per year $127,600 11 Selling and administrative expenses: 12   Variable per unit sold $5 13   Fixed per year $65,000 14 15 Year 1 Year 2 16 Units in beginning inventory 0 17 Units produced during the year...
A B C D 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 30,000...
A B C D 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 30,000 units 5 Selling price per unit $70 per unit 6 Variable expenses per unit $42 per unit 7 Fixed expenses $420,000 8 If your formulas are correct, you should get the correct answers to the following questions. (a) What is the break-even in dollar sales?        (b) What is the margin of safety percentage?        (c) What is the degree of operating leverage? (Round...
  Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2   Budgeted unit sales...
  Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2   Budgeted unit sales 50,000 65,000 115,000 70,000 80,000 90,000   Selling price per unit $7 per unit             1 Chapter 7: Applying Excel 2 3 Data Year 2 Quarter Year 3 Quarter 4 1 2 3 4 1 2 5 Budgeted unit sales 50,000 65,000 115,000 70,000 80,000 90,000 6 7 � Selling price per unit $8 per unit 8 � Accounts receivable, beginning balance...
Chapter 13: Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying Excel form and...
Chapter 13: Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell C22 enter the formula "= B10". Note: The present value factors could be computed using the built-in Excel function PV, but we recommend using the formulas in Appendix 13B. Verify that your worksheet matches the example in the text. Check your worksheet by changing the discount rate to 10%. The net...
Chapter 3: Applying Excel 2 3 Enter a formula into each of the cells marked with...
Chapter 3: Applying Excel 2 3 Enter a formula into each of the cells marked with a ? below 4 Review Problem: Activity-Based Costing 5         6 Data 7     Deluxe Tourist 8 Annual sales in units 1,000 9,000 9 Direct materials per unit $33 $33 10 Direct labor-hours per unit 1 1 11 12 Direct labor rate $17 per DLH 13 14 Estimated 15 Overhead Expected Activity 16 Activities and Activity Measures Cost Deluxe Tourist Total 17 Labor...
Consider the following time series data. Quarter Year 1 Year 2 Year 3 1 2 4...
Consider the following time series data. Quarter Year 1 Year 2 Year 3 1 2 4 5 2 4 5 8 3 1 3 4 4 7 9 10 (a) Choose the correct time series plot. (i) (ii) (iii) (iv) - Select your answer -Plot (i)Plot (ii)Plot (iii)Plot (iv)Item 1 What type of pattern exists in the data? - Select your answer -Positive trend pattern, no seasonalityHorizontal pattern, no seasonalityNegative trend pattern, no seasonalityPositive trend pattern, with seasonalityHorizontal pattern, with...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT