Operating Budget Example (Excel)

Document Sample
Operating Budget Example (Excel) Powered By Docstoc
					                WHITE CELLS ARE ADJUSTABLE
                                                                                                                                                                        © Kristoffer Burnett - Certified Management Accountant
                                                                                                                                                                              Small business solutions at http://www.imperoco
                Operating Budget Example

  Pro Forma Income Statement

                         Tax rate (%):           35.0%

Net sales                                $ 15,946,821 From the Sales Budget
Cost of goods sold                        (11,267,373) From the Cost Of Goods Sold Budget
Gross profit                                4,679,448 Net sales - Cost of goods sold

Operating expenses                           (2,827,586) From the SG&A Budget
Operating profit (EBIT)                       1,851,862 Gross profit - Operating expenses

Interest income                                    951 From interest bearing investments
Interest expense                               (44,083) From short and long-term borrowings used to finance operations
Tax                                           (633,056) Based on the percentage entered above

Net profit                               $   1,175,675 Amount available after all expenses have been paid


                Ratios

Profit margin                                      7.4% Net profit / Net sales

Gross margin                                     29.3% Gross profit / Net sales

Times interest earned                            42.01 Operating profit / Interest expense. This ratio gauges the organization's ability to meet its debt obligations

Degree of financial leverage                       1.02       1


Degree of operating leverage                       2.47       2




 $1,600,000
                                                                    Components of Operating Income
 $1,400,000


 $1,200,000


 $1,000,000
                                                                                                                                                                        DM
                                                                                                                                                                        DL
   $800,000
                                                                                                                                                                        OH
                                                                                                                                                                        SGA
   $600,000
                                                                                                                                                                        SALES

   $400,000


   $200,000


             $0
          WHITE CELLS ARE ADJUSTABLE
                                                                                                                                                                                      © Kristoffer Burnett - Certified Management Accountant, 2009-2011
                                                                                                                                                                                            Small business solutions at http://www.imperoco.com
           Operating Budget Example


     Unit Sales Budget

      Percentage of sales
assumed to be uncollectible        7.5%

                                   2       Jan-2011      Feb-2011      Mar-2011         Apr-2011   May-2011        Jun-2011         Jul-2011   Aug-2011        Sep-2011         Oct-2011    Nov-2011       Dec-2011      Jan-2012     Feb-2012    Two extra periods included as a reference for the "Production/Direct Materials Budget"
             Item                 Total
            Item1                14,790         1,205        1,210         1,215           1,220          1,225        1,230           1,235          1,240        1,245           1,250          1,255        1,260         1,265        1,270
            Item2                12,853         1,057        1,059         1,062           1,064          1,067        1,070           1,072          1,075        1,078           1,080          1,083        1,086         1,089        1,092
            Item3                24,311         2,104        2,090         2,075           2,061          2,047        2,032           2,018          2,004        1,991           1,977          1,963        1,949         1,936        1,923
            Item4                12,395         1,010        1,014         1,018           1,022          1,027        1,031           1,035          1,039        1,043           1,048          1,052        1,056         1,060        1,064
            Item5                31,021         2,329        2,373         2,417           2,463          2,509        2,556           2,604          2,653        2,702           2,753          2,805        2,857         2,911        2,965


     Dollar Sales Budget
               1                                         Jan-2011      Feb-2011      Mar-2011          Apr-2011   May-2011       Jun-2011          Jul-2011   Aug-2011       Sep-2011          Oct-2011    Nov-2011     Dec-2011
         Sales Price              Item       Total
 $                    168.00     Item1    $ 2,484,720   $   202,440   $   203,280   $   204,120    $   204,960    $   205,800   $   206,640    $   207,480    $   208,320   $   209,160    $   210,000    $   210,840   $   211,680
                      289.00     Item2      3,714,517       305,473       306,051       306,918        307,496        308,363       309,230        309,808        310,675       311,542        312,120        312,987       313,854
                      170.00     Item3      4,132,870       357,680       355,300       352,750        350,370        347,990       345,440        343,060        340,680       338,470        336,090        333,710       331,330
                      282.00     Item4      3,495,390       284,820       285,948       287,076        288,204        289,614       290,742        291,870        292,998       294,126        295,536        296,664       297,792
 $                    110.00     Item5      3,412,310       256,190       261,030       265,870        270,930        275,990       281,160        286,440        291,830       297,220        302,830        308,550       314,270
                 Gross sales               17,239,807     1,406,603     1,411,609     1,416,734      1,421,960      1,427,757     1,433,212      1,438,658      1,444,503     1,450,518      1,456,576      1,462,751     1,468,926
         Uncollectible portion              1,292,986       105,495       105,871       106,255        106,647        107,082       107,491        107,899        108,338       108,789        109,243        109,706       110,169 Based on the percentage entered above
                    Net sales             $15,946,821   $ 1,301,108   $ 1,305,738   $ 1,310,479    $ 1,315,313    $ 1,320,675   $ 1,325,721    $ 1,330,759    $ 1,336,165   $ 1,341,729    $ 1,347,333    $ 1,353,045   $ 1,358,757




Notes:

1 In this example sales prices are expected to remain level throughout the budget period. If they are expected to change, a weighted-average can be used.
2 Totals only include the first 12 periods.




      D:\Docstoc\Working\pdf\22d1dcf7-1048-473d-8b1e-2f24c5b08a82.xlsx                                                                                             Page 2 of 10                                                                                                                                          5/24/20118:59 PM
    WHITE CELLS ARE ADJUSTABLE
                                                                                                                                                                         © Kristoffer Burnett - Certified Management Accountant, 2009-2011
                                                                                                                                                                               Small business solutions at http://www.imperoco.com
     Operating Budget Example


   Production Budget

 % of next month's sales in
          ending inventory:    35%       1
                                         2      Jan-2011 Feb-2011 Mar-2011 Apr-2011 May-2011 Jun-2011 Jul-2011 Aug-2011 Sep-2011 Oct-2011 Nov-2011 Dec-2011 Jan-2012 Extra period included as a reference for the Direct Materials Budget
                               Item    Total
                              Item1
       Budgeted unit sales                         1,205       1,210       1,215       1,220        1,225    1,230    1,235    1,240      1,245    1,250    1,255    1,260      1,265 From the Sales Budget
     Plus ending inventory                           424         425         427         429          431      432      434      436        438      439      441      443        445 Based on the percentage entered above
 (Less) beginning inventory                         (422)       (424)       (425)       (427)        (429)    (431)    (432)    (434)      (436)    (438)    (439)    (441)      (443) Same as previous months ending inventory
      Units to be produced            14,811       1,207       1,211       1,217       1,222        1,227    1,231    1,237    1,242      1,247    1,251    1,257    1,262      1,267
                              Item2
       Budgeted unit sales                         1,057       1,059       1,062       1,064        1,067    1,070    1,072    1,075      1,078    1,080    1,083    1,086      1,089
     Plus ending inventory                           371         372         372         373          375      375      376      377        378      379      380      381        382
 (Less) beginning inventory                         (370)       (371)       (372)       (372)        (373)    (375)    (375)    (376)      (377)    (378)    (379)    (380)      (381)
      Units to be produced            12,864       1,058       1,060       1,062       1,065        1,069    1,070    1,073    1,076      1,079    1,081    1,084    1,087      1,090
                              Item3
       Budgeted unit sales                         2,104       2,090       2,075       2,061        2,047    2,032    2,018    2,004      1,991    1,977    1,963    1,949      1,936
     Plus ending inventory                           732         726         721         716          711      706      701      697        692      687      682      678        673
 (Less) beginning inventory                         (736)       (732)       (726)       (721)        (716)    (711)    (706)    (701)      (697)    (692)    (687)    (682)      (678)
      Units to be produced            24,253       2,100       2,084       2,070       2,056        2,042    2,027    2,013    2,000      1,986    1,972    1,958    1,945      1,931
                              Item4
       Budgeted unit sales                         1,010       1,014       1,018       1,022        1,027    1,031    1,035    1,039      1,043    1,048    1,052    1,056      1,060
     Plus ending inventory                           355         356         358         359          361      362      364      365        367      368      370      371        372
 (Less) beginning inventory                         (354)       (355)       (356)       (358)        (359)    (361)    (362)    (364)      (365)    (367)    (368)    (370)      (371)
      Units to be produced            12,412       1,011       1,015       1,020       1,023        1,029    1,032    1,037    1,040      1,045    1,049    1,054    1,057      1,061
                              Item5
       Budgeted unit sales                         2,329       2,373       2,417       2,463        2,509    2,556    2,604    2,653      2,702    2,753    2,805     2,857     2,911
     Plus ending inventory                           831         846         862         878          895      911      929      946        964      982    1,000     1,019     1,038
 (Less) beginning inventory                         (815)       (831)       (846)       (862)        (878)    (895)    (911)    (929)      (946)    (964)    (982)   (1,000)   (1,019)
      Units to be produced            31,225       2,345       2,388       2,433       2,479        2,526    2,572    2,622    2,670      2,720    2,771    2,823     2,876     2,930



Notes:

1 The percentage of the following month's sales, in units, the organization wants to have in inventory.
2 Totals only include the first 12 periods.




     D:\Docstoc\Working\pdf\22d1dcf7-1048-473d-8b1e-2f24c5b08a82.xlsx                                                          Page 3 of 10                                                                                             5/24/20118:59 PM
         WHITE CELLS ARE ADJUSTABLE
                                                                                                                                                     © Kristoffer Burnett - Certified Management Accountant, 2009-2011
                                                                                                                                                           Small business solutions at http://www.imperoco.com
         Operating Budget Example


     Bill(s) of Materials           1


                                 Direct                Direct Material       Extended            Direct              Direct Material      Extended               Direct              Direct Material      Extended
             Item               Material Quantity         Cost/Unit            Cost      Item   Material Quantity       Cost/Unit           Cost      Item      Material Quantity       Cost/Unit           Cost
            Item1                 DM9       3         $              5.18    $ 15.54    Item2     DM4       8       $              8.99   $ 71.92    Item3        DM7       3       $              1.28   $ 3.84
                                 DM10       6                        1.67       10.02             DM9       1                      5.18       5.18                DM5       9                      4.75      42.75
                                                      Total material cost    $ 25.56              DM5       3                      4.75      14.25                                  Total material cost   $ 46.59
                                                                                                  DM3       6                      6.74      40.44
                                                                                                                    Total material cost   $ 131.79

                                 Direct                Direct Material       Extended            Direct              Direct Material      Extended
             Item               Material Quantity         Cost/Unit            Cost      Item   Material Quantity       Cost/Unit           Cost
            Item4                 DM5       3         $              4.75    $ 14.25    Item5     DM8       2       $              7.44   $ 14.88
                                  DM1      10                        2.16       21.60            DM10       9                      1.67      15.03
                                  DM2       6                        6.74       40.44                               Total material cost   $ 29.91
                                  DM8       1                        7.44        7.44
                                                      Total material cost    $ 83.73



Notes:

1 This information will be used in the composition of the Direct Materials Budget.
             WHITE CELLS ARE ADJUSTABLE
                                                                                                                                                                           © Kristoffer Burnett - Certified Management Accountant, 2009-2011
                                                                                                                                                                                 Small business solutions at http://www.imperoco.com
              Operating Budget Example


   Direct Materials Budget Detail          1


         % of next month's production
                  in ending inventory:    25%          2
                                                                               Jan-2011    Feb-2011    Mar-2011   Apr-2011    May-2011    Jun-2011       Jul-2011   Aug-2011 Sep-2011     Oct-2011   Nov-2011    Dec-2011
                                          Item        DM           Total
                                         Item1
DM units needed for production                       DM9                          3,621    3,633    3,651    3,666    3,681    3,693    3,711    3,726    3,741    3,753    3,771                                    3,786    Units to be produced from the Production Budget * the quantity in the bill of material
Plus ending inventory                                                               908      913      917      920      923      928      932      935      938      943      947                                      950    Based on the percentage entered above
(Less) beginning inventory                                                         (905)    (908)    (913)    (917)    (920)    (923)    (928)    (932)    (935)    (938)    (943)                                    (947)   Same as previous months ending inventory
Units to be purchased                                                44,478       3,624    3,638    3,655    3,669    3,684    3,698    3,715    3,729    3,744    3,758    3,775                                    3,789    A negligible amount of lead/delivery time is assumed. However, this must be taken into consideration to avoid shortages
DM cost/unit                                                           5.18        5.18     5.18     5.18     5.18     5.18     5.18     5.18     5.18     5.18     5.18     5.18                                     5.18    From the bill of material
Total                                                DM9       $    230,396    $ 18,772 $ 18,845 $ 18,933 $ 19,005 $ 19,083 $ 19,156 $ 19,244 $ 19,316 $ 19,394 $ 19,466 $ 19,555 $                                 19,627    Total cost of direct materials for this finished good

DM units needed for production                       DM10                         7,242    7,266    7,302    7,332    7,362    7,386    7,422    7,452    7,482    7,506    7,542                                    7,572
Plus ending inventory                                                             1,817    1,826    1,833    1,841    1,847    1,856    1,863    1,871    1,877    1,886    1,893                                    1,901
(Less) beginning inventory                                                       (1,811)  (1,817)  (1,826)  (1,833)  (1,841)  (1,847)  (1,856)  (1,863)  (1,871)  (1,877)  (1,886)                                  (1,893)
Units to be purchased                                                88,956       7,248    7,275    7,309    7,340    7,368    7,395    7,429    7,460    7,488    7,515    7,549                                    7,580
DM cost/unit                                                           1.67        1.67     1.67     1.67     1.67     1.67     1.67     1.67     1.67     1.67     1.67     1.67                                     1.67
Total                                                          $    148,557    $ 12,104 $ 12,149 $ 12,206 $ 12,258 $ 12,305 $ 12,350 $ 12,406 $ 12,458 $ 12,505 $ 12,550 $ 12,607 $                                 12,659

Total DM cost for Item1                                        $    378,953    $ 30,876    $ 30,994   $ 31,139    $ 31,263    $ 31,388    $ 31,505       $ 31,650   $ 31,774   $ 31,899   $ 32,016   $ 32,161   $   32,286

                                         Item2
DM units needed for production                       DM4                          8,464    8,480    8,496    8,520    8,552    8,560    8,584    8,608    8,632    8,648    8,672                                    8,696
Plus ending inventory                                                             2,120    2,124    2,130    2,138    2,140    2,146    2,152    2,158    2,162    2,168    2,174                                    2,180
(Less) beginning inventory                                                       (2,116)  (2,120)  (2,124)  (2,130)  (2,138)  (2,140)  (2,146)  (2,152)  (2,158)  (2,162)  (2,168)                                  (2,174)
Units to be purchased                                               102,976       8,468    8,484    8,502    8,528    8,554    8,566    8,590    8,614    8,636    8,654    8,678                                    8,702
DM cost/unit                                                           8.99        8.99     8.99     8.99     8.99     8.99     8.99     8.99     8.99     8.99     8.99     8.99                                     8.99
Total                                                          $    925,754    $ 76,127 $ 76,271 $ 76,433 $ 76,667 $ 76,900 $ 77,008 $ 77,224 $ 77,440 $ 77,638 $ 77,799 $ 78,015 $                                 78,231

DM units needed for production                       DM9                           1,058      1,060       1,062       1,065       1,069       1,070         1,073      1,076      1,079      1,081      1,084        1,087
Plus ending inventory                                                                265        266         266         267         268         268           269        270        270        271        272          273
(Less) beginning inventory                                                          (265)      (265)       (266)       (266)       (267)       (268)         (268)      (269)      (270)      (270)      (271)        (272)
Units to be purchased                                                12,872        1,058      1,061       1,062       1,066       1,070       1,070         1,074      1,077      1,079      1,082      1,085        1,088
DM cost/unit                                                           5.18         5.18       5.18        5.18        5.18        5.18        5.18          5.18       5.18       5.18       5.18       5.18         5.18
Total                                                          $     66,677    $   5,480 $    5,496 $     5,501 $     5,522 $     5,543 $     5,543 $       5,563 $    5,579 $    5,589 $    5,605 $    5,620 $      5,636

DM units needed for production                       DM5                          3,174    3,180    3,186    3,195    3,207    3,210    3,219    3,228    3,237    3,243    3,252                                    3,261
Plus ending inventory                                                               795      797      799      802      803      805      807      809      811      813      815                                      -
(Less) beginning inventory                                                         (794)    (795)    (797)    (799)    (802)    (803)    (805)    (807)    (809)    (811)    (813)                                    (815)
Units to be purchased                                                37,798       3,175    3,182    3,188    3,198    3,208    3,212    3,221    3,230    3,239    3,245    3,254                                    2,446
DM cost/unit                                                           4.75        4.75     4.75     4.75     4.75     4.75     4.75     4.75     4.75     4.75     4.75     4.75                                     4.75
Total                                                          $    179,541    $ 15,081 $ 15,115 $ 15,143 $ 15,191 $ 15,238 $ 15,257 $ 15,300 $ 15,343 $ 15,385 $ 15,414 $ 15,457 $                                 11,619

DM units needed for production                       DM3                          6,348    6,360    6,372    6,390    6,414    6,420    6,438    6,456    6,474    6,486    6,504                                    6,522
Plus ending inventory                                                             1,590    1,593    1,598    1,604    1,605    1,610    1,614    1,619    1,622    1,626    1,631                                      -
(Less) beginning inventory                                                       (1,587)  (1,590)  (1,593)  (1,598)  (1,604)  (1,605)  (1,610)  (1,614)  (1,619)  (1,622)  (1,626)                                  (1,631)
Units to be purchased                                                75,597       6,351    6,363    6,377    6,396    6,415    6,425    6,442    6,461    6,477    6,490    6,509                                    4,891
DM cost/unit                                                           6.74        6.74     6.74     6.74     6.74     6.74     6.74     6.74     6.74     6.74     6.74     6.74                                     6.74
Total                                                          $    509,524    $ 42,806 $ 42,887 $ 42,981 $ 43,109 $ 43,237 $ 43,305 $ 43,419 $ 43,547 $ 43,655 $ 43,743 $ 43,871 $                                 32,965

Total DM cost for Item2                                        $   1,681,495   $139,495    $139,768   $140,058    $140,488    $140,918    $141,112       $141,506   $141,908   $142,267   $142,561   $142,963   $ 128,451

                                         Item3
DM units needed for production                       DM7                          6,300   6,252   6,210   6,168   6,126   6,081   6,039   6,000   5,958   5,916   5,874                                              5,835
Plus ending inventory                                                             1,563   1,553   1,542   1,532   1,520   1,510   1,500   1,490   1,479   1,469   1,459                                              1,448
(Less) beginning inventory                                                       (1,575) (1,563) (1,553) (1,542) (1,532) (1,520) (1,510) (1,500) (1,490) (1,479) (1,469)                                            (1,459)
Units to be purchased                                                72,632       6,288   6,242   6,199   6,158   6,114   6,071   6,029   5,990   5,947   5,906   5,864                                              5,824
DM cost/unit                                                           1.28        1.28    1.28    1.28    1.29    1.29    1.29    1.30    1.30    1.30    1.31    1.31                                               1.31
Total                                                          $     94,196    $ 8,049 $ 7,990 $ 7,935 $ 7,961 $ 7,904 $ 7,849 $ 7,855 $ 7,804 $ 7,748 $ 7,755 $ 7,699 $                                             7,647

DM units needed for production                       DM5                         18,900      18,756      18,630      18,504      18,378     18,243         18,117     18,000     17,874     17,748     17,622       17,505
Plus ending inventory                                                             4,689       4,658       4,626       4,595       4,561      4,529          4,500      4,469      4,437      4,406      4,376        4,345
(Less) beginning inventory                                                       (4,725)     (4,689)     (4,658)     (4,626)     (4,595)    (4,561)        (4,529)    (4,500)    (4,469)    (4,437)    (4,406)      (4,376)
Units to be purchased                                                217,897     18,864      18,725      18,598      18,473      18,344     18,211         18,088     17,969     17,842     17,717     17,592       17,474
DM cost/unit                                                            4.75       4.75        4.75        4.75        4.75        4.75       4.75           4.75       4.75       4.75       4.75       4.75         4.75
Total                                                          $   1,035,011   $ 89,604 $    88,944 $    88,341 $    87,747 $    87,134 $   86,502 $       85,918 $   85,353 $   84,750 $   84,156 $   83,562 $     83,002

Total DM cost for Item3                                        $   1,129,207   $ 97,653    $ 96,934   $ 96,275    $ 95,708    $ 95,038    $ 94,351       $ 93,773   $ 93,157   $ 92,498   $ 91,910   $ 91,261   $   90,648

                                         Item4
DM units needed for production                       DM5                          3,033    3,045    3,060    3,069    3,087    3,096    3,111    3,120    3,135    3,147    3,162                                    3,171
Plus ending inventory                                                               761      765      767      772      774      778      780      784      787      791      793                                      796
(Less) beginning inventory                                                         (758)    (761)    (765)    (767)    (772)    (774)    (778)    (780)    (784)    (787)    (791)                                    (793)
Units to be purchased                                                37,274       3,036    3,049    3,062    3,074    3,089    3,100    3,113    3,124    3,138    3,151    3,164                                    3,174
DM cost/unit                                                           4.75        4.75     4.75     4.75     4.75     4.75     4.75     4.75     4.75     4.75     4.75     4.75                                     4.75
Total                                                          $    177,052    $ 14,421 $ 14,483 $ 14,545 $ 14,602 $ 14,673 $ 14,725 $ 14,787 $ 14,839 $ 14,906 $ 14,967 $ 15,029 $                                 15,077

DM units needed for production                       DM1                         10,110      10,150      10,200      10,230      10,290     10,320         10,370     10,400     10,450     10,490     10,540       10,570
Plus ending inventory                                                             2,538       2,550       2,558       2,573       2,580      2,593          2,600      2,613      2,623      2,635      2,643        2,653
(Less) beginning inventory                                                       (2,528)     (2,538)     (2,550)     (2,558)     (2,573)    (2,580)        (2,593)    (2,600)    (2,613)    (2,623)    (2,635)      (2,643)
Units to be purchased                                               124,245      10,120      10,162      10,208      10,245      10,297     10,333         10,377     10,413     10,460     10,502     10,548       10,580
DM cost/unit                                                           2.16        2.16        2.16        2.16        2.16        2.16       2.16           2.16       2.16       2.16       2.16       2.16         2.16
Total                                                          $    268,369    $ 21,859 $    21,950 $    22,049 $    22,129 $    22,242 $   22,319 $       22,414 $   22,492 $   22,594 $   22,684 $   22,784 $     22,853

DM units needed for production                       DM2                          6,066    6,090    6,120    6,138    6,174    6,192    6,222    6,240    6,270    6,294    6,324                                    6,342
Plus ending inventory                                                             1,523    1,530    1,535    1,544    1,548    1,556    1,560    1,568    1,574    1,581    1,586                                    1,592
(Less) beginning inventory                                                       (1,517)  (1,523)  (1,530)  (1,535)  (1,544)  (1,548)  (1,556)  (1,560)  (1,568)  (1,574)  (1,581)                                  (1,586)
Units to be purchased                                                74,547       6,072    6,097    6,125    6,147    6,178    6,200    6,226    6,248    6,276    6,301    6,329                                    6,348
DM cost/unit                                                           6.74        6.74     6.74     6.74     6.74     6.74     6.74     6.74     6.74     6.74     6.74     6.74                                     6.74
Total                                                          $    502,447    $ 40,925 $ 41,094 $ 41,283 $ 41,431 $ 41,640 $ 41,788 $ 41,963 $ 42,112 $ 42,300 $ 42,469 $ 42,657 $                                 42,786

DM units needed for production                       DM8                           1,011      1,015       1,020       1,023       1,029       1,032         1,037      1,040      1,045      1,049      1,054        1,057
Plus ending inventory                                                                254        255         256         257         258         259           260        261        262        264        264          265
(Less) beginning inventory                                                          (253)      (254)       (255)       (256)       (257)       (258)         (259)      (260)      (261)      (262)      (264)        (264)
Units to be purchased                                                12,424        1,012      1,016       1,021       1,024       1,030       1,033         1,038      1,041      1,046      1,051      1,054        1,058
DM cost/unit                                                           7.44         7.44       7.44        7.44        7.44        7.44        7.44          7.44       7.44       7.44       7.44       7.44         7.44
Total                                                          $     92,435    $   7,529 $    7,559 $     7,596 $     7,619 $     7,663 $     7,686 $       7,723 $    7,745 $    7,782 $    7,819 $    7,842 $      7,872

Total DM cost for Item4                                        $   1,040,302   $ 84,735    $ 85,085   $ 85,473    $ 85,780    $ 86,217    $ 86,518       $ 86,887   $ 87,188   $ 87,582   $ 87,940   $ 88,312   $   88,586

                                         Item5
DM units needed for production                       DM8                          4,690    4,776    4,866    4,958    5,052    5,144    5,244    5,340    5,440    5,542    5,646                                    5,752
Plus ending inventory                                                             1,194    1,217    1,240    1,263    1,286    1,311    1,335    1,360    1,386    1,412    1,438                                    1,465
(Less) beginning inventory                                                       (1,173)  (1,194)  (1,217)  (1,240)  (1,263)  (1,286)  (1,311)  (1,335)  (1,360)  (1,386)  (1,412)                                  (1,438)
Units to be purchased                                                62,742       4,711    4,799    4,889    4,981    5,075    5,169    5,268    5,365    5,466    5,568    5,672                                    5,779
DM cost/unit                                                           7.44        7.44     7.44     7.44     7.44     7.44     7.44     7.44     7.44     7.44     7.44     7.44                                     7.44
Total                                                          $    466,800    $ 35,050 $ 35,705 $ 36,374 $ 37,059 $ 37,758 $ 38,457 $ 39,194 $ 39,916 $ 40,667 $ 41,426 $ 42,200 $                                 42,996

DM units needed for production                       DM10                        21,105      21,492      21,897      22,311      22,734     23,148         23,598     24,030     24,480     24,939     25,407       25,884
Plus ending inventory                                                             5,373       5,474       5,578       5,684       5,787      5,900          6,008      6,120      6,235      6,352      6,471        6,593
(Less) beginning inventory                                                       (5,276)     (5,373)     (5,474)     (5,578)     (5,684)    (5,787)        (5,900)    (6,008)    (6,120)    (6,235)    (6,352)      (6,471)
Units to be purchased                                               282,342      21,202      21,593      22,001      22,417      22,837     23,261         23,706     24,142     24,595     25,056     25,526       26,006
DM cost/unit                                                           1.67        1.67        1.67        1.67        1.67        1.67       1.67           1.67       1.67       1.67       1.67       1.67         1.67
Total                                                          $    471,511    $ 35,407 $    36,060 $    36,742 $    37,436 $    38,138 $   38,846 $       39,589 $   40,317 $   41,074 $   41,844 $   42,628 $     43,430

Total DM cost for Item5                                        $    938,312    $ 70,457    $ 71,765   $ 73,116    $ 74,495    $ 75,896    $ 77,303       $ 78,783   $ 80,233   $ 81,741   $ 83,269   $ 84,828   $   86,426


Direct Materials Budget Summary

                                                 Sum of Amount Month-Year                                                                                                                  Right click inside report and select "Refresh" to update if changes have been made above.
                                                 DM                Jan-2010 Feb-2010 Mar-2010 Apr-2010 May-2010 Jun-2010 Jul-2010 Aug-2010 Sep-2010 Oct-2010 Nov-2010 Dec-2010 Grand Total
                                                          DM1 $      21,859 $ 21,950 $ 22,049 $ 22,129 $ 22,242 $ 22,319 $ 22,414 $ 22,492 $ 22,594 $ 22,684 $ 22,784 $ 22,853 $ 268,369
                                                         DM10        47,512   48,210   48,948   49,694   50,442   51,196   51,995   52,775   53,579   54,394   55,235   56,089     620,068
                                                          DM2        40,925   41,094   41,283   41,431   41,640   41,788   41,963   42,112   42,300   42,469   42,657   42,786     502,447
                                                          DM3        42,806   42,887   42,981   43,109   43,237   43,305   43,419   43,547   43,655   43,743   43,871   32,965     509,524
                                                          DM4        76,127   76,271   76,433   76,667   76,900   77,008   77,224   77,440   77,638   77,799   78,015   78,231     925,754
                                                          DM5       119,106  118,541  118,028  117,539  117,045  116,484  116,005  115,534  115,040  114,537  114,048  109,697   1,391,603
                                                          DM7         8,049    7,990    7,935    7,961    7,904    7,849    7,855    7,804    7,748    7,755    7,699    7,647      94,196
                                                          DM8        42,579   43,264   43,970   44,677   45,421   46,143   46,917   47,661   48,449   49,245   50,041   50,867     559,235
                                                          DM9        24,253   24,341   24,434   24,527   24,626   24,698   24,807   24,895   24,983   25,071   25,175   25,263     297,073
                                                   Grand Total $    423,216 $424,546 $426,061 $427,734 $429,457 $430,790 $432,600 $434,260 $435,986 $437,697 $439,525 $426,397 $ 5,168,268




Notes:

1 In this example DM prices are expected to remain level throughout the budget period. If they are expected to change, a weighted-average can be used.
2 The percentage of the following month's production needs, in units, the organization wants to have in inventory.




         D:\Docstoc\Working\pdf\22d1dcf7-1048-473d-8b1e-2f24c5b08a82.xlsx                                                                                                  Page 5 of 10                                                                                                                                                  5/24/20118:59 PM
          WHITE CELLS ARE ADJUSTABLE
                                                                                                                                                                                        © Kristoffer Burnett - Certified Management Accountant, 2009-2011
                                                                                                                                                                                              Small business solutions at http://www.imperoco.com
           Operating Budget Example


   Direct Labor Hours Per Unit
                                                  1         Direct Labor Direct Labor                                              Direct Labor Direct Labor                                               Direct Labor Direct Labor
                                      Item      Hours         Rate/Hour    Cost/Unit                         Item        Hours       Rate/Hour    Cost/Unit                          Item        Hours       Rate/Hour    Cost/Unit
                                     Item1          2.9     $      13.93 $      40.40                       Item2            1.4   $      16.36 $      22.90                        Item3            2.9   $      12.63 $      36.63

                                                            Direct Labor Direct Labor                                              Direct Labor Direct Labor
                                      Item      Hours         Rate/Hour    Cost/Unit                         Item        Hours       Rate/Hour    Cost/Unit
                                     Item4          3.0     $      16.13 $      48.39                       Item5            2.1   $      13.24 $      27.80


   Direct Labor Budget Detail
                                                                Jan-2011        Feb-2011    Mar-2011    Apr-2011     May-2011          Jun-2011        Jul-2011     Aug-2011    Sep-2011     Oct-2011          Nov-2011        Dec-2011
                                      Item       Total
                                     Item1
DL hours needed for production                                      3,500           3,512      3,529       3,544        3,558              3,570           3,587       3,602       3,616        3,628              3,645           3,660   Hours * Units to be produced from the Production Budget
DL cost/hr                                                          13.93           13.93      13.93       13.93        13.93              13.93           13.93       13.93       13.93        13.93              13.93           13.93
Total DL cost for Item1                       $ 598,320     $      48,759   $      48,921   $ 49,163    $ 49,365     $ 49,567      $      49,729   $      49,971    $ 50,173    $ 50,375     $ 50,537      $      50,779   $      50,981

                                     Item2
DL hours needed for production                                      1,481           1,484      1,487       1,491        1,497              1,498           1,502       1,506       1,511        1,513              1,518           1,522
DL cost/hr                                                          16.36           16.36      16.36       16.36        16.36              16.36           16.36       16.36       16.36        16.36              16.36           16.36
Total DL cost for Item2                       $ 294,637     $      24,232   $      24,278   $ 24,324    $ 24,393     $ 24,484      $      24,507   $      24,576    $ 24,645    $ 24,713     $ 24,759      $      24,828   $      24,897

                                     Item3
DL hours needed for production                                      6,090           6,044      6,003       5,962        5,922              5,878           5,838       5,800       5,759        5,719              5,678           5,641
DL cost/hr                                                          12.63           12.63      12.63       12.63        12.63              12.63           12.63       12.63       12.63        12.63              12.63           12.63
Total DL cost for Item3                       $ 888,315     $      76,917   $      76,331   $ 75,818    $ 75,305     $ 74,792      $      74,243   $      73,730    $ 73,254    $ 72,741     $ 72,228      $      71,716   $      71,240

                                     Item4
DL hours needed for production                                      3,033           3,045      3,060       3,069        3,087              3,096           3,111       3,120       3,135        3,147              3,162           3,171
DL cost/hr                                                          16.13           16.13      16.13       16.13        16.13              16.13           16.13       16.13       16.13        16.13              16.13           16.13
Total DL cost for Item4                       $ 600,617     $      48,922   $      49,116   $ 49,358    $ 49,503     $ 49,793      $      49,938   $      50,180    $ 50,326    $ 50,568     $ 50,761      $      51,003   $      51,148

                                     Item5
DL hours needed for production                                      4,925           5,015      5,109       5,206        5,305              5,401           5,506       5,607       5,712        5,819              5,928           6,040
DL cost/hr                                                          13.24           13.24      13.24       13.24        13.24              13.24           13.24       13.24       13.24        13.24              13.24           13.24
Total DL cost for Item5                       $ 868,180     $      65,200   $      66,396   $ 67,647    $ 68,926     $ 70,233      $      71,512   $      72,902    $ 74,237    $ 75,627     $ 77,045      $      78,491   $      79,964

Total DL Hours                                   234,104           19,029          19,099      19,188      19,272       19,368            19,443          19,544       19,635      19,733       19,826            19,931          20,033
Grand total                                  $ 3,250,068    $     264,031   $     265,041   $ 266,310   $ 267,492    $ 268,870     $     269,929   $     271,360    $ 272,634   $ 274,024    $ 275,330     $     276,816   $     278,230

Average DL rate/hr                                          $       13.88   $       13.88   $   13.88   $    13.88   $     13.88   $       13.88   $        13.88   $   13.88   $    13.89   $     13.89   $       13.89   $       13.89 Grand Total / Total DL Hours



Notes:

1 The average expected hours of direct labor needed to manufacture one unit.
2 DL rates are expected to remain level throughout the budget period. If they are expected to change, a weighted-average can be used.
3 DL costs should include regular wages and all benefits.




      D:\Docstoc\Working\pdf\22d1dcf7-1048-473d-8b1e-2f24c5b08a82.xlsx                                                                       Page 6 of 10                                                                                                                      5/24/20118:59 PM
                   WHITE CELLS ARE ADJUSTABLE
                                                                                                                                                                                      © Kristoffer Burnett - Certified Management Accountant, 2009-2011
                                                                                                                                                                                            Small business solutions at http://www.imperoco.com
                    Operating Budget Example

                                                     1
Total budgeted variable overhead:              $ 1,726,238
Total expected direct labor hours:                 234,104 Total DL Hours from the Direct Labor Budget
VOH rate /DL hour:                             $      7.37 Total budgeted variable overhead / Total expected direct labor hours

Total budgeted fixed overhead:                 $ 1,131,792
Fixed overhead amount per item per month:      $    18,863 Total budgeted fixed overhead / (# items * # months), default = 60 (5 * 12)


           Overhead Budget Detail                    2
                                                                              Jan-2011     Feb-2011     Mar-2011      Apr-2011    May-2011    Jun-2011    Jul-2011    Aug-2011    Sep-2011    Oct-2011    Nov-2011    Dec-2011
                                                   Item           Total
                                                  Item1
DL hours needed for production                                     42,952         3,500        3,512       3,529        3,544     3,558          3,570       3,587       3,602       3,616       3,628       3,645       3,660    DL hours needed for production from the Direct Labor Budget
VOH rate/DL hour:                                                                  7.37         7.37        7.37         7.37      7.37           7.37        7.37        7.37        7.37        7.37        7.37        7.37    From above
Allocated VOH                                                                    25,797       25,883      26,011       26,118    26,225         26,310      26,438      26,545      26,652      26,738      26,866      26,973    "DL hours needed for production" * "VOH rate/DL hour"
Allocated FOH                                                                    18,863       18,863      18,863       18,863    18,863         18,863      18,863      18,863      18,863      18,863      18,863      18,863    From above
Total allocated OH for Item1                                  $ 542,914      $ 44,660 $ 44,746 $ 44,874 $ 44,981 $ 45,088                     $ 45,173    $ 45,302    $ 45,408    $ 45,515    $ 45,601    $ 45,729    $ 45,836
Allocated OH/DL hour                                          $   12.64      Total allocated OH / DL hours needed for production
DL hours/unit                                                        2.9     Hours from the Direct Labor Budget
Allocated OH/unit                                             $   36.66      Allocated OH/DL hour * DL hours/unit

                                                  Item2
DL hours needed for production                                     18,010       1,481        1,484        1,487          1,491       1,497       1,498       1,502       1,506       1,511       1,513       1,518       1,522
VOH rate/DL hour:                                                                7.37         7.37         7.37           7.37        7.37        7.37        7.37        7.37        7.37        7.37        7.37        7.37
Allocated VOH                                                                  10,916       10,937       10,958         10,989      11,030      11,040      11,071      11,102      11,133      11,154      11,185      11,216
Allocated FOH                                                                  18,863       18,863       18,863         18,863      18,863      18,863      18,863      18,863      18,863      18,863      18,863      18,863
Total allocated OH for Item2                                  $ 359,089      $ 29,780     $ 29,800     $ 29,821       $ 29,852    $ 29,893    $ 29,903    $ 29,934    $ 29,965    $ 29,996    $ 30,017    $ 30,048    $ 30,079
Allocated OH/DL hour                                          $   19.94
DL hours/unit                                                        1.4
Allocated OH/unit                                             $   27.92

                                                  Item3
DL hours needed for production                                     70,334       6,090        6,044        6,003          5,962       5,922       5,878       5,838       5,800       5,759       5,719       5,678       5,641
VOH rate/DL hour:                                                                7.37         7.37         7.37           7.37        7.37        7.37        7.37        7.37        7.37        7.37        7.37        7.37
Allocated VOH                                                                  44,883       44,541       44,242         43,943      43,644      43,323      43,024      42,746      42,447      42,148      41,848      41,570
Allocated FOH                                                                  18,863       18,863       18,863         18,863      18,863      18,863      18,863      18,863      18,863      18,863      18,863      18,863
Total allocated OH for Item3                                  $ 744,718      $ 63,747     $ 63,405     $ 63,105       $ 62,806    $ 62,507    $ 62,186    $ 61,887    $ 61,609    $ 61,310    $ 61,011    $ 60,712    $ 60,434
Allocated OH/DL hour                                          $   10.59
DL hours/unit                                                        2.9
Allocated OH/unit                                             $   30.71

                                                  Item4
DL hours needed for production                                     37,236       3,033        3,045        3,060          3,069       3,087       3,096       3,111       3,120       3,135       3,147       3,162       3,171
VOH rate/DL hour:                                                                7.37         7.37         7.37           7.37        7.37        7.37        7.37        7.37        7.37        7.37        7.37        7.37
Allocated VOH                                                                  22,353       22,442       22,552         22,619      22,751      22,818      22,928      22,994      23,105      23,193      23,304      23,370
Allocated FOH                                                                  18,863       18,863       18,863         18,863      18,863      18,863      18,863      18,863      18,863      18,863      18,863      18,863
Total allocated OH for Item4                                  $ 500,788      $ 41,216     $ 41,305     $ 41,415       $ 41,482    $ 41,614    $ 41,681    $ 41,791    $ 41,858    $ 41,968    $ 42,057    $ 42,167    $ 42,233
Allocated OH/DL hour                                          $   13.45
DL hours/unit                                                        3.0
Allocated OH/unit                                             $   40.35

                                                  Item5
DL hours needed for production                                     65,573       4,925        5,015        5,109          5,206       5,305       5,401       5,506       5,607       5,712       5,819       5,928       6,040
VOH rate/DL hour:                                                                7.37         7.37         7.37           7.37        7.37        7.37        7.37        7.37        7.37        7.37        7.37        7.37
Allocated VOH                                                                  36,294       36,959       37,656         38,367      39,095      39,807      40,581      41,324      42,097      42,887      43,692      44,512
Allocated FOH                                                                  18,863       18,863       18,863         18,863      18,863      18,863      18,863      18,863      18,863      18,863      18,863      18,863
Total allocated OH for Item5                                  $ 709,628      $ 55,157     $ 55,822     $ 56,519       $ 57,231    $ 57,958    $ 58,670    $ 59,444    $ 60,187    $ 60,961    $ 61,750    $ 62,555    $ 63,375
Allocated OH/DL hour                                          $   10.82
DL hours/unit                                                        2.1
Allocated OH/unit                                             $   22.72


         Overhead Budget Summary
                                                                             Jan-2011     Feb-2011     Mar-2011       Apr-2011    May-2011    Jun-2011     Jul-2011   Aug-2011    Sep-2011    Oct-2011    Nov-2011    Dec-2011
Total allocated overhead by item                  Item1       $   542,914    $ 44,660     $ 44,746     $ 44,874       $ 44,981    $ 45,088    $ 45,173    $ 45,302    $ 45,408    $ 45,515    $ 45,601    $ 45,729    $ 45,836
                                                  Item2           359,089       29,780       29,800       29,821         29,852      29,893      29,903      29,934      29,965      29,996      30,017      30,048      30,079
                                                  Item3           744,718       63,747       63,405       63,105         62,806      62,507      62,186      61,887      61,609      61,310      61,011      60,712      60,434
                                                  Item4           500,788       41,216       41,305       41,415         41,482      41,614      41,681      41,791      41,858      41,968      42,057      42,167      42,233
                                                  Item5           709,628       55,157       55,822       56,519         57,231      57,958      58,670      59,444      60,187      60,961      61,750      62,555      63,375
Grand total                                                   $ 2,857,136    $ 234,560    $ 235,078    $ 235,735      $ 236,351   $ 237,060   $ 237,614   $ 238,358   $ 239,027   $ 239,750   $ 240,435   $ 241,210   $ 241,957
Total DL Hours by Month                                           234,104       19,029       19,099       19,188         19,272      19,368      19,443      19,544      19,635      19,733      19,826      19,931      20,033
Average OH/DL hour for company                                $     12.20    $ 12.33      $ 12.31      $ 12.29        $ 12.26     $ 12.24     $ 12.22     $ 12.20     $ 12.17     $ 12.15     $ 12.13     $ 12.10     $ 12.08



Notes:

1 For simplicity's sake, in this example, variable overhead expenses are allocated by direct labor hours.
2 Overhead includes all expenses that are realized from converting raw materials into finished goods ready to ship.




         D:\Docstoc\Working\pdf\22d1dcf7-1048-473d-8b1e-2f24c5b08a82.xlsx                                                                         Page 7 of 10                                                                                                                    5/24/20118:59 PM
           WHITE CELLS ARE ADJUSTABLE
                                                                                                                                         © Kristoffer Burnett - Certified Management Accounta
                                                                                                                                               Small business solutions at http://www.imperoc
            Operating Budget Example


  Ending Finished Goods Inventory
                                                         Total
                                     Item   Per Unit   Dec-2011
                                    Item1
Ending inventory                                            443    Plus ending inventory from the Production Budget
Cost of direct materials                    $ 25.56    $ 11,323    Total Material Cost from Direct Materials Budget * Ending inventory
Cost of direct labor                           40.40     17,897    Direct Labor Cost/Unit from Direct Labor Budget * Ending inventory
Cost of overhead                               36.66     16,240    Allocated OH/unit from Overhead Budget * Ending inventory
Total cost                                  $ 102.62   $ 45,461

                                    Item2
Ending inventory                                            381
Cost of direct materials                    $ 131.79   $ 50,212
Cost of direct labor                           22.90      8,725
Cost of overhead                               27.92     10,638
Total cost                                  $ 182.61   $ 69,574

                                    Item3
Ending inventory                                            678
Cost of direct materials                    $ 46.59    $ 31,588
Cost of direct labor                           36.63     24,835
Cost of overhead                               30.71     20,821
Total cost                                  $ 113.93   $ 77,245

                                    Item4
Ending inventory                                            371
Cost of direct materials                    $ 83.73    $ 31,064
Cost of direct labor                           48.39     17,953
Cost of overhead                               40.35     14,970
Total cost                                  $ 172.47   $ 63,986

                                    Item5
Ending inventory                                          1,019
Cost of direct materials                    $ 29.91    $ 30,478
Cost of direct labor                          27.80      28,328
Cost of overhead                              22.72      23,152
Total cost                                  $ 80.43    $ 81,958

Total ending inventory value                           $ 338,224




   D:\Docstoc\Working\pdf\22d1dcf7-1048-473d-8b1e-2f24c5b08a82.xlsx                             Page 8 of 10                                           5/24/20118:59 PM
         WHITE CELLS ARE ADJUSTABLE
                                                                                                                                                 © Kristoffer Burnett - Certified Management Accountant, 2009
                                                                                                                                                       Small business solutions at http://www.imperoco.com
          Operating Budget Example


   Cost of Goods Sold Budget

                                    Item Quantity Value        Extended Value
Beginning FG inventory             Item1    422 $ 102.62       $       43,306 Quantity = (Less) beginning inventory from the Production Budget
                                   Item2    370   182.61               67,566 Value = Total cost from the Ending FG Inventory Budget
                                   Item3    736   113.93               83,852 Extended value = Quantity * Value
                                   Item4    354   172.47               61,054
                                   Item5    815 $ 80.43        $       65,550
Total                                                          $      321,329

Manufactured units and costs       Item1   14,811   $ 102.62   $     1,519,905 Quantity = Units to be produced from the Production Budget
                                   Item2   12,864     182.61         2,349,095
                                   Item3   24,253     113.93         2,763,144
                                   Item4   12,412     172.47         2,140,698
                                   Item5   31,225   $ 80.43    $     2,511,427
Cost of goods manufactured                                     $    11,284,269

Cost of goods available for sale                               $    11,605,597 Total of Beginning FG Inventory + Cost of goods manufactured

Ending FG inventory                Item1     (443) $ 102.62    $       (45,461) Quantity = Plus ending inventory from the Production Budget
                                   Item2     (381)   182.61            (69,574)
                                   Item3     (678)   113.93            (77,245)
                                   Item4     (371)   172.47            (63,986)
                                   Item5   (1,019) $ 80.43     $       (81,958)
Total                                                          $      (338,224)

Cost of goods sold                                             $    11,267,373 Cost of goods available for sale - Total Ending FG inventory
               WHITE CELLS ARE ADJUSTABLE
                                                                                                                                                                           © Kristoffer Burnett - Certified Management Accountant, 2009
                                                                                                                                                                                 Small business solutions at http://www.imperoco.com
                Operating Budget Example

    SG&A Budget
                                               1        Jan-2011    Feb-2011    Mar-2011    Apr-2011    May-2011    Jun-2011    Jul-2011    Aug-2011    Sep-2011    Oct-2011    Nov-2011    Dec-2011
      Department     Fixed/Variable           Total
Finance/Accounting       Fixed            $   480,690   $ 40,826    $ 36,875    $ 40,826    $ 39,509    $ 40,826    $ 39,509    $ 40,826    $ 40,826    $ 39,509    $ 40,826    $ 39,509    $ 40,826
Sales/Marketing         Variable              848,276      72,045      65,073      72,045      69,721      72,045      69,721      72,045      72,045      69,721      72,045      69,721      72,045
Research/Development     Fixed                565,517      48,030      43,382      48,030      46,481      48,030      46,481      48,030      48,030      46,481      48,030      46,481      48,030
Shipping                Variable              395,862      33,621      30,368      33,621      32,537      33,621      32,537      33,621      33,621      32,537      33,621      32,537      33,621
Customer Service         Fixed                537,241      45,629      41,213      45,629      44,157      45,629      44,157      45,629      45,629      44,157      45,629      44,157      45,629
Total                                     $ 2,827,586   $ 240,151   $ 216,911   $ 240,151   $ 232,404   $ 240,151   $ 232,404   $ 240,151   $ 240,151   $ 232,404   $ 240,151   $ 232,404   $ 240,151




Notes:

1 These amounts will include any expense that was not included in overhead (except interest and taxes). Unlike DM, DL, and OH, they can not be inventoried.
ds sold                           (11,267,373) "Cost of goods sold" from "Cost Of Goods Sold Budget"
Gross profit                                   4,679,448 B6 - B7

Operating expenses                              (2,827,586) "Total" from "SG&A Budget(s)"
Operating profit (EBITDA)                        1,851,862 B8 - B10

Depreciation                                     (361,250)   The non-cash write down of property and equipment
Interest income                                       951    From interest bearing investments
Interest expense                                  (44,083)   From short and long-term borrowings used to finance operations
Tax                                              (506,618)   From cell B4

Net profit                                  $     940,862 Amount available after all expenses have been paid
Burnett, 2010
 www.imperoco.com




Sold Budget"




to finance operations

				
DOCUMENT INFO
Description: The operating budget is an outline of the expected revenues and costs related to normal operations over a specific time period. With the flex budget, revenues and variable costs are adjusted to reflect a greater or lesser level of activity.
ImperoCo Business Solutions ImperoCo Business Solutions http://www.imperoco.com
About ImperoCo's mission is to assist small and medium sized businesses in maximizing the efficiency of their operations; thereby increasing their ability to compete more effectively.