# VUsolutions Mgt402 Complete Functional Budget Excel Sheet by vufellows

VIEWS: 28 PAGES: 11

• pg 1
```									Instruction:           Lecture # 35~ 37

1   Unprotect the sheet by putting the passward
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
Selling expences                      XXX                    (XX)
Profit from operation                                         XX
less:       Financial Charges                                            (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