# Partial budgeting Template by Rabia06

VIEWS: 212 PAGES: 23

• pg 1
```									                       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
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

```
To top