Chapter 8 Form Chapter 8: Applying Excel DataYear 2 Quarter Year 3 Quarter 123412 Budgeted unit sales40,00060,000100,00050,00070,00080,000 • Selling price per unit$8 • Accounts...

1 answer below »
Excel worksheet


Chapter 8 Form Chapter 8: Applying Excel DataYear 2 QuarterYear 3 Quarter 123412 Budgeted unit sales40,00060,000100,00050,00070,00080,000 • Selling price per unit$8 • Accounts receivable, beginning balance$65,000 • Sales collected in the quarter sales are made75% • Sales collected in the quarter after sales are made25% • Desired ending finished goods inventory is30%of the budgeted unit sales of the next quarter • Finished goods inventory, beginning12,000units • Raw materials required to produce one unit5pounds • Desired ending inventory of raw materials is10%of the next quarter's production needs • Raw materials inventory, beginning23,000pounds • Raw material costs$0.80per pound • Raw materials purchases are paid60%in the quarter the purchases are made and40%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 budgetYear 2 QuarterYear 3 Quarter 123412 Budgeted unit sales?????? Selling price per unit?????? Total sales?????? Construct the schedule of expected cash collectionsYear 2 Quarter 1234Year Beginning balance accounts receivable?? First-quarter sales??? Second-quarter sales??? Third-quarter sales??? Fourth-quarter sales?? Total cash collections????? Construct the production budgetYear 2 QuarterYear 3 Quarter 1234Year12 Budgeted unit sales??????? Add desired ending finished goods inventory?????? Total needs?????? Less beginning finished goods inventory?????? Required production in units?????? Construct the raw materials purchases budgetYear 2 QuarterYear 3 Quarter 1234Year1 Required production (units)?????? Raw materials required to produce one unit (pounds)?????? 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 (pounds)????? Cost of raw materials per pound????? Cost of raw materials to be purchased????? Construct the schedule of expected cash paymentsYear 2 Quarter 1234Year Beginning balance accounts payable?? First-quarter purchases??? Second-quarter purchases??? Third-quarter purchases??? Fourth-quarter purchases?? Total cash disbursements????? Required 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. Units60,00075,000 Fill in the following information from your worksheet Total cash receipts for the year Total cash disbursements for the year
Answered 1 days AfterOct 18, 2021

Answer To: Chapter 8 Form Chapter 8: Applying Excel DataYear 2 Quarter Year 3 Quarter 123412 Budgeted...

Ayushi answered on Oct 20 2021
110 Votes
Chapter 8 Form
    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
    • Accounts receivable, begin
ning 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    40,000    60,000    100,000    50,000    70,000    80,000
    Selling price per unit    $8    $8    $8    $8    $8    $8
    Total sales    $320,000    $480,000    $800,000    $400,000    $560,000    $640,000
    Construct the schedule of expected cash collections    Year 2 Quarter
        1    2    3    4    Year
    Beginning balance accounts receivable    $ 65,000                $ 65,000
    First-quarter sales    $240,000    $ 80,000            $ 320,000
    Second-quarter sales        360,000    $ 120,000        $ 480,000
    Third-quarter sales            600,000    $ 200,000    $ 800,000
    Fourth-quarter sales                300,000    $ 300,000
    Total cash collections    $ 305,000    $ 440,000    $ 720,000    $ 500,000    $ 1,965,000
    Construct the production budget    Year 2 Quarter                    Year 3 Quarter
        1    2    3    4    Year    1    2
    Budgeted unit sales    40,000    60,000    100,000    50,000    250,000    70,000    80,000
    Add desired ending finished goods inventory    18,000    30,000    15,000    21,000    84,000    24,000    - 0
    Total needs    58,000    90,000    115,000    71,000    334,000    94,000
    Less beginning finished goods inventory    12,000    18,000    30,000    15,000    75,000    21,000
    Required production in units    46,000    72,000    85,000    56,000    259,000    73,000
    Construct the raw...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here