Calculate Budget Variance in Excel

Document Sample
Calculate Budget Variance in Excel Powered By Docstoc
					Budgeting


  EMBA 5403
  Fall 2008
Introduction

 tool for planning and controlling
  organizations.
 A budget is the quantitative expression
  of a proposed plan of action by
  management for a future time period
  and an aid to the coordination and
  implementation of the plan.


EMBA 5403 Fall 2008   Mugan           2/?
Budgeting Cycle
– Planning the performance of the
  organization
– Providing a frame of reference, a set
  of specific expectations against which
  actual results can be compared
– Investigating variations from plans
– Correcting action follows, if necessary
– Planning again
EMBA 5403 Fall 2008   Mugan            3/?
Budget
 Quantitative plans
      Static –Master Budget
           For a certain level
      Flexible
           Adjusted for different levels and actual level
 Short term or long term
      Capital budgets
      Operational budgets
 Financial and nonfinancial data

EMBA 5403 Fall 2008          Mugan                      4/?
Uses of Budgets
 Goal congruence
 Enhance communication and
  coordination
      Among different units of the business
      Among managers, and subunits
 Performance evaluation
 Determining possible bottlenecks


EMBA 5403 Fall 2008    Mugan                   5/?
Budgets and Feedback
 Feedback through variance analysis
      Variance= deviations from the budget;
       both negative and positive; the amount
       of deviation is important
 Variances provide managers with
      Early warning of problems
      A basis for performance evaluation-who’s
       responsible
      A basis for strategy evaluation
EMBA 5403 Fall 2008    Mugan                    6/?
Budgeting and Human Behavior
 Top-down and bottom-up approach
 Participative budgeting
 Superiors may dominate the budget
  process or hold subordinates accountable
  for events they have no control over
 Subordinates may build “budgetary slack”
  into their budgets
      By underestimating budgeted revenues, or
       overestimating budgeted expenses, in an effort
       to make the resulting budgeted goals (profits)
       more easily attainable

EMBA 5403 Fall 2008       Mugan                     7/?
Comparison
Top-down budgets:
    Top management makes the aggregate forecasts
    then disaggregates down to lower levels
    Decision control more important than decision
       management
Bottom-up budgets (participative budgeting):
    Lower levels know better what they are doing
    They make initial forecasts therefore can be held
       responsible
    Decision management more important than decision
       control
 Top executive officers of firms have final decision
   rights over the entire budget process and resolve
   disputes
 After adoption, the budget acts as a set of contracts
   among the various units of the firm
EMBA 5403 Fall 2008       Mugan                       8/?
Responsibility Centers
 a part, segment, or subunit of an
  organization whose manager is
  accountable for a specified set of
  activities
 Responsibility Accounting – a system
  that measures the plans, budgets,
  actions, and actual results of each
  Responsibility Center

EMBA 5403 Fall 2008   Mugan          9/?
Types of Responsibility Centers
1. Cost – accountable for costs only-
   evaluated based on actual and budgeted
   costs
2. Revenue – accountable for revenues only-
   evaluated based on actual and budgeted
   revenues
3. Profit – accountable for revenues and
   costs-evaluated on the profit
4. Investment – accountable for investments,
   revenues, and costs – return on
   investment; residual income; EVA
EMBA 5403 Fall 2008   Mugan               10/?
Controllability
 What items are under the control of
  the manager?
      Allocated?
      Avoidable?
      Unavoidable?




EMBA 5403 Fall 2008   Mugan             11/?
Budget Process
 Based on historical data and
  changing conditions develop
  estimates
 Goals and budgets are made known
  to key personnel
 Deviations from budgets are
  investigated and corrective action
  taken – managers and accountants

EMBA 5403 Fall 2008   Mugan        12/?
EMBA 5403 Fall 2008   Mugan   13/?
Components of Master Budgets
 Operating Budget – leads to budgeted income
  statement
   Sales budget
   Production budget
           Direct Materials budget
           Direct Labor budget
           Manufacturing overhead budget
 Financial Budget – leads to balance sheet and cash
  flow statement
   Cash collections
   Cash payments
   Purchase of assets
   Payment of dividends
   Borrowing and lending

EMBA 5403 Fall 2008            Mugan                   14/?
Time Coverage of Budgets
 Strategic planning requires long-term
  budgets (2, 5, or 10 years)
 Many firms require managers to prepare
  both short-term and long-term budgets as
  part of the periodic budget review
 Operating budgets-usually prepared on
  monthly, quarterly, annually basis
 Usually the master budget or the static
  budget is prepared on yearly basis
 Flexible budgets – rolling budgets

EMBA 5403 Fall 2008   Mugan              15/?
Master Budget




EMBA 5403 Fall 2008   Mugan   16/?
Basic Operating Budget Steps
1. Prepare the Sales Budget
2. Prepare the Production Budget (in
   Units)
3. Prepare the Direct Materials Usage
   Budget and Direct Materials
   Purchases Budget
4. Prepare the Direct Labor Budget


EMBA 5403 Fall 2008   Mugan             17/?
Basic Operating Budget Steps
5. Prepare the Manufacturing Overhead
   Budget
6. Prepare the Cost of Goods Sold
   Budget
7. Prepare the Selling and
   Administrative Expense Budget
8. Prepare the Budgeted Income
   Statement

EMBA 5403 Fall 2008   Mugan        18/?
Basic Financial Budget Steps
Based on the Operating Budgets:
1. Prepare the Cash Budget
2. Prepare the Budgeted Balance Sheet
3. Prepare the Budgeted Statement of
   Cash Flows




EMBA 5403 Fall 2008   Mugan         19/?
Cash Budget
 A cash budget shows expected cash
  receipts and disbursements; it
  indicates the months having cash
  shortages and excesses.




EMBA 5403 Fall 2008   Mugan           20/?
Sales Budget
 First budget prepared since most
  budgets cannot be prepared without
  an estimate of sales
 A variety of methods are used to
  estimate sales:
         Economic models
         Sales trends
         Trade journals
         Sales force estimates

EMBA 5403 Fall 2008       Mugan    21/?
Production Budget
    Quantity to be produced based on
    following formula:




EMBA 5403 Fall 2008   Mugan            22/?
Example Exercise #1
 VitaPup produces a vitamin-enhanced dog
  food that is sold in Kansas. The company
  expects sales to be 12,600 bags in January,
  14,500 bags in February, and 19,000 bags
  in March. There are 1,260 bags on hand at
  the start of January. VitaPup desires to
  maintain monthly ending inventory equal to
  10% of next month’s expected sales.

 Prepare the production budget for VitaPup
  for the months of January and February.


EMBA 5403 Fall 2008   Mugan               23/?
Example Exercise #1 Solution
 Production Budget for January
       Expected Sales                 12,600
     +Desired Ending Inventory    1,450
     - Beginning Inventory            (1,260)
       Total Production               12,790

 Production Budget for February
        Expected Sales                14,500
     + Desired Ending Inventory   1,900
     - Beginning Inventory            (1,450)
        Total Production              14,950

EMBA 5403 Fall 2008       Mugan             24/?
Direct Material Purchase Budget
 Depends upon the amount needed for
  production and the amount needed
  for ending inventory

 The following formula can be used:




EMBA 5403 Fall 2008   Mugan            25/?
Direct Labor Budget
 Direct labor can be calculated using
  the following formula:
      Number of units produced x Labor hours
       per unit x Rate per hour


 Once calculated, can be used to
  determine the approximate number
  of employees needed

EMBA 5403 Fall 2008   Mugan                26/?
Manufacturing Overhead Budget
 Variable Costs
      Multiply variable cost per unit by
       quantity produced


 Fixed Costs
      Remain relatively constant
      Depreciation could fluctuate based on
       planned acquisitions


EMBA 5403 Fall 2008     Mugan                  27/?
Selling and Administrative Expense
Budget

    Includes the following:
      Salaries

      Advertising

      Office Expenses

      Other General Expenses

EMBA 5403 Fall 2008      Mugan   28/?
Budgeted Income Statement
    Compilation of information provided
    by previously prepared budgets
         Sales Budget
         Direct Materials Budget
         Direct Labor Budget
         Manufacturing Overhead Budget
         Selling and Administrative Expense
          Budget


EMBA 5403 Fall 2008      Mugan                 29/?
Capital Acquisitions Budget
 Acquisitions include:
      Property
      Plant
      Equipment



 Must be carefully planned due to the
  large amounts of cash that could be
  used
EMBA 5403 Fall 2008   Mugan          30/?
Cash Receipts and Disbursements
Budget

 Managers must plan for two items:
      Amount of Cash Flows
      Timing of Cash Flows


 Importance
      Differences between cash flows and
       income
      Anticipate cash shortages or surpluses

EMBA 5403 Fall 2008    Mugan                    31/?
Example Exercise #2
 The Warrenburg Antique Mall budgeted
  credit sales in the first quarter of 2009 to
  be as follows:
               January            $150,000
               February           $160,000
               March       $172,000
     Credit sales in December of 2008 are expected to
        be $200,000. The company expects to collect
        75% of a month’s sales in the month of sale and
        25% in the following month.

 Estimate the cash receipts for January and
  February.
EMBA 5403 Fall 2008       Mugan                     32/?
Example Exercise #2 Solution
 January Estimated Cash Receipts
        December (200,000 x 25%)    $50,000
        January (150,000 x 75%)    $112,500
        Total                      $162,500


 February Estimated Cash Receipts
        January (150,000 x 25%)     $37,500
        February (160,000 x 75%)   $120,000
        Total                      $157,500
EMBA 5403 Fall 2008    Mugan            33/?
Budgeted Balance Sheet
 Last budget prepared

 Sometimes referred to as the pro
  forma balance sheet

 Used to assess the effect of planned
  decisions on the future financial
  position of the firm
EMBA 5403 Fall 2008   Mugan              34/?
Budgeting Overview Flowchart
                                     Operating Budget                   Financial Budget


                                         Revenues
                                          Budget


                        Ending                                               Capital
                                         Production
                       Inventory                                           Expenditures
                                          Budget
                        Budget                                               Budget


                                          Direct
                         Direct                         Manufacturing
                                       Manufacturing
                       Materials                         Overhead
                                        Labor Costs                        Cash Budget
                      Costs Budget                      Costs Budget
                                          Budget



                                       Cost of Goods
                                        Sold Budget
                                                                            Budgeted
                                                                          Balance Sheet

                                         Operating
                                         Expense
                                          Budget                            Budgeted
                                                                           Statement of
                                                                           Cash Flows
                                         Budgeted
                                          Income
                                         Statement



EMBA 5403 Fall 2008                                    Mugan                               35/?
MASTER BUDGET EXAMPLE
               Newport Stationery Store                      Recent and anticipated sales:
      Balance Sheet as of September 30, 2007                 September October November December January
Current Assets                                                  $40.000    $48.000     $60.000 $80.000 $36.000
 Cash                                    $12.000
 Accounts Receivable                      10.000
 Inventory                                63.600
Equipment -- net                         100.000
Liabilities as of September 30            None


Credit sales:                                  75% cash           25% credit
Gross margin percentage                        30%
Salaries and wages (as % of revenues)          15%
Rent (as % of revenues)                         5%
Other operating costs (as % of revenues)        4%
Monthly depreciation                        $1.000
Minimum inventory level                    $30.000
October purchases (light fixtures)            $600
November purchases (light fixtures)           $400
December purchases                              $0
Minimum cash balance                        $8.000
Annual interest rate on borrowings             18%

EMBA 5403 Fall 2008                                  Mugan                                           36/?
     Fill in the schedules
                                                    Schedule A
                                          Budgeted Monthly Cash Receipts
Item                                         September     October     November     December total Q3
Total sales                                 $    40.000 $      48.000 $   60.000   $   80.000 $ 188.000
Credit sales                                     10.000        12.000     15.000       20.000 $    47.000
Cash sales (total sales - credit sales)     $    30.000 $      36.000 $   45.000   $   60.000 $ 141.000



Receipts:
 Cash sales                                             $    36.000   $   45.000   $   60.000   $ 141.000
 Collections on accounts receivable (past
month's credit sales)                                        10.000       12.000       15.000   $  37.000
 Total                                                  $    46.000   $   57.000   $   75.000   $ 178.000




      EMBA 5403 Fall 2008                           Mugan                                       37/?
 Fill in the schedules
                                     Schedule B

                   Budgeted Monthly Cash Disbursements for Purchases
Item                                October      November     December     4th Quarter
Purchases                         $    42.000 $     56.000 $      25.200   $ 123.200
Deduct 2% cash discount                   840        1.120           504         2.464
Disbursements                     $    41.160 $     54.880 $      24.696   $ 120.736




 EMBA 5403 Fall 2008                   Mugan                                  38/?
    Fill in the schedules
                                       Schedule C
                    Budgeted Monthly Cash Disbursements for Operations
Item                                  October     November     December      4th Quarter
Salaries and wages                  $     7.200 $      9.000 $      12.000   $    28.200
Rent                                      2.400        3.000         4.000         9.400
Other cash operating costs                1.920        2.400         3.200         7.520
Total disbursements for operations  $    11.520 $    14.400 $       19.200   $    45.120




    EMBA 5403 Fall 2008                   Mugan                                   39/?
    Fill in the schedules

                                          S chedule D
                          Budgeted Total Monthly Cash Disbursements
Item                                     October      November  December      4th Quarter
Purchases (from Schedule B)            $   41.160   $   54.880   $   24.696   $   120.736
Cash operating costs (from Schedule C)     11.520       14.400       19.200        45.120
Light fixtures                                600          400            0         1.000
Total disbursements                    $   53.280   $   69.680   $   43.896   $   166.856




    EMBA 5403 Fall 2008                     Mugan                                 40/?
   Fill in the schedules

                                         Schedule E
                          Budgeted Cash Receipts and Disbursements
Item                                    October     November     December    4th Quarter
Total reciepts (from Schedule A)      $    46.000 $     57.000 $    75.000   $ 178.000
Total disbursements (from Schedule D)      53.280       69.680      43.896       166.856
Net cash increase (decrease)          $    (7.280) $ (12.680) $     31.104   $    11.144




   EMBA 5403 Fall 2008                    Mugan                                    41/?
      Fill in the schedules
                                                  Schedule F
                                             Financing Required
Item                                    September       October    November    December 4th Quarter
                                                      $
Beginning cash balance (prior month's ending cash balance) 12.000 $     8.720 $    8.040 $ 12.000
Net cash increase (decrease) (from Schedule E)             (7.280)    (12.680)    31.104     11.144
Cash position before borrowing                              4.720      (3.960)    39.144     23.144
Minimum cash balance required                               8.000       8.000      8.000      8.000
Cash excess (deficiency)                                   (3.280)    (11.960)    31.144     15.144
Borrowing required (multiples of $1,000)                    4.000      12.000          0     16.000
Interest payments                                                                    540        540
Borrowing repaid                                                                  16.000     16.000
Ending cash balance                    $     12.000 $       8.720 $     8.040 $   22.604 $   22.604




       EMBA 5403 Fall 2008                      Mugan                                     42/?
  Fill in the schedules
                              Newport Stationery Store
            Budgeted income Statement for quarter ending December 31, 2007

Revenues (schedule A)                                                    $   188.000
Cost of goods sold                                                           131.600
Gross margin                                                                  56.400
Operating costs
 Salaries and wages (Schedule C)                            $   28.200
 Rent (Schedule C)                                               9.400
 Other cash operating costs (Schedule C)                         7.520
 Depreciation                                                    3.000        48.120
Operating income                                                               8.280
Deduct interest expense (Schedule F)                                            (540)
Add purchase discounts (Schedule B)                                            2.464
Net income before taxes                                                  $    10.204


   EMBA 5403 Fall 2008                     Mugan                                43/?
Fill in the schedules
                                        Newport Stationery Store
                                  Balance Sheet as of December 31, 2007
      Assets
      Current assets
       Cash (Schedule F)                                                            $    22.604
       Acounts receivable (December credit sales from Schedule A)                        20.000
       Inventory (buffer inventory + Dec. inventory purchases from
      Sch. B)                                                                            55.200
        Total current assets                                                        $    97.804
      Equipment and fixtures

       Equipment -- net (Sept 30 balance - depreciation for quarter)   $   97.000
       Fixtures (Schedule D)                                                1.000        98.000
        Total                                                                       $   195.804
      Liabilities and Owner's Equity
       Liabilities                                                                         None
       Owners' Equity (Sept. 30 owners' equity + net income for
      quarter)                                                                      $   195.804
                                                                                    $   195.804



EMBA 5403 Fall 2008                                Mugan                                          44/?
Use of Computers in the Budget
Planning Process

 Extremely useful in budgeting process
      Excel Spreadsheet
      Other specialized program


 Allows for company to determine
  effects of a decision on entire budget
      “What if” Analysis



EMBA 5403 Fall 2008    Mugan           45/?
Budgetary Control
 Budgets as a Standard for Evaluation
    Actual amounts are compared with
     budgeted amounts

    Differences between actual and budgeted
     amounts are referred to as budget
     variances

    Budget variances should be investigated
     when they are material
EMBA 5403 Fall 2008   Mugan                46/?
Budgetary Control
 Management must make sure the level of
  activity in the budget is equal to the actual
  level of activity

 Static Budget
      Not adjusted for the actual level of production


 Flexible Budget
      A set of budget relationships that can be
       adjusted for various production activity levels
EMBA 5403 Fall 2008        Mugan                         47/?
Investigating Budget Variances
Causes of Budget Variances
      Budget may not have been well
       conceived

      Conditions may have changed

      Managers may have performed
       particularly well or poorly


EMBA 5403 Fall 2008   Mugan            48/?
Investigating Budget Variances
Management by Exception
      Economical approach

      Only exceptional variances are
       investigated

      Must investigate both unfavorable and
       favorable exceptional variances


EMBA 5403 Fall 2008    Mugan                   49/?
Variances
 If managers learn that specific actions they
  took helped lower the actual costs, then
  they can obtain further cost savings by
  repeating those actions on similar jobs in
  the future
 If the factors causing actual costs to be
  higher than expected can be identified,
  then actions may be taken to prevent those
  factors from recurring in the future
 If cost changes are likely to be permanent,
  cost information can be updated for future
  jobs

EMBA 5403 Fall 2008   Mugan                 50/?
First-Level Variances
 The first-level variance for a cost item is
  the difference between the actual costs and
  the master budget costs for that cost item
 Variances are favorable (F) if the actual
  costs are less than estimated master
  budget costs
 Unfavorable (U) variances arise when
  actual costs exceed estimated master
  budget costs

EMBA 5403 Fall 2008   Mugan                51/?
Planning Variances
 A flexible budget adjusts the master
  budget to reflect the actual volume by using
  standard costs
    Standard costs are budgeted unit costs
    Standards are established per unit of product as
     well as per unit of input
 Cost differences between the master and the
  flexible budget are called planning
  variances
    Reflect the difference between planned output and
     actual output
    Arise entirely because the planned volume of
     activity was not realized
EMBA 5403 Fall 2008      Mugan                     52/?
 Flexible Budget Variances
 Flexible budget variances are the
  differences between the flexible budget and
  the actual results
 Flexible budget variances reflect:
   Quantity variances -- the difference
     between the planned and the actual usage
     of inputs per unit of output
   Cost variances -- the difference between
     the planned and the actual price or cost per
     unit of the various cost items
 EMBA 5403 Fall 2008   Mugan                 53/?
Second & Third-Level Variances
 The second-level variances are the
  planning variance and the flexible budget
  variance

 The direct material flexible budget
  variances and direct labor flexible budget
  variances can be decomposed further into
  third-level variances:
   Efficiency variances
   Price variances

EMBA 5403 Fall 2008   Mugan                   54/?
Direct Material Variances
 The material quantity variance is calculated
  as:
   Quantity variance = (AQ-SQ) x SP
      Where:
      AQ = actual quantity of materials used
      SQ = standard (estimated) quantity of
           materials required
      SP = standard (estimated) price of
           materials

EMBA 5403 Fall 2008   Mugan                  55/?
 Direct Material Variances
 The material price variance is calculated as:
   Price variance = (AP-SP) x AQ
     Where:
     AP = actual price of materials
     SP = standard (estimated) price of materials
     AQ = actual quantity of materials used

   The price variance may, however, be
    calculated using the quantity purchased
    rather than the quantity used

 EMBA 5403 Fall 2008   Mugan                  56/?
Direct Labor Variances
   Efficiency variance = (AH-SH) x SR
   Rate variance = (AR-SR) x AH
       Where:
       AH = actual number of direct labor hours
       AR = actual wage rate & SR = standard rate
       SH = standard (estimated) number of direct labor
             hours
 The sum of the rate variance and the efficiency variance
  equals the total flexible budget direct labor variance
 Standard hours of DL reflects the total hours allowed for the
  actual output level given standard direct labor hours per
  output unit


 EMBA 5403 Fall 2008          Mugan                           57/?
Overhead Variances
 Variable
 Fixed
 The quantity of capacity-related costs
  may not change from period to period,
  but the spending on them may fluctuate
 Monitoring spending variances on
  capacity-related resources is possible
  and desirable
 EMBA 5403 Fall 2008   Mugan         58/?
Variable Overhead Cost Variances
 consist of
    a quantity component called the efficiency
     variance
    and a price component called spending variance
 Variable overhead cost variances may be
  analyzed in a manner similar to direct
  material or direct labor variances when they
  are assigned to products in the traditional
  way – by the direct labors


EMBA 5403 Fall 2008     Mugan                     59/?
Fixed Overhead variances
 Since fixed costs are flexed to reflect the
  actual capacity level; but fixed within a
  range there is no price variance but a
  budget variance
      Actual fixed costs – budgeted fixed costs
        And
 Volume variance to reflect the change in
  capacity
      Fixed overhead rate per driver unit=(actual
       driver units – driver units allowed for the actual
       output level)

EMBA 5403 Fall 2008         Mugan                      60/?
Variances

  Short summary
           Static Budgets


A static budget ( master budget) is prepared for only
          one level of a given type of activity.


     All actual results are compared with the
     original budgeted amounts, even if sales
  volume is more or less than originally planned.



 EMBA 5403 Fall 2008        Mugan                62/?
          Master Budget Variance: Sales


               The variances of actual results
             from the master budget are called
              master (static) budget variances.




EMBA 5403 Fall 2008         Mugan                 63/?
          Master Budget Variance: Expenses


                Actual expenses that exceed
                budgeted expenses result in
               unfavorable expense variances.


            Actual expenses that are less than
               budgeted expenses result in
              favorable expense variances.

EMBA 5403 Fall 2008         Mugan                64/?
          Flexible Budget

        A flexible budget (variable budget) is a
       budget that adjusts for changes in sales
        volume and other cost-driver activities.




EMBA 5403 Fall 2008       Mugan                    65/?
          Flexible Budget Formulas


       To develop a flexible budget, managers
        determine revenue and cost behavior
           (within the relevant range) with
                respect to cost drivers.




EMBA 5403 Fall 2008      Mugan                  66/?
           Evaluation of Financial Performance
                        Flexible
                        budget
                       for actual                Sales-
                          sales       Master     activity
                         activity     budget    variances

Units                  7,000            9,000     2,000 U
Sales               $217,000         $279,000   $62,000 U
Variable costs       152,600          196,200    43,600 F
Contribution margin $ 64,400         $ 82,800   $18,400 U
Fixed costs           70,000           70,000         –
Operating income $ (5,600)           $ 12,800   $18,400 U


 EMBA 5403 Fall 2008         Mugan                    67/?
          Isolating the Causes of Variances


          Effectiveness is the degree to which
           a goal, objective, or target is met.

    Efficiency is the degree to which inputs are
    used in relation to a given level of outputs.

               Performance may be effective,
                  efficient, both, or neither.
EMBA 5403 Fall 2008          Mugan                  68/?
             Flexible-Budget Variances

           Total flexible-budget variance
           = Total actual results
           – Total flexible-budget planned results

 Actual                                             Flexible
 results                                            budget
$(11,570)                                           $(5,600)
                           $5,970 Unfavorable
                        Flexible-budget variances

  EMBA 5403 Fall 2008              Mugan                69/?
          Sales-Activity Variances


                      Total sales-activity variance
                                    =
 Actual sales unit – Master budgeted sales units
                                   ×
         Budgeted contribution margin per unit


EMBA 5403 Fall 2008                Mugan              70/?
Sales price and Sales Volume
Variances
          Sales prices fluctuations cause variance:
           The sales-price variances arises because a
           company increased or decreased its sales
           price when compared with the budgeted
           sales price.
 SPV = (Act. Sale Price – Exp. Sale
  Price) X Act. Sales Volume
          Volume fluctuations cause variance: The
           sales-volume variance, which arises from
           an increase or decrease in units sold.
 SVV = (Act. Sales Vol. – Bud. Sale
  Vol.) X Unit Contribution Margin
EMBA 5403 Fall 2008        Mugan                   71/?
          Variances from Material and Labor
          Standards
       Standard Direct-Materials Cost Allowed:
                 Units of good output achieved
                                   ×
                Input allowed per unit of output
                                   ×
                      Standard unit price of input
                                   =
                       Flexible budget or total
                        standard cost allowed
EMBA 5403 Fall 2008               Mugan              72/?
          Price and Usage Variances



               (Actual price – Standard Price)
                      × Actual quantity



          (Actual quantity – Standard quantity)
                    × Standard price

EMBA 5403 Fall 2008          Mugan                73/?
          Variable-Overhead
          Efficiency Variance



    When actual cost-driver activity differs from
    the standard amount allowed for the actual
       output achieved, a variable-overhead
           efficiency variance will occur.




EMBA 5403 Fall 2008      Mugan                      74/?
          Variable-Overhead
          Spending Variance



      This is the difference between the actual
         variable overhead and the amount
       of variable overhead budgeted for the
          actual level of cost-driver activity.




EMBA 5403 Fall 2008      Mugan                    75/?
         Variable Overhead Variances
 Actual               Flexible Budget     Flexible Budget
Variable                for Variable        for Variable
Overhead                Overhead at         Overhead at
Incurred               Actual Hours       Standard Hours
AH × AR                  AH × SVR              SH × SVR



              Spending              Efficiency
              Variance              Variance
          Spending variance = AH(AR - SVR)
          Efficiency variance = SVR(AH - SH)
EMBA 5403 Fall 2008         Mugan                    76/?
        Fixed Overhead Variances
Actual Fixed          Fixed                Fixed
 Overhead            Overhead             Overhead
 Incurred             Budget              Applied
                                         cost driver ×
                                       predet.overhead rate



          Budget                 Volume
         Variance                Variance
     Predetermined FOVH= Budgeted Fixed OVH/
     normal activity level of cost driver
     Cost driver = units produced, direct labor
 EMBA 5403 Fall 2008
     hours, machine hoursMugan
                             etc.                      77/?
    Variance example 1
Requirement 1
                               Performance Report, April 2004
                                     Actual               Budget          Variance
Units (pounds)                         450.000              400.000        50.000 F
Revenues                            $3.555.000           $3.200.000       355.000 F
Direct materials                       865.000              580.000       285.000 U
Direct manufacturing labor             348.000              336.000        12.000 U
Selling price per pound of cookie        $7,90                $8,00         $0,10 U
Selling-price variance                                                $ 45.000,00 U




    EMBA 5403 Fall 2008                    Mugan                              78/?
     Variance example 1
Requirements 2, 3 and 4
                                            Price             Actual Input Quantity × Efficiency
                   Actual Costs Incurred Variance                 Budgeted Price        Variance            Flexible Budget
                            (1)         (2) = (1) - (3)                 (3)           (4) = (3) - (5)             (5)
Direct Materials
 Cookie mix                     $93.000            $0                        $93.000          $3.000    U          $90.000
 Milk chocolate                 532.000       133.000     U                  399.000          61.500    U          337.500
 Almonds                        240.000             0                        240.000          15.000    U          225.000
                               $865.000       133.000     U                 $732.000         $79.500    U         $652.500

Direct manufacturing labor
 Mixing                        $108.000            $0                       $108.000              $0              $108.000
 Baking                         240.000             0         $              240.000          30.000    F          270.000
                               $348.000            $0                       $348.000         $30.000    F         $378.000




      EMBA 5403 Fall 2008                                 Mugan                                               79/?
   Variance example 2
Requirement 1
                                     Actual    Flexible Budget Static Budget
Production and sales in units          110.000        110.000        120.000
Machine hours                           30.000          33.000        36.000
Fixed manuf. Overhead (FMOH)          $440.000       $450.000      $450.000
Variable manuf. Overhead (VMOH)       $960.000       $990.000    $1.080.000
VMOH per machine hour                   $32,00          $30,00        $30,00
FMOH allocated per machine hour                                       $12,50




    EMBA 5403 Fall 2008           Mugan                               80/?
   Variance example 2
                                                          Actual
                                                        Input Qty.
                                                            ×
                                                        Budgeted                  Flexible
Variable Manufacturing
Overhead                 Actual Costs                      Rate                   Budget


                                $960.000                $900.000                  $990.000




                                           $60.000 U                 $90.000 F
                                           Spending                  Efficiency
                                           variance                  variance

    EMBA 5403 Fall 2008                         Mugan                               81/?
    Variance Example 2


Requirement 2
                                                                   Static/Flexible
                                                                   Budget Lump
Fixed Manufacturing Overhead   Actual Costs                             Sum                            Allocated
                                    $440.000                              $450.000                      $375.000

                                                $10.000 F                            $75.000 U
                                               Spending variance                     Production volume variance




     EMBA 5403 Fall 2008                              Mugan                                                        82/?
   Variance Example 2
Requirement 3
                                   Actual      Flexible Budget   Static Budget
Production and Sales in Units        110.000           110.000         150.000
Machine hours                         30.000            33.000          45.000
Fixed manuf. Overhead (FMOH)        $440.000          $450.000       $450.000
Variable manuf. Overhead (VMOH)     $960.000          $990.000     $1.350.000
VMOH per machine hour                 $32,00            $30,00          $30,00
FMOH allocated per machine hour                                         $10,00




    EMBA 5403 Fall 2008           Mugan                               83/?
      Variance Example 2

                                                      Actual Input
Variable                                                 Qty. ×
Manufacturing                                          Budgeted                            Flexible
Overhead           Actual Costs                          Rate                              Budget


                       $960.000                           $900.000                         $990.000




                                    $60.000   U                             $90.000    F
                                  Spending variance                  Efficiency variance




      EMBA 5403 Fall 2008                         Mugan                                    84/?
        Variance Example 2

                                                          Static/Flexible
Fixed Manufacturing                                       Budget Lump
Overhead              Actual Costs                             Sum                                       Allocated
                           $440.000                              $450.000                                 $300.000

                                        $10.000 F                                       $150.000 U
                                      Spending variance                     Production volume variance




        EMBA 5403 Fall 2008                           Mugan                                              85/?
THE END




EMBA 5403 Fall 2008   Mugan   86/?

				
DOCUMENT INFO
Description: Calculate Budget Variance in Excel document sample