Scribd Logo
Upload

Welcome to Scribd!

  • 182 views

    Accounting Excel Budget Project

    Uploaded by

    api-242531880
    The document provides Presidio Manufacturing's master budget for production planning over a 3 month period. It includes sales, cash collections, production, materials purchase, direct labor, manufacturing overhead, operating expenses, and combined cash budgets. Key figures are total sales of $333,600, cost of goods sold of $216,840, operating expenses of $40,150, net income of $49,938. The budgets are used to plan cash flows, production needs, and purchases to ensure the company has sufficient funds to operate while maximizing profits.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    Accounting Excel Budget Project

    Uploaded by

    api-242531880
    182 views8 pages
    The document provides Presidio Manufacturing's master budget for production planning over a 3 month period. It includes sales, cash collections, production, materials purchase, direct labor, manufacturing overhead, operating expenses, and combined cash budgets. Key figures are total sales of $333,600, cost of goods sold of $216,840, operating expenses of $40,150, net income of $49,938. The budgets are used to plan cash flows, production needs, and purchases to ensure the company has sufficient funds to operate while maximizing profits.

    Original Title

    accounting excel budget project

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document provides Presidio Manufacturing's master budget for production planning over a 3 month period. It includes sales, cash collections, production, materials purchase, direct labor, manufacturing overhead, operating expenses, and combined cash budgets. Key figures are total sales of $333,600, cost of goods sold of $216,840, operating expenses of $40,150, net income of $49,938. The budgets are used to plan cash flows, production needs, and purchases to ensure the company has sufficient funds to operate while maximizing profits.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    182 views8 pages

    Accounting Excel Budget Project

    Uploaded by

    api-242531880
    The document provides Presidio Manufacturing's master budget for production planning over a 3 month period. It includes sales, cash collections, production, materials purchase, direct labor, manufacturing overhead, operating expenses, and combined cash budgets. Key figures are total sales of $333,600, cost of goods sold of $216,840, operating expenses of $40,150, net income of $49,938. The budgets are used to plan cash flows, production needs, and purchases to ensure the company has sufficient funds to operate while maximizing profits.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    You are on page 1of 8

    MANAGERIAL ACCOUNTING - Fourth Edition

    Solutions Manual

    PRESIDIO MANUFACTURING
    Master Budget

    Sales Budget
    January
    February
    8300
    9900
    12
    12
    99600
    118800

    March
    9600
    12
    115200

    Cash Collections Budget


    January
    February
    34860
    41580
    46150
    64740
    81010
    106320

    March
    40320
    77220
    117540

    Production Budget
    January
    February
    8300
    9900
    990
    960
    9290
    10860
    830
    990
    8460
    9870

    March
    9600
    900
    10500
    960
    9540

    December
    Unit Sales
    5917
    Unit Selling Price
    12
    Total Sales Revenue
    71000

    Cash Collections (35%)


    Credit Collections (65%)
    Total Cash Collections

    Unit Sales
    Desired Ending Inventory
    Total Needed
    Beginning Inventory
    Units to Produce

    Chapter 9: The Master Budget

    Page 1 of 8

    MANAGERIAL ACCOUNTING - Fourth Edition

    Units to be Produced

    Solutions Manual

    Direct Materials Budget


    January
    February
    8460
    9870

    Quantity of DM needed per unit


    Quantity of DM needed for production
    Desired Ending Inventory of DM
    Total Quantity of DM needed
    Beginning Inventory of DM
    Quantity of DM to purchase
    Cost per pound
    Total cost of DM purchase

    3
    25380
    5922
    31302
    5076
    26226
    2
    52452

    3
    29610
    5724
    35334
    5922
    29412
    2
    58824

    March
    9540
    3
    28620
    5376
    33996
    5724
    28272
    2
    56544

    Cash Payments for Direct Material Purchases Budget


    January
    February
    March
    December purchases (from accounts
    43000
    payable on balance sheet)
    January Purchases
    10490
    41962
    February Purchases
    11765
    47059
    March Purchases
    11309
    Total Disbursements
    53490
    53726
    58368

    Cash Payments for Direct Labor Budget


    January
    February
    March
    Direct labor hours per unit 0.05
    169200
    197400
    190800
    Direct labor rate per hour is $9
    423
    494
    477
    Total cost of direct labor
    3807
    4442
    4293

    Chapter 9: The Master Budget

    Page 2 of 8

    MANAGERIAL ACCOUNTING - Fourth Edition

    Solutions Manual

    Cash Payments for Manufacturing Overhead Budget


    January
    February
    March
    Rent (fixed)
    5500
    5500
    5500
    Other MOH (fixed)
    2900
    2900
    2900
    9306
    10857
    10494
    Variable Conversion Costs
    (DL+MOH)
    Total Disbursements
    17706
    19257
    18894

    Cash Payments for Operating Expenses Budget


    January
    February
    March
    Variable Operating Expenses
    10375
    12375
    12000
    Fixed Operating Expenses
    1800
    1800
    1800
    Total Disbursements
    12175
    14175
    13800

    Cash Balance Beginning


    Add Cash Collections
    Total Cash Available
    Less Cash Disbursements:
    For Materials
    For Conversion Costs
    For Operating Expenses
    For Equiptment
    For Taxes
    Total Disbursements
    Excess (Deficiency in Cash)
    Financing:
    Borrowings
    Repayments
    Interest
    Total Financing
    Cash Balance Ending

    Chapter 9: The Master Budget

    Combined Cash Budget


    January
    February
    4600
    21945
    81010
    106320
    85610
    128265
    53490
    0
    12175
    5000
    70665
    14945

    53726
    0
    14175
    12200
    10000
    90101
    38163

    7000

    3000

    7000
    21945

    3000
    41163

    March
    41163
    117540
    158703
    58368
    0
    13800
    16600
    88768
    69935

    -9000
    -270
    -9270
    60665

    Page 3 of 8

    MANAGERIAL ACCOUNTING - Fourth Edition

    Solutions Manual

    Budgeted Manufacturing Cost per Unit


    Direct Materials: 3lbs. Each *$2 per lb.
    Conversion Costs: $1.10 each
    Fixed MOH: $.70 each
    Cost of Manufacturing each unit

    Budgeted Income Statement


    Sales
    Cost of Good Sold ($7.80* units sold)
    Gross Profit
    Operating Expenses
    Depreciation Expense
    Operating Income
    Less: Interest Expense
    Less: Provision for income tax @30%
    Net Income

    Chapter 9: The Master Budget

    $
    $
    $
    $
    $
    $
    $
    $
    $

    333,600
    (216,840)
    116,760
    40,150
    5,000
    71,610
    (270)
    21,402
    49,938

    Page 4 of 8

    MANAGERIAL ACCOUNTING - Fourth Edition

    Solutions Manual

    NG

    April
    9000
    12
    108000

    May
    8600
    12
    103200

    Quarter
    116760
    188110
    304870

    Quarter
    27800
    900
    28700
    830
    27870

    Chapter 9: The Master Budget

    Page 5 of 8

    MANAGERIAL ACCOUNTING - Fourth Edition

    Solutions Manual

    Quarter
    27870
    3
    83610
    5376
    88986
    5076
    83910
    2
    167820

    dget
    Quarter
    43000
    52452
    58824
    11309
    0
    165585

    Quarter
    557400
    1394
    12542

    Chapter 9: The Master Budget

    Page 6 of 8

    MANAGERIAL ACCOUNTING - Fourth Edition

    Solutions Manual

    get
    Quarter
    16500
    8700
    30657
    0
    55857

    t
    Quarter
    34750
    5400
    40150

    Quarter
    4600
    304870
    309470
    165585
    0
    40150
    33800
    10000
    249535
    59935
    10000
    -9000
    -270
    730
    60665

    Chapter 9: The Master Budget

    Page 7 of 8

    MANAGERIAL ACCOUNTING - Fourth Edition

    $
    $
    $
    $

    Solutions Manual

    6.00
    1.10
    0.70
    7.80

    Chapter 9: The Master Budget

    Page 8 of 8

    You might also like