Docstoc

VUsolutions Mgt402 Complete Functional Budget Excel Sheet

Document Sample
VUsolutions Mgt402 Complete Functional Budget Excel Sheet Powered By Docstoc
					Instruction:           Lecture # 35~ 37

        1   Unprotect the sheet by putting the passward
        2   Password is 123
        3   Input the data in the Green cell only
        4   Yellow cell having the data automatically
        5   All calculation will be done automatically for Production Cost Budget
        6   After calculation save the sheet.
        7   Production Budget in units
        8   Production cost Budget in price

Income Statement:

            Sales                                                         XX
less:       Cost of goods sold                                           (XX)
            Gross Profit                                                  XX
less:       Operating expences
            Administrative expences               XXX
            Selling expences                      XXX                    (XX)
            Profit from operation                                         XX
less:       Financial Charges                                            (XX)
add:        other income                                                  XX
            Profit before tax                                             XX
less:       Tax (%)                                                      (XX)
            Profit After tax                                              XX
                                             PRODUCTION COST BUDGET
        Sales is 120% of cost Rs. 3,60,000

        Sales                                                   360,000
        Cost of goods sold                                                 300,000
        Cost of closing finished goods                           40,000
        Cost of Opening finished goods                           60,000
        Units produced                                            2,400
        Direct labour / Unit cost                                              40
        Direct Material / Unit cost                                            30
        Variable FOH / Unit cost                                               20
        Fixed FOH                                                64,000

        Production Cost Budget (under Functional Budget)

        Cost of Goods Sold                                      300,000
Add:    Cost of closing finished goods                           40,000
        Cost of goods available for sale                        340,000
less:   Cost of Opening finished goods                          (60,000)
        Cost of Goods to be produced or manufactured            280,000

        Item wise Cost of Goods to be produced         Units:     2,400


        Direct Material                                          72,000
        Direct labour                                            96,000
        Variable FOH                                             48,000
        Fixed FOH                                                64,000
                                                                280,000
      Calculation for Solution Support

Per unit cost for manufacturing
Per unit cost              280,000   /     2,400
                     =      116.67   / units
                                          Direct Material Cost Budget
        One units of finished goods requires
        Material A                                                    2 kg   5    Rs/kg
        Material B                                                  10 Pc 1.5     Rs/pc
        Material C                                                  0.5 Lts 10    Rs/Lts
                                                                                      A
        Closing units in the inventory                                                 1,200
        Opening units in the inventory                                                 1,000

        Raw Material Consumption Plan:

          Items         Units            Qty         Rate   Amount (Rs.)
        Material A         2400                  2        5     24,000
        Material B         2400                 10      1.5     36,000
        Material C         2400                0.5       10     12,000
        Total                                                    72,000 (Verify with production cost budget)

        Quantity Budget for Material
                                                                                      A
        Units to be consumed                                                           4,800
Add:    Closing units in the inventory                                                 1,200
        Units available for use                                                        6,000
less:   Opening units in the inventory                                                (1,000)
        Units to be purchased                                                         5,000
        Per units cost                                                      Rs.           5
        Cost                                                                           25000
        Total Cost
                                                      Direct Material Cost Budget
                                               * It is prepared both in units & Quantity

                                                 Importance of Material Cost Budget

              B             C                  1. It help in determining min. & max. Stock level (Store)
              4,000             300            2. It help to purchasing dept. in developing & purchasing schedule (EOQ)
              3,000             100            3. It helps in Financial arrangement (Finance)




h production cost budget)


               B            C
              24,000        1,200
               4,000          300
              28,000        1,500
              (3,000)        (100)
              25,000        1,400
                   2           10
               37500        14000
                                      76,500
hedule (EOQ)
                        Direct Labour Cost Budget
           No. of           Work
                                        Rate (Rs.)     units
           worker        perforfance
Grade A             10            168             40   / hr
Grade B             15 per piece                 0.8   /Pc


           No. of         Work
   Items                                  Rate         units Amount (Rs.)
           worker      perforfance
Grade A             10          168               40   / hr       67,200
Grade B             15        2,400              0.8   /Pc        28,800
Total                                                             96,000 (Verify with production cos
                                       Direct Labour Cost Budget

                               * It is prepared both in units & Quantity

                                  Importance of Labour Cost Budget

                               1. Provide the HR Dept. about no. & catergeries of the workforce requirment

                               2. It help to HR dept. for new recurement or identify surplus workforce
                               3. It helps in HR Dept to organized the worker training program.

Verify with production cost budget)
                                      FOH Cost Budget

Variable FOH based on direct labour                         50%
Fixed FOH constant over the period                                 64,000
Direct Labour                                                      96,000

         Items                                          Amount (Rs.)
Variable FOH                                                   48,000 (Verify with production cost budg
Fixed FOH                                                       64,000
Total                                                          112,000

Variable FOH cost Budget Item wise

                                                        Under/over
          Items            Budget        Actual          applied
Indirect labor               18,600        20,000                   1,400
Indirect Material            18,000        15,000                  (3,000)
Power & Fuel                  1,800          2,500                    700
Heating & lighting            4,600          6,000                  1,400
Repair & cleaning             4,000          5,000                  1,000
Spoil Work*                   1,000          1,500                    500
Total                        48,000        50,000
Difference                                                          2,000

Fixed FOH cost Budget Item wise

         Items                                              Cost
Building Rent                                                      30,000
Insurance                                                          10,000
Depreciation (St line)                                              4,000
Supv/ Manager Salary                                               20,000
Total                                                              64,000 (Verify with production cost budg
                                            FOH Cost Budget
                             * It is prepared both in units & Quantity

                                     Key Points FOH Cost Budget

                             1.There are predetermined Rate (OAR)
                             2.Consider both fixed & Variable FOH cost
th production cost budget)   3. Normal Spoil* charged to FOH
                             4. Abnormal Spoil charged to WIP
                             5. Depriciation is fixed if we use straight line method
                             6. Depriciation is variable if we based on no of units produced




th production cost budget)
                                       Master Budget
                                 Budgeted Income statement

        Sales                                                               360,000
less:   Cost of goods sold
        Direct Material                                 72,000
        Direct Labour                                   96,000
        Fixed FOH                                       64,000
        Variable FOH                                    48,000
        Cost of goods manufactured                               280,000
add:    Opening finished goods inventory                          60,000
        Cost of goods avalible for sale                          340,000
less:   Closing finished goods inventory                         (40,000)
        Cost of goods sold                                                  300,000
        Gross Profit                                                         60,000
less:   Operating expences
        Selling expences
        Fixed selling expences                           5,000
        Variable Selling expences                       10,000    15,000
        Administrative expences
        Fixed admins expences                           12,000
        Variable admins expences                         8,000    20,000    (35,000)
        Profit from operation                                                25,000
less:   Financial Charges                                                    (5,000)
add:    other income                                                            -
        Profit before tax                                                    20,000
less:   Tax                40% (of Profit before tax)                        (8,000)
        Profit After tax                                                     12,000

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:28
posted:6/28/2012
language:English
pages:11
Description: VUsolutions Mgt402 Complete Functional Budget Excel Sheet