Docstoc

Partial budgeting Template

Document Sample
Partial budgeting Template Powered By Docstoc
					                       A                           B             C                      E                    F

1    Partial budgeting form (Profitability impacts)
2    Change being considered:
3                          Litters per year:           141.6
4                    Positive impacts                          Per                   Competing impacts
 5   Increased incomes                         $                      Increased costs                    $
 6                                                     $0.00 litter                                              $0.00
 7                                                                                                               $0.00
 8                                                     $0.00 year
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 Total increased incomes                         $0.00              Total increased costs                  $0.00
26 Reduced costs                               $                      Reduced incomes                    $
27
28
29
30
31   Total reduced costs                           $0.00              Total reduced incomes                  $0.00
32   Total positive impacts                        $0.00              Total competing impacts                $0.00
     A   B   C       E                                F
33               Per year difference:                $0.00
34                         Per litter:               $0.00
35
36
37               Calculating capital recovery charges
38                                           $0 Beginning market value
39                                           $0 Ending market value
40                                             5 Years of useful life
41                                        6.00% Interest rate
42                                        $0.00 Capital recovery charge
       G   I   J   K   L

1

2
3
4    Per
 5
 6 l
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
                                                 G        I   J   K   L
                                           33
                                             34
                                             35
                                             36
Calculating capital recovery charges         37
                                             38
                                Beginning market value
                                             39
                                Ending market value
                                             40
                                Years of useful life
                                             41
                                             42
                                Capital recovery charge
                        A                            B             C         D                             E

1    Partial budgeting form (Profitability impacts)
2    Change being considered: (baseline template; no changes)
3                           Litters per year:            141.6          multiplier
4                     Positive impacts                           Per                                    Competing impacts
 5   Increased incomes                          $                                        Increased costs
 6                                                       $0.00 litter         141.6
 7
 8                                                       $0.00 year                  1
 9
10      The subtotal "Total increased incomes" (cell B25) is calculated
11                                                                                                         The multiplier values in D and H
        with this formula: =SUMPRODUCT(B6:B24,D6:D24)                                                      =IF(F6="","",IF(LEFT(G6,1)="l",$B$3,
12
13      This multiplies each value in column B with the value in column D                                  If the first character of the “Per”
14      and then adds those products. Other subtotals are similar.                                         multiplier will be the number of li
15      Tip: If your spreadsheet application doesn't support the SUMPRODUCT
16                                                                                                         columns are normally hidden.
        calculation, you can just use an extra column to first multiply columns B and
17                                                                                                         Tip: The "$" characters lock that part
        D (and F and H); then calculate the sum of this extra column for the
18                                                                                                         value at cell B3 so you can copy this
        subtotal.
19
20
21
22
23
24
25 Total increased incomes                            $0.00                              Total increased costs
26 Reduced costs                                $                                        Reduced incomes
27
28
29
30
31   Total reduced costs                              $0.00                              Total reduced incomes
32   Total positive impacts                          $0.00                               Total competing impacts
                A                         B       C       D     E
33                                                            Per year difference:
34                                            =B25+B31                  Per litter:
35
36
37   This area of the worksheet helps you calculate the          Calculating capital recovery charges
38   capital recovery charge. The formula is:                                          $0
39   =((E41*(1+E41)^E40)/((1+E41)^E40-1))*(E38-                                        $0
40   E39)+(E41*E39)                                                                      5
41                                                                                  6.00%
42                                                                                  $0.00
43
                               F         G         H          I                   J          K   L

                      1

                      2
                      3                       multiplier
Competing impacts 4                     Per
                        5 $
                        6         $0.00 l           141.6
                        7         $0.00                  1
                        8
                        9
                       10
  The multiplier values in D and H are selected with this formula:
                       11
  =IF(F6="","",IF(LEFT(G6,1)="l",$B$3,1))
                       12
  If the first character of the “Per” cell in column C or G is the letter “ l ”, the
                       13
                       14
  multiplier will be the number of litters per year; otherwise, it's just 1. These
                       15
  columns are normally hidden.
                       16
  Tip: The "$" characters lock that part of the formula to always use the litters per year
                       17
  value at cell B3 so you can copy this formula anywhere and it will still work.
                       18
                       19
                       20
                       21
                       22
                       23
                       24
                       25        $0.00
                     26   $
                     27
                     28
                     29
                     30
                     31         $0.00
                     32        $0.00          =F25+F31
                              F          G       H
                                             =F25+F31     I   J   K   L
                   33        $0.00
                                             =B32-F32
                      34     $0.00
                      35                        =F33/B3
                      36
                      37
Calculating capital recovery charges
                      38 Beginning market value
                      39 Ending market value
                      40 Years of useful life
                      41 Interest rate
                      42 Capital recovery charge
                      43
                           A                               B              C                             E

1    Partial budgeting form (Profitability impacts)
2    Change being considered: Increased idle time (+2 days)
3                               Litters per year:           149.33
4
5                         Positive impacts                             Per                            Competing impacts
 6 Increased incomes                                $                          Increased costs
 7 Increased value of litter at market                         $21.53 litter   Feed
 8 0.2063 increased pigs per litter                                             36.3676 extra lb to market per litter
 9 x 0.98 mortality in nursery at 2%                                            x 3.33 feed:gain ratio
10 x 0.98 mortality in grower at 2%                                             = 121.10 lb feed
11 x 0.98 mortality in finisher at 2%                                           x $0.07 per lb feed
12 = 0.194168 increased pigs to market                                          = $8.36 cost of feed per litter
13 x 241 lb weight at market
14 = 46.79447 increased lbs to market per litter
15 x $0.46 market price per lb
16 = $21.53 increased value of litter at market
17 Total increased incomes                               $3,215.07             Total increased costs
18 Reduced costs                                    $                          Reduced incomes
19 Pigs not fed                                                $30.30 litter   Decreased capacity
20 0.54721 pigs not fed                                                         10.667 fewer litters
21 x 241 lb market weight                                                       x 8.14 pigs per litter
22 = 131.88 lb not produced                                                     x 0.98 x 0.98 x 0.98 mortality
23 x 3.33 feed:gain ratio                                                       = 81.717 total pigs lost
24 = 439.12 lb feed not fed                                                     149.33 litters
25 x $0.07 per lb feed                                                          = 0.54721 pigs lost per litter
26 = $30.30 saved feed per litter                                               x 241 lb weight at market
27                                                                              x $0.46 market price per lb
28                                                                              = $60.66 cost of lost pigs due to decreased capacity
29
30 Total reduced costs                                   $4,524.70             Total reduced incomes
31   Total positive impacts                             $7,739.77              Total competing impacts
32                                                                                          Per year difference:
     A   B   C   E
33                   Per litter:
                                               F              G    I   J   K   L

                                   1

                                   2
                                   3
                                   4
         Competing impacts
                         5                                 Per
                                    6   $
                                    7               $8.36 litter
                                    8
                                    9
                                   10
                                   11
                                   12
                                   13
                                   14
                                   15
                                   16
                                   17         $1,248.40
                                   18 $
                                   19              $60.66 litter
                                   20
                                   21
                                   22
                                   23
                                   24
                                   25
                                   26
                                   27
                                   28
st of lost pigs due to decreased capacity
                                   29
                                   30         $9,058.36
                                   31       $10,306.76
                                   32        –$2,566.98
     F         G   I   J   K   L
33   –$17.19
                           A                              B            C

1    Partial budgeting form (Profitability impacts)
2    Change being considered: Sow washing
3                                Litters per year:             160
4
5                        Positive impacts                            Per
 6 Increased incomes                                 $
 7 Increased value of litter at market                        $0.18 litter
 8 0.00169 increased pigs per litter
 9 x 0.98 mortality in nursery at 2%
10 x 0.98 mortality in grower at 2%
11 x 0.98 mortality in finisher at 2%
12 = 0.001591 increased pigs to market
13 x 241 lb weight at market
14 = 0.38333 increased lbs to market per litter
15 x $0.46 market price per lb
16 = $0.18 increased value of litter at market
17
18 Total increased incomes                               $28.80
19   Reduced costs                                   $
20
21
22   Total reduced costs                                  $0.00
23   Total positive impacts                              $28.80
24
25
                                       E                     F             G     I

tability impacts)
             1

             2
             3
             4
             5                      Competing impacts                    Per
             6 Increased costs                          $
             7 Labor                                              $0.73 litter
             8 0.1 hours per pig
             9 x 15 pigs per farrowing
            10 = 1.5 hours per farrowing
            11 x 13 farrowings per year
            12 = 19.5 hours per year
            13 x $6.00 hourly rate
            14 = $117 cost for 75-sow farm
            15 160 litters per year
            16 = $0.73 labor cost per litter
            17
            18 Total increased costs                         $116.80
            19   Reduced incomes                        $
            20
            21
            22   Total reduced incomes                           $0.00
            23   Total competing impacts                    $116.80
            24                Per year difference:           –$88.00
            25                          Per litter:           –$0.55
     J   K   L

1

2
3
4
5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
                           A                                  B              C

1   Partial budgeting form (Profitability impacts)
2   Change being considered: AIAO
3                                Litters per year:                156.8
4
5                         Positive impacts                                Per
 6 Increased incomes                                 $
 7 Increased value of litter at market                            $23.72 litter
 8 0.2273 increased pigs per litter
 9 x 0.98 mortality in nursery at 2%
10 x 0.98 mortality in grower at 2%
11 x 0.98 mortality in finisher at 2%
12 = 0.21393 increased pigs to market
13 x 241 lb weight at market
14 = 51.558 increased lb to market per litter
15 x $0.46 market price per lb
16 = $23.72 increased value of litter at market
17
18
19
20 Total increased incomes                                  $3,719.30
21 Reduced costs                                     $
22 Pigs not fed                                                    $8.66 litter
23 0.15635 pigs not fed
24 x 241 lb market weight
25 = 37.691 lb not produced
26 x 3.33 feed:gain ratio
27 = 125.48 lb feed not fed
28 x $0.07 per lb feed
29 = $8.66 saved feed per litter
30
31 Labor saved: monitoring                                   $1,950.00 year
32 $6.00 cost of labor
33 x 50 hours required per farrowing with continuous flow
34 x 0.50 AIAO savings of 50% of monitoring time
35 = $150 saved per farrowing
36 x 13 farrowings per year
37 = $1950 saved per year
38
39 Labor saved: cleaning                                       $175.50 year
40 6. hours labor with continuous flow
41 – 3.75 hours with AIAO
42 = 2.25 hours savings with AIAO
43 x $6.00 per hour labor cost
44 x 13 farrowings per year
45 = $175.50 labor saved per year
46
47 Total reduced costs                                      $3,483.39
                     A           B        C
48   Total positive impacts   $7,202.68
49
50
                                             E                                        F              G     I

ability impacts)
             1

             2
             3
             4
             5                           Competing impacts                                        Per
             6 Increased costs                                                   $
             7 Feed                                                                        $11.85 litter
             8 51.558 extra lb to market per litter
             9 3.33 feed:gain ratio
            10 = 171.69 lb feed
            11 x $0.07 per lb feed
            12 = $11.85 cost of feed per litter
            13
            14 Capital recovery charge                                                    $482.78 year
            15 • $4000 beginning market value
            16 • $800 ending market value
            17 • 10 years useful life
            18 • 6.00% interest rate
            19 (for one-time costs to convert facilities from continuous flow to AIAO)
            20 Total increased costs                                                   $2,340.86
            21 Reduced incomes                                              $
            22 Decreased litters                                                     $2,717.84 year
            23 3.2 fewer litters
            24 x 8.14 pigs per litter
            25 x 0.98 mortality in nursery at 2%
            26 x 0.98 mortality in grower at 2%
            27 x 0.98 mortality in finisher at 2%
            28 = 24.516 fewer pigs to market
            29 x 241 lb at market
            30 x $0.46 market price per lb
            31 = $2717.84 lost
            32
            33
            34
            35
            36
            37
            38
            39
            40
            41
            42
            43
            44
            45
            46
            47 Total reduced incomes                                              $2,717.84
                     E                       F        G   I
48   Total competing impacts              $5,058.70
49                Per year difference:    $2,143.98
50                          Per litter:      $13.67
     J   K   L

1

2
3
4
5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
                           A                               B             C                               E                      F

1    Partial budgeting form (Profitability impacts)
2    Change being considered: Pressure wash and disinfect (Cleaning)
3                                Litters per year:               160
4
5                         Positive impacts                             Per                            Competing impacts
 6 Increased incomes                                 $                         Increased costs                            $
 7 Increased value of litter at market                         $35.99 litter   Feed                                                 $18.23
 8 0.3449 increased pigs per litter                                             78.233 extra lb. to market per litter
 9 x 0.98 mortality in nursery at 2%                                            x 3.33 feed:gain ratio
10 x 0.98 mortality in grower at 2%                                             = 260.51 lb. feed
11 x 0.98 mortality in finisher at 2%                                           x $0.07 per lb. feed
12 = 0.32462 increased pigs to market                                           = $18.23 cost of feed per litter
13 x 241 lb weight at market
14 = 78.233 increased lbs to market per litter                                 Labor                                             $175.50
15 x $0.46 market price per lb.                                                 0.15 hours per crate
16 = $35.99 increased value of litter at market                                 x 15 crates for 75-sow farm
17                                                                              x 13 farrowings per year
18                                                                              = 29.25 hours per year
19                                                                              x $6.00 cost of labor per hour
20                                                                              = $175.50 yearly labor cost of cleaning
21
22                                                                             Capital recovery charge                           $712.19
23                                                                               • $3000 beginning market value
24                                                                               • $0 ending market value
25                                                                               • 5 years useful life
26                                                                               • 6.00% interest rate
27                                                                             (to purchase pressure washer)
28 Total increased incomes                               $5,758.40             Total increased costs                          $3,804.49
29   Reduced costs                                   $                         Reduced incomes                            $
30
31
32   Total reduced costs                                       $0.00           Total reduced incomes                                $0.00
                     A           B        C                   E                       F
33   Total positive impacts   $5,758.40       Total competing impacts              $3,804.49
34                                                         Per year difference:    $1,953.91
35                                                                   Per litter:      $12.21
      G     I   J   K   L

1

2
3
4
5    Per
 6
 7 litter
 8
 9
10
11
12
13
14 year
15
16
17
18
19
20
21
22 year
23
24
25
26
27
28
29
30
31
32
1
32

				
DOCUMENT INFO