Scribd Logo
Upload

Welcome to Scribd!

  • 86 views

    Excel Budget Project

    Uploaded by

    api-341205347
    The document is a master budget for Wasatch Manufacturing for the quarter ending March 31. It includes budgets for sales, production, direct materials, direct labor costs, manufacturing overhead costs, operating expenses and cash. The combined cash budget shows opening and closing cash balances for each month. It also calculates a budgeted income statement for the quarter.

    Copyright:

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

    Excel Budget Project

    Uploaded by

    api-341205347
    86 views7 pages
    The document is a master budget for Wasatch Manufacturing for the quarter ending March 31. It includes budgets for sales, production, direct materials, direct labor costs, manufacturing overhead costs, operating expenses and cash. The combined cash budget shows opening and closing cash balances for each month. It also calculates a budgeted income statement for the quarter.

    Original Title

    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 is a master budget for Wasatch Manufacturing for the quarter ending March 31. It includes budgets for sales, production, direct materials, direct labor costs, manufacturing overhead costs, operating expenses and cash. The combined cash budget shows opening and closing cash balances for each month. It also calculates a budgeted income statement for the quarter.

    Copyright:

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

    Excel Budget Project

    Uploaded by

    api-341205347
    The document is a master budget for Wasatch Manufacturing for the quarter ending March 31. It includes budgets for sales, production, direct materials, direct labor costs, manufacturing overhead costs, operating expenses and cash. The combined cash budget shows opening and closing cash balances for each month. It also calculates a budgeted income statement for the quarter.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    of 7

    WASATCH MANUFACTURING

    Master Budget
    NOTE: Cells highlighted in blue contain static numbers (inputs) and not formulas.

    Sales Budget
    December January February
    Unit sales 7000 8000 9000
    Unit selling price $ 15 $ 15 $ 15
    Total sales Revenue $ 105,000 $ 120,000 $ 135,000

    Req. 1

    Cash Collections Budget


    January February March
    Cash sales $36,000 $40,500 $38,700
    Credit sales $ 73,500 $ 84,000 $ 94,500
    Total collections $109,500 $124,500 $133,200

    Req. 2

    Production Budget
    January February March
    Unit sales 8000 9000 8600
    Plus: Desired ending inventory 1800 1720 1880
    Total needed 9800 10720 10480
    Less: Beginning inventory 1600 1800 1720
    Units to produce 8200 8920 8760

    Req. 3

    Direct Materials Budget


    January February March
    Units to be produced 8200 8920 8760
    Multiply by: Quantity of DM needed per unit 4 4 4
    Quantity of DM needed for production 32800 35680 35040
    Plus: Desired ending inventory of DM (15%) 5352 5256 5328
    Total quantity of DM needed 38152 40936 40368
    Less: Beginning inventory of DM 4920 5352 5256
    Quantity of DM to purchase 33232 35584 35112
    Multiply by: Cost per pound $ 1.00 $ 1.00 $ 1.00
    Total cost of DM purchases $33,232 $35,584 $35,112
    April May
    Unit Sales 9400 6800
    Plus: Desired End Inventory (20%) 1360
    Total Needed 10760
    Less: Beginning Inventory 1880
    Units to produce 8880
    DM needed per unit 4
    Quantity of DM needed for production 35520

    Req. 4

    Cash Payments for Direct Material Purchases Budget


    January February March
    December purchases (From AP) $37,000
    January purchases $8,308 $24,924
    February purchases $8,896 $26,688
    March purchases $8,778
    Total disbursements $45,308 $33,820 $35,466

    Req. 5

    Cash Payments for Direct Labor Costs


    January February March
    Units Produced 8,200 8,920 8,760
    Multiply by: Hours per unit 0.1 0.1 0.1
    Direct Labor Hours 820 892 876
    Multiply by: Direct Labor rate per hour $15 $15 $15
    Direct Labor Cost $12,300 $13,380 $13,140
    Req. 6

    Cash Payments for Manufacturing Overhead Budget


    January February March
    Rent (fixed) $10,000 $10,000 $10,000
    Other MOH (fixed) $6,000 $6,000 $6,000
    Variable manufacturing overhead $ 10,250 $ 11,150 $ 10,950
    Total disbursements $26,250 $27,150 $26,950

    Req. 7

    Cash Payments for Operating Expenses Budget


    January February March
    Variable operating expenses $ 10,400 $ 11,700 $ 11,180
    Fixed operating expenses $ 2,200 $ 2,200 $ 2,200
    Total disbursements $ 12,600 $ 13,900 $ 13,380

    Req. 8

    Combined Cash Budget


    January February March
    Cash balance, beginning $17,000 15042 15292
    Plus: cash collections (req. 1) 109500 124500 133200
    Total cash available 126500 139542 148492
    Less cash payments:
    DM purchases (req. 4) 45308 33820 35466
    Direct labor (req. 5) 12300 13380 13140
    MOH costs (req 6) 26250 27150 26950
    Operating expenses (req 7) 12600 13900 13380
    Tax payment 28000
    Equipment purchases 20000 8000 25000
    Total cash payments 116,458 124,250 113,936
    Ending cash before financing 10,042 15,292 34,556
    Financing:
    Borrowings 5000
    Repayments -19000
    Interest -188
    Total financing 5000 0 -19188
    Cash balance, ending $15,042 $15,292 $29,369
    Req. 9

    Budgeted Manufacturing Cost per Unit


    Direct materials cost per unit $4.00
    Direct labor cost per unit $1.50
    Variable MOH cost per unit (given data) $1.25
    Fixed MOH per unit (given data) $0.75
    Cost of manufacturing each unit $7.50

    Req. 10

    Wasatch Manufacturing
    Budgeted Income Statement
    For the Quarter Ended March 31
    Sales $ 384,000
    Cost of goods sold 192000
    Gross profit 192000
    Operating expenses 33280
    Depreciation expense (given data) 10000
    Operating income 148720
    Less: interest expense -188
    Less: provision for income tax 34162
    Net income $109,288
    CTURING
    et

    March April May


    8600 9400 6800
    $ 15 $ 15 $ 15
    $ 129,000 $ 141,000 $ 102,000

    Quarter
    $115,200
    $ 252,000
    $367,200

    Quarter
    25600
    1880
    27480
    1600
    25880

    Quarter
    25880
    4
    103520
    5328
    108848
    4920
    103928
    $ 1.00
    $103,928
    Quarter
    $37,000
    $33,232
    $35,584
    $8,778
    $114,594

    Quarter
    25,880
    0.1
    2,588
    $15
    $38,820
    Quarter
    $30,000
    $18,000
    $32,350
    $80,350

    Quarter
    $ 33,280
    $ 6,600
    $ 33,280

    Quarter
    17000
    367200
    384200

    114594
    38820
    80350
    33280
    28000
    53000
    348,044
    36,156

    5000
    -19000
    -188
    -14188
    $29,369

    You might also like