Scribd Logo
Upload

Welcome to Scribd!

  • 29 views

    Excel Budget Problem

    Uploaded by

    api-313254091
    This document contains a master budget for Wasatch Manufacturing for the months of January through May. It includes budgets for sales, production, direct materials, direct labor, manufacturing overhead, operating expenses, and cash. The combined cash budget shows expected cash collections, cash payments for expenses, and ending cash balances each month. It also includes calculations of budgeted manufacturing cost per unit.

    Copyright:

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

    Excel Budget Problem

    Uploaded by

    api-313254091
    29 views5 pages
    This document contains a master budget for Wasatch Manufacturing for the months of January through May. It includes budgets for sales, production, direct materials, direct labor, manufacturing overhead, operating expenses, and cash. The combined cash budget shows expected cash collections, cash payments for expenses, and ending cash balances each month. It also includes calculations of budgeted manufacturing cost per unit.

    Original Title

    excel budget problem

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains a master budget for Wasatch Manufacturing for the months of January through May. It includes budgets for sales, production, direct materials, direct labor, manufacturing overhead, operating expenses, and cash. The combined cash budget shows expected cash collections, cash payments for expenses, and ending cash balances each month. It also includes calculations of budgeted manufacturing cost per unit.

    Copyright:

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

    Excel Budget Problem

    Uploaded by

    api-313254091
    This document contains a master budget for Wasatch Manufacturing for the months of January through May. It includes budgets for sales, production, direct materials, direct labor, manufacturing overhead, operating expenses, and cash. The combined cash budget shows expected cash collections, cash payments for expenses, and ending cash balances each month. It also includes calculations of budgeted manufacturing cost per unit.

    Copyright:

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

    Introduction to Management Accounting Solutions Manual

    WASATCH MANUFACTURING
    Master Budget

    Sales Budget
    December January February March April May
    Unit sales 8,444 8,900 9,900 9,200 9,500 8,600
    Unit selling price $ 9 $ 9 $ 9 $ 9 $ 9 $ 9
    Total sales Revenue $ 76,000 $ 80,100 $ 89,100 $ 82,800 $ 85,500 $ 77,400

    Req. 1

    Cash Collections Budget


    January February March Quarter
    Cash sales $28,035 $31,185 $28,980 $88,200
    Credit sales $ 52,065.00 $ 57,915.00 $ 53,820.00 $163,800
    Total collections $80,100 $89,100 $82,800 $252,000

    Req. 2

    Production Budget
    January February March Quarter
    Unit sales 8,900 9,900 9,200 $28,000
    Plus: Desired ending inventory 1,485 1,380 1,425 1,425
    Total needed 10,385 11,280 10,625 29,425
    Less: Beginning inventory 1,335 1,485 1,380 1,335
    Units to produce 9,050 9,795 9,245 28,090

    Req. 3

    Direct Materials Budget


    January February March Quarter

    Chapter 9: The Master Budget and Responsibility Accounting 1


    Introduction to Management Accounting Solutions Manual

    Units to be produced 9,050 9,795 9,245 28,090


    Multiply by: Quantity of DM needed per unit 2 2 2 2
    Quantity of DM needed for production 18,100 19,590 18,490 56,180
    Plus: Desired ending inventory of DM 1,959 1,849 1,787 1,787
    Total quantity of DM needed 20,059 21,439 20,277 57,967
    Less: Beginning inventory of DM 1,810 1,959 1,849 1,810
    Quantity of DM to purchase 18,249 19,480 18,428 56,157
    Multiply by: Cost per pound $ 1.50 $ 1.50 $ 1.50 $ 1.50
    Total cost of DM purchases $27,374 $29,220 $27,642 $84,236

    April May
    Unit Sales 9,500 8,600
    Plus: Desired End Inventory 860
    Total Needed 10,360
    Less: Beginning Inventory 1,425
    Units to produce 8,935
    DM needed per unit 2
    Quantity of DM needed for production 17,870

    Req. 4

    Cash Payments for Direct Material Purchases Budget


    January February March Quarter
    December purchases (From AP) $22,000 $22,000
    January purchases $8,212 $19,161 $27,374
    February purchases $5,844 $20,454 $26,298
    March purchases $8,293 $8,293
    Total disbursements $30,212 $25,005 $28,747 $83,964

    Req. 5

    Chapter 9: The Master Budget and Responsibility Accounting 2


    Introduction to Management Accounting Solutions Manual

    Cash Payments for Direct Labor Costs


    January February March Quarter
    Direct Labor $3,530 $3,820 $3,606 $10,955

    Req. 6

    Cash Payments for Manufacturing Overhead Budget


    January February March Quarter
    Rent (fixed) $6,500 $6,500 $6,500 $19,500
    Other MOH (fixed) $2,100 $2,100 $2,100 $6,300
    Variable manufacturing overhead $ 12,670 $ 13,713 $ 12,943 $39,326
    Total disbursements $21,270 $22,313 $21,543 $65,126

    Req. 7

    Cash Payments for Operating Expenses Budget


    January February March Quarter
    Variable operating expenses $ 10,680 $ 11,880 $ 11,040 $ 33,600
    Fixed operating expenses $ 1,400 $ 1,400 $ 1,400 $ 4,200
    Total disbursements $ 12,080 $ 13,280 $ 12,440 $ 37,800

    Req. 8

    Combined Cash Budget


    January February March Quarter
    Cash balance, beginning $6,000 $5,008 $11,860 $6,000
    Plus: cash collections (req. 1) 80,100 89,100 82,800 252,000
    Total cash available 86,100 94,108 94,660 258,000
    Less cash payments:
    DM purchases (req. 4) 30,212 25,005 28,747 83,964
    Direct labor (req. 5) 3,530 3,820 3,606 10,955

    Chapter 9: The Master Budget and Responsibility Accounting 3


    Introduction to Management Accounting Solutions Manual

    MOH costs (req 6) 21,270 22,313 21,543 65,126


    Operating expenses (req 7) 12,080 13,280 12,440 37,800
    Tax payment 10,800 10,800
    Equipment purchases 15000 6,000 4000 25,000
    Total cash payments 82,092 81,219 70,335 233,645
    Ending cash before financing 4,008 12,890 24,325 24,355
    Financing:
    Borrowings 1,000 1,000
    Repayments -1000 -1,000
    Interest -30 -30
    Total financing 1,000 -1,030 0 -30
    Cash balance, ending $5,008 $11,860 $24,325 $24,325

    Req. 9

    Budgeted Manufacturing Cost per Unit


    Direct materials cost per unit $3.00
    Direct labor cost per unit $0.39
    Variable MOH cost per unit $1.40
    Fixed MOH per unit (given in problem) $0.80
    Cost of manufacturing each unit $5.59

    Req. 10

    Damon Manufacturing
    Budgeted Income Statement
    For the Quarter Ended March 31
    Sales $ 252,000
    Cost of goods sold 156,520
    Gross profit 95,480
    Operating expenses 37,800
    Depreciation expense 5,200

    Chapter 9: The Master Budget and Responsibility Accounting 4


    Introduction to Management Accounting Solutions Manual

    Operating income 52,480


    Less: interest expense -30
    Less: provision for income tax 14,686
    Net income $37,764

    Chapter 9: The Master Budget and Responsibility Accounting 5

    You might also like