1

The Calculation of Postal Inframarginal Costs

Charles McBride

1. Introduction

In this task, we develop and apply methodologies for calculating inframarginal costs by cost 

component. We first present a review of the literature on using the USPS accounting system to 

calculate marginal, volume variable, specific fixed, attributable, incremental, and inframarginal 

costs in Section 2.2 below. Since the constant elasticity function plays a pivotal role in USPS 

cost analysis, we describe this function and its properties in Section 2.3. In Section 2.4, we 

describe our methods for calculating inframarginal costs. We present our results for Fiscal Years 

2007 and 2013 in Section 2.5. Finally, we provide a summary of our Excel workpaper in an 

Appendix. 

2. Review of Relevant Literature

The purpose of this section is to review the history of the development and calculation of USPS 

incremental costs in the formal economics literature and in earlier PRC rate cases. This review 

will serve to introduce the extensive (and sometimes confusing) terminology used by economists 

and the Postal Service in this area, and will also be used as the starting point for our calculations 

of inframarginal costs. 

Bradley, Colvin and Smith, 1993

USPS inframarginal costs were first mentioned publicly in a 1993 paper by Bradley, Colvin, and 

Smith.1The authors noted that marginal costs vary with the level of output, but do not 

incorporate “any truly fixed cost or infra-marginal cost” (see page 135, last partial paragraph). 

They also mentioned that inframarginal cost “...is the amount of variable cost that has been 

incurred in producing all the previous units of output up to the current level of production” (see 

page 136, first partial paragraph). The authors then address how the postal product cost structure 

can be estimated from USPS accounting data. Because of the complexities involved in 

estimating an aggregate top-down cost function, “postal product costing, including econometric 

cost estimation, is conducted on a disaggregated basis” (see page 138, third full paragraph). This 

bottom-up approach involves estimating volume-variable cost2for each cost component within 

the twenty USPS cost segments. The importance of incremental costs in product pricing to avoid 

cross-subsidization is also discussed, but the authors note that the USPS accounting system was 

not being used to estimate incremental costs at that time.

Bradley, Colvin and Panzar, 1999

Further developments in the use of the Postal Service accounting system for product pricing were 

described in a 1999 paper by Bradley, Colvin, and Panzar.3They described the USPS cost 

 

1

Bradley, Michael; Jeff Colvin and Mark Smith (1993), “Measuring Product Costs for Ratemaking: The United 

States Postal Service,” in M.A. Crew and P.R. Kleindorfer eds., Regulation and the Nature of Postal and Delivery 

Services, Kluwer Academic Publishers.

2

The authors note that volume variable cost is a term used by the Postal Service to refer to the product of marginal 

cost and output and that it does not refer to the “textbook concept of (total or average) variable cost.”

3 Bradley, Michael; Jeff Colvin and John Panzar (1999), “On Setting Prices and Testing Cross-Subsidy Using 

Accounting Data,” Journal of Regulatory Economics, 16: 83-100.
2

system in terms of a “bottom-up” cost component structure, which incorporates both economies 

of scale and economies of scope. Each cost component j may have fixed costs Fj, which include 

both product specific and common costs; and/or variable costs VCj(Dj), which are solely a 

function of the total amount of a cost driver Dj. For each component, the driver Dj is a weighted 

sum of the product volumes, where the weight for a given product is the amount of the driver 

required for one unit of the product.

4

 The authors then show analytically that volume variable 

cost VVCj for a component is the product of total cost Cj times the elasticity ej of cost with 

respect to the driver. 

Although not explicitly stated in the paper, this functional form for Cj, which reflects economies 

of scale and scope, is the constant elasticity function. After use of some algebra and differential 

calculus, the authors demonstrate that Cj = ejCj + (1-ej)Cj, where the first term of this equation is 

the component’s volume variable cost and the second term “includes both the component’s fixed 

cost and any infra-marginal costs generated by increasing returns to scale in the driver” (see page 

89, last paragraph) Product incremental costs can also be calculated for this cost function. Let 

Dij be the reduction in the driver for component j when product i is removed from the system. 

Then the incremental cost ICij for product i and component j = Fij + VCj(Dj) – VCj(Dj-Dij), where 

Fij is the specific fixed cost for product i and component j.

PRC Docket R97-1

The Postal Service first presented the concept of incremental costs for regulatory review in PRC 

Docket R97-1. The theoretical development was described in testimony by John Panzar (USPST-41) and was very similar to the later version described in the 1999 Bradley et al paper. 

Calculations of product incremental cost for the 1996 base year were provided in the testimony 

of William Takis (USPS-T-41). Witness Takis used the component-by-component “bottom-up” 

approach to calculating incremental costs. He first classified each component as independent or 

dependent, depending on whether or not the component had its own distribution key and a 

variability estimate. Next, he used one of the three following methods for calculating the 

incremental costs for each independent component:

 Equation-Based: incremental costs are calculated using a specific functional form for the 

cost function. The functional form is developed econometrically and usually has more 

than one elasticity parameter. In Docket R97-1, econometric methods were used to 

estimate cost functions for mail processing labor, purchased transportation, and city 

carrier load time.

 Constant Elasticity: used for independent components that have variability estimates and 

distribution keys but do not meet the requirements for equation-based calculation.

 Other: used for independent components that are based on single-subclass stops, specific 

fixed costs for more than one product, or are 100 percent volume variable costs. 

To calculate the incremental costs of the dependent components, Takis uses the “burden” 

method, which distributes costs based on the distribution of the sum of one or more independent 

components.

 

4

For example, the driver might be employee manhours in a particular mail processing operation, or the cost of the 

employee manhours.
3

PRC Docket R2000-1

In Docket R2000-1, the Postal Service again presented theoretical testimony (Michael Bradley, 

USPS-T-22) and empirical calculations (Nancy Kay, USPS-T-23) for incremental costs, but with 

somewhat different procedures than those used in Docket R97-1. First, witness Bradley specifies 

the difference between a “calibrated” cost model and an “estimated” cost model. An estimated 

model has econometrically-estimated parameters using a single set of data. A calibrated model 

has parameters that are determined from a variety of sources, including econometric studies, 

engineering studies and even informed judgment. The calibrated model has a constant elasticity 

structure.

5 A single volume variability for a component can be calculated directly from the 

parameters of an econometric model or simply as the ratio of volume variable costs and accrued 

costs; this volume variability estimate is then used as the elasticity parameter in the constant 

elasticity cost function. Witness Bradley’s R2000-1 approach to calculating incremental costs is 

different from the earlier R97-1 method in that he uses only calibrated models instead of a 

combination of estimated and calibrated models.

As shown in witness Kay’s workpapers,6the Excel workbook WPTables.xls provides a complete 

description of her calculation of incremental cost for each cost component. For components that 

include multiple cost pools within a component (e.g., mail processing and window service), she 

provides this same information on a more detailed (subcomponent) level.

Docket R2006-1 and After

The Postal Service continued its calculations of incremental costs in Dockets R2001-1, R2005-1, 

and R2006-1. The same procedure established in Docket R2000-1 was used in each case, but 

minor changes in the accounting system, such as changing the number of cost pools in various 

cost components, were incorporated. In Docket R2006-1, Witness Dion Pifer presented the most 

recent public version of the USPS methods for calculating incremental costs in USPS-T-18 and 

library reference USPS-LR-L-72.

In 2006, the postal regulatory landscape was changed dramatically by the passage of the Postal 

Accountability and Enhancement Act. The new regulatory focus was primarily on whether the 

USPS prices for market-dominant products stayed under a CPI-based price cap. For purposes of 

changing rates, the USPS filed detailed annual reports to the PRC in “ACR” Dockets instead of 

omnibus rate case filings. Much of the annual data submitted in these reports were similar to the 

data presented in earlier omnibus rate cases. However, incremental costs for non-competitive 

products were no longer published, except for product specific fixed costs which were 

considered to be attributable costs. Incremental costs for competitive products were also 

submitted to the PRC, but with confidentiality restrictions.

3. Constant Elasticity Cost Function

Since the constant elasticity function7plays such an important role in USPS costing, we present a 

short summary of the function and its properties in this section. In mathematical terms, the

 

5 Described above in the summary of the 1999 Bradley et al paper.

6

See the Support Materials folder in USPS-LR-I-150.

7

This function is also known as the “isoelastic” function. For more information on its properties, see the Wikipedia 

article at http://en.wikipedia.org/wiki/Isoelastic_function.
4 Total Cost

Driver

Figure 1

Example of Constant Elasticity Cost Function

D0 D1

C0

C1

O
5

constant elasticity function is defined by C = AD

e

, where C is total cost, A is a constant, D is a 

cost driver, and e is the elasticity parameter. Plots of a typical constant elasticity (e = 0.6) cost 

function and its slope (marginal cost function) are shown in the two figures above. 

It can be seen from Figure 1 that the constant elasticity function passes through the origin and 

thus has no fixed cost; this means that all costs are variable costs (in the economic sense). Figure

2 can be used to show how total costs are segmented into volume variable costs8and 

inframarginal costs. As an example, the total costs at driver level D0 are equal to the area under 

the marginal cost curve from the origin O to D0. The volume variable portion is the rectangular 

area under the line M0-C from the origin O to D0. The infra-marginal portion is the area between 

the marginal cost curve and the line M0-C. As noted in Section 2.2 above, the volume variable 

costs are equal to eAD0

e

= eC0, so by simple algebra, inframarginal costs are equal to (1-e)C0.

These relationships also hold for changes in the driver level and the resulting changes in costs. 

Note in Figure 1 that the effect of increasing the driver level from D0 to D1 is to increase total 

costs from C0 to C1. It can be shown with some algebra and calculus that the exact difference in 

cost is

 [(

 

 

)

 

 ]

It should be noted that if the change in the driver D = D1 – D0 is small compared to D0, then it 

can be shown that approximately, C/C0 = e(D/D0). Thus the percentage change in costs is 

approximately equal to the elasticity e times the percentage change in the driver.9

The difference in total costs is the area enclosed by the points D0-C-E-D1. The inframarginal 

cost difference is the area C-D-E, and the volume variable cost difference is the area D0-D-E-D1. 

The change in volume variable and inframarginal costs can shown to equal eC and (1-e)C, 

respectively.

The constant elasticity function plays a major role in postal costing because it is a simple oneparameter function that can reflect the economies of scale and scope inherent in many postal 

activities. As noted above, it has the useful analytical property that the change in volume 

variable equals eC, and so the change in inframarginal costs equals (1-e)C. This means that 

volume variable costs and inframarginal costs are proportional for all levels of the driver.

4. Approach for Calculating Inframarginal Costs 

In this section we describe the steps and data sources used to calculate inframarginal costs for 

Fiscal Years 2007 and 2013. We will illustrate the process for FY 2007, but the same process is 

used for FY 2013.

Our approach incorporates the same USPS constant elasticity structure10 used to calculate 

volume variable and incremental costs. We will use the same bottom-up calculation method

 

8 Recall from the above literature review that USPS volume variable costs do not correspond to the economic 

concept of variable costs. It is apparently a term used only by the Postal Service to indicate the product of marginal 

costs and product volume.

9 This approximation is based on the first-order term of the Taylor Series expansion for the function (1+x)e and is

reasonably accurate for values of x up to 0.20.
6

described in Section 2.2 above, so we need source data that contains the maximum amount of 

cost component detail. This detailed data is contained in the USPS CRA Model Public B Cost 

Matrix, which is provided as a USPS library reference for the various ACR dockets, starting with 

Docket ACR2007.11 This cost matrix is an Excel workbook that contains cost data by cost 

segment, cost component, and product.

For purposes of calculating inframarginal costs for a cost component, we do not need the product 

detail, so we use only the attributable, Other,12 and total costs from the CRA Cost Matrix. Also, 

we do not need to know the product specific fixed costs that are used in calculating incremental 

costs. Since FY 2007, the Postal Service has considered product specific fixed costs to be part of 

attributable costs, along with volume variable costs. But attributable costs have already been 

calculated and are included in the CRA cost matrix, so our task is simplified considerably. To 

calculate inframarginal costs, we need only to split Other costs into inframarginal and fixed 

portions, as summarized below:

 For independent cost components that use the constant elasticity cost function, Other 

costs are inframarginal. 

 For non-constant elasticity components, Other costs are fixed.

13

 

 For dependent cost components, Other costs will usually be part inframarginal and part 

fixed, because dependent components are based on a sum of components, some of which 

have inframarginal costs while others have fixed costs. 

Independent Components

Independent components that are 100 percent fixed are easily identified in the CRA Cost Matrix. 

To classify the other independent components, we use the approach introduced by witnesses 

Bradley and Kay in Docket R2000-1 and updated by witness Pifer (USPS-T-18) in Docket 

R2006-1. Table 1 below contains excerpts from Pifer’s worksheet WPTable1.xls.14 The column 

labeled “IC Model Reference” lists the R2006-1 Pifer worksheet(s) used to calculate the costs for 

each component. 

 

10 The constant elasticity structure may not refer only to cost components that use the constant elasticity function. It 

may also apply to other components that have a volume variability that is considered constant over time even though 

there are fixed costs. A more apt term might be “constant variability structure.”

11 USPS-LR-5 – FY 2007 CRA Model (Model Files, Cost Matrices, and Reports).

12 For pricing purposes, the term “contribution to institutional costs” is used to indicate the difference between the 

revenue and the attributable cost for a product, so it is similar to the business term “product profitability.” In USPS 

cost analysis, the term “other” costs is used to mean the difference between total costs and attributable costs. To 

avoid confusion, in this paper we will assume that institutional costs are equal to other costs, but we will use both 

terms depending on the subject under discussion. To reduce confusion, we will capitalize the term “Other.” 

13 Again, this approach was used by the Postal Service to calculate incremental costs before FY 2007, and, with 

serious reservations, we adopt the same approach for calculating inframarginal costs in this paper.

14 The complete version of Pifer’s Table 1 is included in our Excel workpaper.
7

Table 1. Examples of Inframarginal Cost Calculations

Cost 

Segment

Cost Component/ 

Description

CRA Model 

Component 

Number

Source of 

Cost Pool

Incremental 

Cost 

Treatment, 

R2006-1

R2006-1

IC Model 

Reference

1 Postmasters EAS 23 

and Below

1 CE ConsElas.xls, 

ICSummary.xls

2 Higher Level 

Supervisors

30 CE ConsElas.xls

Central Mail MarkUp Supervision677 IC=VVC ICSummary.xls

3 Admin Clerks –

Claims and Inquiry

66 IC=VVC ICSummary.xls

Admin Clerks – Data 

Collection

421 CE ConsElas.xls

Admin Clerks Other 41 IC=VVC, PS ICSummary.xls

Admin Clerks 

Specific Fixed

227 PS ICSummary.xls

11 Operating Equip. 

Maintenance 

Personnel

75 Distributed 

to cost 

pools using 

keys

ICSummary.xls

OCR 1233 CE Equip.xls

MPBCS 1234 CE Equip.xls

CSBCS 1235 CE Equip.xls

DBCS 1236 CE Equip.xls

MERLIN 1237 CE Equip.xls

FSM 1238 CE Equip.xls

LTR Cancel 1240 CE Equip.xls

Flats Cancel 1241 CE Equip.xls

Cull 1242 CE Equip.xls

RBCS 1243 IC=VVC Equip.xls

CFS 1231 CE Equip.xls

The first row in the table shows that costs for the Segment 1 component Postmasters EAS 23 and 

Below are based on the constant elasticity function, so all of the Other costs for this component 

are inframarginal. The same is also true for the Segment 2 component Higher Level 

Supervisors. However, the Segment 2 component Central Mail Markup Supervision is 

considered part attributable and part Other, so Other costs are assumed fixed for this component. 

Similarly, the Other costs for the Admin Clerks Other component in Segment 3 are fixed, since 

this component’s incremental costs are equal to the sum of its volume variable costs and product 

specific fixed costs. 

The Operating Equipment Maintenance Personnel component in Segment 11 requires a more 

complex classification method, because it is based on the sum of costs from several cost pools, 

each with its own variability estimate and distribution key. Fortunately, all of the cost pools 

except for the RBCS pool use the constant elasticity method, and so the sum of Other costs for

these pools will be inframarginal. Given the fact that our goal in this paper is to calculate a 

relatively accurate estimate of system-wide inframarginal costs, it seems appropriate to assume 

the RBCS pool is also a constant elasticity pool if the error caused by this approximation is 

small. Because the FY 2007 CRA Cost Matrix contains the distribution keys for these cost 

pools, we can estimate the size of this error. RBCS fixed costs amount to less than 0.1 percent of 

total costs for these pools, so the effect of assuming this amount is inframarginal rather than 
8

fixed is negligible. Multiple cost pools are also used for other components, including mail 

processing and window service, but the constant elasticity pools are much greater in magnitude 

than the non-constant elasticity pools, so the same approximation will be used for these 

components.

Dependent Components

Attributable and Other costs for dependent components are calculated based on the proportions 

of attributable and Other costs in a distribution key which is the sum of one or more independent 

components. However, to calculate inframarginal and fixed costs for dependent components, it 

is necessary to first total up the inframarginal and fixed costs in the various components in the 

distribution key. The specific cost components used to form these keys are listed in the USPS 

documentation for the CRA Cost Model in the various ACR dockets.15 The details of these 

calculations are provided in the Task 2 Excel workpapers, which are described in the Task 2 

Appendix.

5. Results of Inframarginal Cost Calculations

There are about 250 cost components in the USPS cost accounting system, too many to list in 

this paper, but the data for each component are included in our workpaper. 

Results Based on USPS R2006-1 Method

Our results are based on the R2006-1 Postal Service assumptions regarding the classification of 

all cost components as far as the level of volume-variable cost variability and whether or not the 

component was considered to be in the constant elasticity family of cost functions. Table 2 

below shows summary FY 2007 and FY 2013 results for attributable, Other, total, inframarginal

and fixed costs by cost segment. Table 3 shows summary FY 2013 results in the same format, 

but these costs are in real 2007 dollars to account for the 12.4 percent CPI change over that 

period. 16

We feel obliged to say that we have serious reservations about the lack of a consistent approach 

as well as documentation for the criteria used by the Postal Service to decide which components 

would be designated as constant elasticity components and which would not. This problem may 

be due to the fact that the concept of incremental cost was rejected by the Commission in this 

early period, and that this lack of interest resulted in the absence of the usual litigation-based 

scrutiny of any new costing concept. It should be kept in mind when reviewing the numerical 

results for inframarginal costs presented in Tables 2 and 3 and elsewhere in this paper.

The results shown in the above tables have been adjusted to remove the distortions caused by 

recent large changes in Worker’s Compensation and Retiree Health Benefits costs over the FY 

2007-2013 time period. The italicized rows labeled “unadj” for CS 18 and All Segments are 

included to show the large difference in results with and without this adjustment. 

 

15 For example, the ACR2007 keys were based on USPS-LR-4, workbook FY07.cntl.xls, sheet DK Addends.

16 BLS CPI Inflation Calculator, http://data.bls.gov/cgi-bin/cpica.
9

Cost Segment

FY07 Total 

Attributable

FY07 Other 

Costs

FY07 Total 

Costs

FY07 Attrib 

Cost %

FY07 InfMar 

Costs

FY07 Fixed 

Costs

FY07 Total 

Cost Share

C/S 1 Postmasters 389,082 1,799,296 2,188,378 17.78% 1,745,221 54,075 3.2%

C/S 2 Supervisors & Technicians 2,293,693 1,789,004 4,082,697 56.18% 903,980 885,024 5.9%

C/S 3 Clerks & Mailhandlers CAG A-J 15,663,709 3,004,184 18,667,893 83.91% 2,780,389 223,795 27.0%

C/S 4 Clerks CAG K 2,935 1,776 4,711 62.30% 1,776 0 0.0%

C/S 6 City Delivery Carriers Office 4,310,057 867,344 5,177,402 83.25% 0 867,344 7.5%

C/S 7 City Delivery Carriers Street 4,107,048 6,981,595 11,088,642 37.04% 5,401,234 1,580,361 16.0%

C/S 8 Vehicle Service Drivers 397,499 260,177 657,676 60.44% 260,177 0 1.0%

C/S 10 Rural Carriers 2,584,381 3,777,322 6,361,703 40.62% 0 3,777,322 9.2%

C/S 11 Custodial & Maint Services 2,325,875 996,398 3,322,273 70.01% 996,398 0 4.8%

C/S 12 Motor Vehicle Service 324,959 883,828 1,208,787 26.88% 284,797 599,031 1.7%

C/S 13 Misc Local Operations 61,331 672,645 733,976 8.36% 98,372 574,273 1.1%

C/S 14 Transportation 5,764,377 737,651 6,502,028 88.66% 633,712 103,939 9.4%

C/S 15 Building Occupancy 1,333,251 602,706 1,935,957 68.87% 264,319 338,387 2.8%

C/S 16 Supplies & Services 1,339,143 1,427,414 2,766,557 48.40% 627,007 800,406 4.0%

C/S 17 Research & Development 0 19,792 19,792 0.00% 0 19,792 0.0%

Unadj C/S 18 Admin and Area Operations 3,459,948 6,526,167 9,986,116 34.65% 14.4%

Adj C/S 18 Admin & Area Ops (no WC/RHB) 173,217 1,795,687 1,968,904 8.80% 23,127 1,772,560 2.8%

C/S 19 General Management Systems 0 50,575 50,575 0.00% 0 50,575 0.1%

C/S 20 Other Servicewide Expenses 1,706,522 695,100 2,401,622 71.06% 394,475 300,624 3.5%

Unadj All Segments 46,063,811 31,092,974 77,156,785 59.70%

Adj All Segments (no WC/RHB) 42,777,080 26,362,493 69,139,573 61.87% 14,414,984 11,947,510 100.0%

Category % for Adj All Segments 61.87% 38.13% 100.00% 20.85% 17.28%

Cost Segment

FY13 Total 

Attributable

FY13 Other 

Costs

FY13 Total 

Costs

FY13 Attrib 

Cost %

FY13 InfMar 

Costs

FY13 Fixed 

Costs

FY13 Total 

Cost Share

C/S 1 Postmasters 318,002 1,476,512 1,794,514 17.72% 1,426,386 50,126 3.2%

C/S 2 Supervisors & Technicians 1,533,153 1,292,062 2,825,215 54.27% 707,969 584,093 5.1%

C/S 3 Clerks & Mailhandlers CAG A-J 10,220,412 1,998,069 12,218,481 83.65% 1,874,618 123,451 21.9%

C/S 4 Clerks CAG K 3,727 1,409 5,136 72.56% 1,409 0 0.0%

C/S 6 City Delivery Carriers Office 2,735,784 459,626 3,195,410 85.62% 0 459,626 5.7%

C/S 7 City Delivery Carriers Street 4,012,868 6,700,689 10,713,557 37.46% 5,223,174 1,477,515 19.2%

C/S 8 Vehicle Service Drivers 324,955 212,694 537,649 60.44% 212,694 0 1.0%

C/S 10 Rural Carriers 2,024,106 4,004,495 6,028,601 33.58% 0 4,004,495 10.8%

C/S 11 Custodial & Maint Services 1,918,113 801,201 2,719,314 70.54% 801,201 0 4.9%

C/S 12 Motor Vehicle Service 367,835 971,741 1,339,577 27.46% 286,032 685,709 2.4%

C/S 13 Misc Local Operations 61,275 486,835 548,110 11.18% 93,905 392,930 1.0%

C/S 14 Transportation 5,210,289 781,401 5,991,690 86.96% 653,677 127,724 10.7%

C/S 15 Building Occupancy 1,145,144 427,106 1,572,250 72.83% 211,788 215,318 2.8%

C/S 16 Supplies & Services 1,153,825 1,240,899 2,394,724 48.18% 451,077 789,822 4.3%

C/S 17 Research & Development 437 18,647 19,084 2.29% 0 18,647 0.0%

Unadj C/S 18 Admin and Area Operations 2,655,460 7,714,287 10,369,747 25.61% 0 0 18.6%

Adj C/S 18 Admin & Area Ops (no WC/RHB) 159,342 1,726,541 1,885,884 8.45% 13,224 1,713,317 3.4%

C/S 19 General Management Systems 91 40,952 41,043 0.22% 0 40,952 0.1%

C/S 20 Other Servicewide Expenses 1,501,559 524,965 2,026,525 74.10% 321,020 203,946 3.6%

Unadj All Segments 35,187,035 29,153,590 64,340,625 54.69% 12,264,950 9,174,353

Adj All Segments (no WC/RHB) 32,690,918 23,165,844 55,856,762 58.53% 12,278,174 10,887,670 100.0%

Category % for Adj All Segments 58.53% 41.47% 100.00% 21.98% 19.49%

Table 2. Summary of FY 2007 Segment Costs (no WC/RHB) 

Table 3. Summary of FY 2013 Segment Costs (2007$, no WC/RHB) 
10

Effect of Cost Changes between FY 2007 and FY 2013

Implementation of the R2006-1 USPS calculations of incremental costs required many 

assumptions about the variability structures of the independent cost components. For example,

assigning a component to the constant elasticity family is tantamount to assuming the component 

is 100 percent variable in the economic sense. As discussed in the paragraph above, it is not 

clear to us what criteria were used by the Postal Service to make those choices for FY 2005. 

However, there were some inconsistencies. 

For example, Higher Level Supervisors used the constant elasticity method and so were 100 

percent variable (in the economic sense), while Other Supervisors and Technicians were 

considered fixed. Window Service components used the constant elasticity method, while 

Contract Stations were assumed to be 100 percent fixed.

Possibly, many of these decisions about volume variabilities were based on the favorable postal 

economic environment at the time. Volume was slowly but consistently growing, and 

The number of mid- and higher-level management employees changed very slowly during the 

typical three-year period between rate cases, so it made sense to consider these and other costs to 

be fixed. However, the unprecedented volume decline from FY 2005 to FY 2013 and the 

associated 30 percent reduction in the size of the USPS work force (including not only craft 

employees, but higher-level EAS and PCES employees) has shown that many of these 

assumptions were overly optimistic. Of course, we have the advantage of 20/20 hindsight, but 

the numbers show that costs for many of these so-called fixed or near-fixed cost components 

declined even more than the system-wide total cost, as shown in Table 4 below.

It can be seen from the table that real total costs declined by 19.2 percent between FY 2007 and 

FY 2013. Most of the other eleven components in the table declined much more. These eleven 

components are normally considered in the CRA system to be system-wide indirect costs which

are distributed using some sort of system-wide distribution key, such as all USPS labor or all 

costs. The first row of the table shows that the economic variability of all (adjusted) costs in 

2007 was 82.7 percent. The economic variability was only 31.2 percent for the sum of these 

Component Comp 

No.

Comp 

Category* 

FY07 Total 

Costs

FY07 Attrib 

Cost %

FY07 

InfMar 

Cost %

FY07 Fixed 

Costs

FY13 Total 

Costs (2007$)

%Chg Total 

FY13/FY07

Adj All Segments (no WC/RHB) 69,139,573 61.9% 20.8% 12,000,257 55,856,762 -19.2%

C/S 2 Prod Specific & Other S&T 0033 PSPF 594,581 0.0% 0.0% 594,480 391,544 -34.1%

C/S 3 General Office & Clerical 0422 IND 294 812,451 64.8% 20.3% 121,854 440,521 -45.8%

C/S 13 Employee Awards 0115 100F 365,498 0.0% 0.0% 365,498 237,284 -35.1%

C/S 13 Communications 0168 PSPF 102,272 0.0% 0.0% 102,249 64,111 -37.3%

C/S 15 Building Projects Expense 0169 100F 234,635 0.0% 23.6% 234,635 151,740 -35.3%

C/S 16 ADP Supplies & Services 0174 PAPF 466,503 0.3% 0.0% 464,874 576,304 23.5%

C/S 16 Misc Postal Sup& Serv 0177 IND 526 1,088,926 60.4% 0.0% 177,753 786,021 -27.8%

C/S 18 Inspection Serv Field Support 0195 100F 458,386 0.0% 0.0% 458,386 408,175 -11.0%

C/S 18 Supplies & Services 0210 PSPF 233,544 10.2% 0.0% 209,735 198,086 -15.2%

C/S 18 Area Administration 0193 100F 179,879 0.0% 0.0% 179,879 93,460 -48.0%

C/S 18 Headquarters 0191 PSPF 733,521 1.9% 8.0% 719,894 689,041 -6.1%

 Total (w/o first row) 5,270,196 23.2% 8.0% 3,629,237 4,036,287 -23.4%

*This column contains abbreviations showing the method of calulcation for each component. "PSPF" means part specific fixed, part fixed;

 "100F" means 100% fixed, and IND# means component # is the distribution key.

Table 4. FY13/FY07 Percent Change in Real Total Cost for Selected Components
11

eleven components. Moreover, their combined fixed cost was $3.6 billion, which is 30 percent of 

total fixed costs, so these non-standard variability assumptions have a significant effect on the 

level of system-wide fixed costs. As an illustration, if this set of components was assumed to 

have the same economic variability as all costs (82.7 percent), their fixed costs would be reduced

by about $2.7 billion, which in turn would reduce the 2007 system-wide fixed cost percentage to 

(17.28-3.92) = 13.36 percent.

Discussion of Results

Table 5 below shows summary results for FY 2007 and FY 2013 based on the Postal Service’s 

R2006-1 method for classifying the various components.

17

 It can be seen by that the overall 

attributable cost share declined from 61.87 percent in FY 2007 to 58.53 percent in FY 2013. 

From the last two columns of those rows, it can be seen that most of this 3.34 percentage point 

decrease was due to the increase in the fixed cost share from 17.28 to 19.49 percent, with the 

remainder due to an increase in the inframarginal cost share from 20.85 to 21.98 percent.

With the “bottom-up” USPS cost structure, changes in system-wide variability percentages over 

time are rarely due to large changes in component-level variability parameters.18 More often

they are due to the fact that each year the various cost components have different proportions of 

total accrued costs, and so the system-wide volume variability is in effect an accrued costweighted sum of the volume variability parameters of all components. The 2007-2013 change in 

system-wide fixed cost share provides a good example of this phenomenon. The large drop in 

volume over this period caused a significant drop in accrued cost shares for the Cost Segment 3 

(Clerks and Mailhandlers) components (27.0% to 17.7%), because they are highly variable with 

volume. However, the accrued cost shares for the Cost Segment 7 (City Delivery Street Time) 

components remained about the same (16.0% to 15.5%), because these components are much 

less variable with volume.

Based on this information, several cost components that are now classified as fixed or mostly 

fixed could be considered to be system-wide indirect costs with their economic variabilities

based on system-wide cost variability. A thorough examination of these and other components 

could lead to lower system-wide fixed costs shares, perhaps by four or five percentage points. 

Aside: The Cost Rollforward Model and the USPS Constant Elasticity Structure

The Cost Rollforward Model has been used by the Postal Service to forecast future “test year” 

costs starting with a historical “base year” cost matrix since at least the late 60’s, possibly long 

before the USPS constant elasticity structure was first mentioned. The model has undergone a 

 

17 As noted previously, we have serious concerns about the accuracy of these Postal Service classifications.

18 See the review of the history (1980-present) of USPS cost methodology changes in the Task 3 Report.
12

long series of programming language rewrites,19 starting with Cobol, then Fortran, then C and 

C++, and finally to the Excel Visual Basic for Applications (VBA) language, but the basic 

forecasting algorithms have remained essentially unchanged. The model uses a series of 

forecasting “effects” to make updates in an organized manner. These include cost level changes, 

volume-related changes, non-volume workload changes, productivity changes, and other systemwide level changes.

Surprisingly, it appears that the method used in the Rollforward Model to incorporate volume 

changes into the forecast is compatible with the constant elasticity cost structure. The 

Rollforward Model “test year” volume effect for a given component is calculated as the product 

of the component’s volume variability times its base year accrued cost. 

It is useful to clarify the difference between a component’s “volume variability” and the 

component’s “elasticity.” This confusing terminology issue was described in footnote 15 of 

witness Bradley’s R2000-1 USPS-T-22 testimony. As noted earlier, the term volume variability 

is unique to the USPS, and its value is often less than total volume variability in the economic 

sense. However, for the constant elasticity function used for many postal cost components, the 

volume variability and the elasticity have the same value. In fact, if one substitutes the term 

“elasticity” for “volume variability,” the approximate change in cost due to a percentage change 

in the driver defined in footnote 7 on page 4 of this paper is the same formula as used by the 

Rollforward Model. Another confusing aspect of this terminology issue is that it often happens 

that a USPS component has a total volume variability of 100 percent, but an elasticity much less 

than 100 percent. It is the elasticity value that is most important for forecasting component costs 

as a function of volume, and the Cost Rollforward Model does that job in a reasonably accurate 

manner.

The issue of whether or not inframarginal costs should be used in pricing is discussed at some 

length by Prof. Panzar in the Task 1 Report. The fact that USPS economic (total) variability 

includes both attributable and inframarginal costs does have favorable implications for the ability 

of the Postal Service to cope with decreasing volumes in the future. Previously, institutional 

costs were considered to be unaffected by volume, so about 40 percent of total costs would 

remain if volume declines further. With the addition of inframarginal costs to attributable costs, 

less than 20 percent of total costs are fixed so, ceteris paribus, the Postal Service’s ability to shed 

additional costs is improved. 

 

19 This author performed several of these rewrites while employed by or consulting for the Postal Rate Commission.
13

Appendix

Description of Excel Workpaper and VBA Program

Our Task 2 workpaper consists of one Excel workbook, which includes an Excel Visual Basic 

for Applications (VBA) program (also called “macro” or “module”). The workbook IM07-

13.xls is used to calculate inframarginal costs by component for seven fiscal years – 2007 

through 2013. We describe the various worksheets used in the workbook in Section A-1 below. 

Details on the approach used to calculate inframarginal costs in the VBA program are presented 

in Section A-2.

A-1. Description of Excel Workbook IM07-13.xls

This workbook contains all data required for calculating inframarginal and fixed costs for Fiscal 

Years 2007 and 2013 as well as an Excel VBA program named “FindIM” used to perform most

of the required calculations. Each worksheet in the workbook is briefly described below.

UserDat: This sheet contains some parameters needed to run the VBA program.

ComponentsIn: This sheet is used as a template for other sheets that contain the various 

inframarginal cost results. It contains the attributable, Other, and total costs for each cost 

component in the USPS CRA Model Public B Cost Matrix library reference for Fiscal Years 

2007-13. Rows for subtotals as shown in the CRA Cost matrix are included but are blanked out 

to avoid double counting. The first three columns of this sheet give the cost segment number, 

the component name and CRA component number. The fourth column indicates the variability 

type of the component: ConsElas (uses the constant elasticity function), 100F (100% fixed), 

100A (100% attributable), PSPF (part specific fixed and part fixed), PAPF (part attributable and 

part fixed), and IND # (dependent component distributed according to component #). Besides 

the CRA data, this sheet contains all the formulas required to calculate the cost segment and 

system-wide cost totals shown in rows 281-303.

Case1: This sheet contains the component-level inframarginal and fixed cost results for Fiscal 

Years 2007 (columns E-J) and 2013 (columns AU-AZ) that are used in the Task 2 Report. 

Similar results for the years 2008-12 are shown in the intermediate columns, and are included for 

use in the Task 3 Report. Results for all years are based on the component variability types 

shown in column D. Rows 265-279 contain the calculations for the various distribution keys 

defined in the worksheet Keys1.

Keys1: This sheet shows the specific cost components used to calculate the various distribution 

keys required for the Case1 sheet.

Table 2&3, Table 4 and Table 5: These sheets show the calculations used to create the 

corresponding tables in the main Task 2 Report.

Table 1 (Pifer): This sheet contains the complete Table 1 from witness Pifer’s incremental cost 

model workpapers in R2006-1. Excerpts for this table are shown in Table 1 of the main report.

AppTables: This sheet contains several tables presented in the Task 2 Appendix.
14

A-2. Calculating Inframarginal Costs by Cost Component Using Excel VBA

General Approach

In this section the data and algorithms used in the VBA program FindIM() are described, and a 

listing of the program module is provided.

The inframarginal cost calculation algorithm is very similar to the one used in the CRA 

Rollforward Model for pre-2007 omnibus rate cases. Tables A-1 and A-2 below are presented to

illustrate the process.

Table A-1 presents a sample of the component input data used by the VBA program. This data 

was copied from the FY07 columns (A-J) of the Case1 sheet of workbook IM07-13.xls described 

above. The source of the data in the last four columns of the table is the USPS CRA Model 

Public B Cost Matrix, a USPS library reference for Docket ACR2007. For convenience, the 

Case1 row number is also included as the first column in Table A-1. The “Comp Category” 

column is based on the categorization of cost components used by witness Pifer in Docket 

R2000-1 (see Table 1 in the main Task 2 report). 

The first row of Table A-1 is an example of an independent component that is categorized as 100 

percent fixed (Postmasters EAS 23 & Below, USPS component number 0001), and its category 

type is denoted by “100F.” The fourth row of the table shows an independent constant elasticity 

component (Mail Processing, number 35), denoted as “ConsElas.” The sixth row of Table A-1 

illustrates a component (Segment 6 In-Office Direct Labor, number 43), which is categorized as 

part attributable and part fixed, denoted by the abbreviation “PAPF.” Row 7 of the table shows 

an independent component (0048) which is categorized as 100 percent product specific fixed, 

Case 1 

Row

Segment Component Comp 

No.

Comp 

Category 

FY07 

Attributable 

Costs

FY07 

Other 

Costs

FY07 Total 

Costs

FY07 

Attrib 

Cost %

3 Seg 1 Postmasters EAS 24 & Above 0002 100F 0 54,075 54,075 0.00%

6 Seg 2 Mail Processing Supervision 0004 IND 35 934,472 56,649 991,121 94.28%

29 Seg 2 Joint Supervision Clerks & Carriers 0678 IND 694 466,972 209,401 676,373 69.04%

37 Seg 3 Mail Processing 0035 ConsElas 13,786,809 835,773 14,622,582 94.28%

43 Seg 3 Training 0470 IND 473 109,019 19,482 128,501 84.84%

57 Seg 6 In-Office Direct Labor 0043 PAPF 3,357,514 510,588 3,868,102 86.80%

65 Seg 7 Product Specific Deliv Activities 0048 100S 5,311 0 5,311 100.00%

143 Seg 14 International 0146 100A 815,558 0 815,558 100.00%

267 Seg 21 C/S 3 Training Other 473 Key 14,997,575 2,680,144 17,677,719 84.84%

273 Seg 21 Supervision Clerks & Carriers (3,6,7) 694 Key 23,523,699 10,548,565 34,072,264 69.04%

Table A-1. FY 2007 Input Data for a Sample of Cost Components (Case 1 Sheet)

Keys1 

Row

Segment Component CRA Key 

Number

# of 

Addends Addend 1 Addend 2 Addend 3 Addend 4 Addend 5 Addend 6 Addend 7

3 Seg 21 CS 3 Training Other 473 7 3:35 3:227 3:40 3:41 3:421 3:423 3:66

9 Seg 21 Supervision Clerks & Carriers (3,6,7) 694 10 21:473 3:470 6:43 6:44 6:604 7:54 7:47

" Addend 8 Addend 9 Addend 10

" 7:48 7:50 7:53

Table A-2. Sample of Distribution Key Definitions (Keys1 Sheet)
15

denoted by “100S.” Row 8 shows another independent component (0146) which is categorized 

as 100 percent attributable, denoted by “100A.”

The second row of Table A-1 shows a dependent component (Mail Processing Supervision, 

component 0004) whose distribution of costs is based on component number 0035, and its 

category is denoted by “IND 35.” From the fourth row of the table, we see that component 0035 

is actually Mail Processing, and that Mail Processing Supervision has the same percent 

attributable value as Mail Processing (as it should). The fifth row of Table A-1 shows another 

dependent component (Cost Segment 3 Training, number 470), which is distributed according to 

the distribution key 473. The second row of Table A-2 shows that distribution key 473 is the 

sum of seven independent cost components.20 Finally, the third row of Table A-1 shows a 

dependent component (Joint Supervision Clerks & Carriers, number 678) that uses component 

694 as its distribution key. The last row of Table A-2 shows that distribution key 694 is the sum 

of distribution key 473 and nine other components. Eight of these nine components are 

independent, but component number 470 (in Segment 3) is a dependent component itself. This 

means that key 694 cannot be calculated until another dependent component has been calculated. 

For this reason, a multi-pass algorithm must be used to ensure that all components and keys have 

been calculated.

Computational Algorithm

The first step in the process for calculating inframarginal costs is to loop through each cost 

component in rows 2-263 of the Case1 sheet. For each component, the Comp Category string is 

used to determine the proper method of determining the split of Other costs into inframarginal 

and fixed components. This will generally not be possible during the initial pass for the 

dependent components, because their inframarginal costs cannot be calculated until the 

inframarginal and fixed costs associated with each cost component addend included in their 

distribution keys have been calculated. The next step in the process is to cycle through the 

distribution keys in rows 265-279 of the Case1 sheet, checking to see if all addend components

have already been calculated for a particular key. If the inframarginal/fixed cost calculations 

have been completed for a component or key, the component/ key itself is marked as complete; if 

not, the component/key is marked as incomplete. 

The steps in the above paragraph are repeated until the inframarginal and fixed costs for all 

distribution keys and cost components have been calculated.

Introduction to the Excel VBA Programming Language

VBA contains the normal high-level programming language constructs, such as typed variables, 

for and do loops, file reading/writing, subroutines and functions, if statements, case and switch 

statements, dimensioned arrays, and so on. It also includes the standard string and arithmetic 

functions found in most Basic languages. However, VBA also uses object-oriented 

programming language statements to emulate virtually all of the functions provided by Excel. 

Excel objects include such items as workbooks, worksheets, cells, ranges of cells, and menus. 

 

20 As noted earlier in footnote 15, all distribution keys are based on the worksheet DK Addends, which is part of the 

Excel workbook FY07.cntl.xls submitted as a library reference in Docket ACR2007. The keys for other years are 

based on the corresponding worksheet submitted in other ACR Dockets.
16

These objects also have a variety of associated “properties,” such as fonts, names, and sizes, 

which can be changed by the programmer. Excel objects also have associated “methods,” which 

are used to change objects or properties. For example, the worksheet object has a method called 

“open”, which is used to open a worksheet. 

The Excel/VBA programs described below make extensive use of the Excel object model. A 

good reference book for learning how to use Excel VBA object-oriented programming features is 

Learn Microsoft Excel VBA 2000 Programming, by Julitta Korol (Wordware Publishing, Inc., 

2000). The Help files provided with the Excel VBA editor are also very useful; for example, 

information on using any VBA language item can be obtained by placing the cursor over the 

item and pressing the F1 function key.

In the VBA language, lines (or sections of lines) that follow the single-quote character (‘) are 

comments only, and have no effect on the program. Also, VBA uses the underscore character 

(_) to flag a line in the program that will be continued on the next line. Note also that the 

“Immediate Window,” which is helpful with program debugging, can be opened using a VBA 

View menu command, and that the “Debug.Print” statement is used in VBA programs to print 

useful information to this window during the execution of an Excel/VBA program.

VBA Program FindIM Description and Listing

This section summarizes the operations performed in various sections of the FindIM VBA 

program code. These remarks are keyed to the line numbers shown on the four-page program 

listing shown below in Table A-3. 

Lines 13-16: The four worksheets used by the program are defined for use by VBA. For 

example, OutSht is the Case1 sheet.

Lines 18-40: Various parameters are read from the UserDat sheet, including the desired year 

JYear and the number of distribution keys NKeys. The starting worksheet column for that year 

is calculated based on the value of JYear. At lines 22-24, three Excel-style cell ranges are 

defined using the VBA function ExcelCol() defined at lines 191-202 (see the description for this 

function below). The columns reserved for inframarginal and fixed costs are cleared, as are the 

data columns for the NKeys distribution keys defined on the Keys1 sheet.

Lines 43-50: A row mapping array CompRow(CompNo) is defined for the Case1 sheet. For 

example, the value of CompRow for CompNo 0004 is row number 6. The array 

CompCat(CompNo) stores the Comp Category for component number CompNo for later 

retrieval. The array CompSt(CompNo) sets the completion status for each component CompNo 

to zero.

Lines 56-188: Major loop to allow calculation of IM/fixed costs for more than one year during a 

single run of the program. Our Task 2 results were calculated one year at a time, so this feature 

was not used.

Lines 58-61: Starting point for each loop through the components. The variable NBad is the 

number of components that could not be calculated during the current pass, and is initialized to

zero.

Lines 62-127: I1 loop through each non-key component. Details provided below.
17

Lines 63-79: The loop starts with I1 = -5 to allow early calculation of the six single-component 

distribution keys (CompNo’s 428, 74, 35, 57, 40, 83, and 54). The Case1 row number for each 

of these keys is set to a new row variable I. Starting with I1 = 1, I = I1.

Lines 80-126: Calculation of inframarginal and fixed costs for each component row I. 

At lines 80-83, the IM variable is initialized to zero; the CompNo variable is set to the 

value in column C of row I; and the variable Cat is set equal to the value in the Comp Category

column D. At rows 84-86, a check is made to see whether the Cat variable for row I is blank or 

has already been calculated (CompSt(CompNo) = 1); if so, program control is transferred to the 

end of the I1 loop at row 128.

At lines 90-113, an If/ElseIf/Else test performs various actions according to the value of 

Cat and Cat3 (the first three characters of the variable Cat). If Cat = ConsElas, the component is 

a constant elasticity component. The code at lines 88-89 sets the variable IM = 1 and 

CompSt(CompNo) = 1, then passes program control to line 115.

If Cat3 = IND, the component is dependent and is calculated using the distribution key 

method, where the key number is specified by the last two or three characters of the variable Cat

(set to IndNo at line 91). In this case, the Other costs of the component will be part inframarginal and part fixed, with the percentage of the two categories set equal to the corresponding 

percentages in the distribution key IndNo. At row 92, a check is made to see if CompSt(IndNo) 

= 1, indicating that calculation of the distribution key has been previously completed. If not, 

program control passes to line 105, where an error message (Debug.Print etc.) is printed and 

program control is passed to the end of the I1 loop. If true, the VBA code at lines 93-102

determines the inframarginal and fixed portions of Other costs based on the corresponding ratios 

from the key itself. At rows 93-98, the variable F is calculated as the ratio of inframarginal to 

Other costs for the distribution key. At rows 100-101, the ratio F is used to split the Other costs 

of the current component into inframarginal and fixed portions.

If the component is neither a constant elasticity or dependent type, then it will either be a 

100 percent fixed component, indicated by “100F” in the Comp Category column, or a product 

specific fixed component, indicated by “100S.” In this case, IM is set to 2, CompSt(CompNo) is 

set to 1 at line 112, and program control passes to line 115.

At lines 115-118, the inframarginal and fixed cost cells of row I are initialized to 0. Then 

the Other costs of row I are copied to the inframarginal cost column of row I if the previously 

indicator variable IM = 1, and to the fixed cost column of row I if IM = 2.

At lines 122-125, a check is made to determine whether the row I allocation of Other 

costs to inframarginal and fixed categories have been calculated correctly. If not, the program 

stops so that the error can be traced and corrected.

Lines 131-179: The program code used to calculate the NKeys = 15 multiple-input distribution 

keys needed to distribute various dependent cost components are shown at rows 265-279 of the 

Case1 sheet. The definitions of these keys are shown at rows 1-15 of the Keys1 sheet (called 

KeySht in the VBA program). For each key, Column C of KeySht shows its component number; 

column D shows its number of addend components; and the remaining columns define each 

addend in Segment#:Component# format. Calculation of these distribution keys must be done in 

several stages, since many of them depend on the results of calculating earlier distribution keys.
18

At lines 138-141, the SumInd(I1,K) array is initialized to zeroes, where I1 is the Keys1 

row number (1-15), and K = 1 thru 6, corresponding to the column order for the Case1 sheet 

(attributable costs, other costs, total costs, attributable cost %, inframarginal costs, and fixed 

costs). NCom is the number of input components for the key.

At lines 142-164, the SumInd array is summed over the corresponding NCom input 

components as defined in the Keys1 sheet. At lines 146-151, the status of each input component

is checked to see whether it has been previously calculated. If it has not, an error message is 

printed, the variable NBad is incremented by one and program control passes to the end of the I1 

For loop at line 179. If it has, program execution continues. At line 153, a check is made to see 

if the key = 525 and the dependent column = 74, 79, or 194. For this particular situation, the 

USPS treatment is nonstandard. Components 74, 79, and 194 (three of the so-called PESSA 

components) are considered fixed with respect to volume only for purposes of calculating this 

particular distribution key. So at lines 155-157, only the other, total, and fixed columns of 

SumInd are incremented instead of all six column categories. In all other situations, all six 

columns are incremented in the usual manner at rows 160-163.

At lines 165-170, the completed SumInd results are stored in the matching six columns of 

the particular distribution key row (with component number IndNo) and the CompSt(IndNo) 

indicator is set to one, indicated completion of the key’s calculation.

At lines 171-177, a check is made for the calculated dependent component to see if the

absolute value of the difference (Dif) between its Other costs and inframarginal costs is within 

one unit of its fixed costs. If not, its fixed costs are set equal to Dif. 

Lines 183-189: A check is made to see if NBad is greater than zero, indicating that some 

components have not yet been calculated. If so, program control loops back to line 58, labeled 

“start0.” If not, the program run is finished and execution stops at line 189.

Lines 191-202: This is a VBA function named ExcelCol that has an integer argument 

representing a column number. It returns the Excel-style column designator (e.g., Column BV) 

at line 200.
19

Table A-3. VBA Program FindIM Listing

1 Public Sub FindIM()

2 Dim InSht As Worksheet, MainWkBk As String, SumInd(100, 9)

3 Dim I As Integer, J As Integer, K As Integer, II, temp

4 Dim UserSht As Worksheet, KeySht As Worksheet, OutSht As Worksheet

5 Dim StartCol, SkipCol, CatCol, NYear, MaxRow, CompNo, IndNo

6 Dim Cat, CompCat(2000), IM, JOCol, JIMCol, Cat3, NCom, I2

7 Dim CompSt(2000), CompRow(2000), F, I1, NKeys, CompStr, J1

8 Dim NBad, NPass, rConstants As Range, JYear, Dif, tempa, tempb

9

10 'Set up Userdata and Component sheets, working directory

11 ActiveWindow.Zoom = 75

12 MainWkBk = ThisWorkbook.Name

13 Set InSht = Workbooks(MainWkBk).Worksheets("ComponentsIn")

14 Set OutSht = Workbooks(MainWkBk).Worksheets("Case1")

15 Set UserSht = Workbooks(MainWkBk).Worksheets("Userdata")

16 Set KeySht = Workbooks(MainWkBk).Worksheets("Keys1")

17

18 'Get parameters, clear IM/fixed columns, clear Key rows 265-279

19 JYear = UserSht.Cells(9, 2) - 2006

20 SkipCol = Int(UserSht.Cells(4, 2))

21 JOCol = 4 + (JYear - 1) * SkipCol

22 temp = ExcelCol(JOCol + 5) + "2:" + ExcelCol(JOCol + 6) + "310"

23 tempa = ExcelCol(JOCol + 1) + "265:" + ExcelCol(JOCol + 3) + "279"

24 tempb = ExcelCol(JOCol + 5) + "2:" + ExcelCol(JOCol + 5) + "2"

25 Stop

26 OutSht.Range(tempa) = 0

27 OutSht.Range(tempb) = 0

28 Set rConstants = OutSht.Range(temp).SpecialCells(xlCellTypeConstants)

29 rConstants.ClearContents

30 OutSht.Range(temp).NumberFormat = "#,##0"

31 OutSht.Rows(303).NumberFormat = "0.00%"

32 OutSht.Application.ActiveWindow.Zoom = 75

33

34 'Get additional parameters

35 StartCol = Int(UserSht.Cells(3, 2))

36 CatCol = Int(UserSht.Cells(5, 2))

37 NYear = Int(UserSht.Cells(6, 2))

38 MaxRow = Int(UserSht.Cells(7, 2))

39 NKeys = UserSht.Cells(8, 2)

40 NPass = 0

41

42 Create component# to row index, get component category & clear status

43 For I = 2 To MaxRow

44 CompNo = Int(OutSht.Cells(I, 3))

45 Cat = OutSht.Cells(I, CatCol)

46 If CompNo <> "" And Cat <> "" Then

47 CompRow(CompNo) = I

48 CompCat(CompNo) = Cat

49 CompSt(CompNo) = 0

50 End If

51 Next I
20

52 'Stop

53 

54 'Separate IM and fixed costs by component

55

56 For J = JYear To JYear

57 'Begin next pass through Case1 rows

58 start0:

59 NPass = NPass + 1

60 NBad = 0

61 For I1 = -5 To MaxRow - Nkeys

62 If I1 = -5 Then 'start with single-component dist keys

63 I = 11 'Comp 428

64 ElseIf I1 = -4 Then

65 I = 81 'Comp 74

66 ElseIf I1 = -3 Then

67 I = 37 'Comp 35

68 ElseIf I1 = -2 Then

69 I = 72 'Comp 57

70 ElseIf I1 = -1 Then

71 I = 38 'Comp 40

72 ElseIf I1 = 0 Then

73 I = 67 'Comp 83

74 ElseIf I1 = 1 Then

75 I = 63 'Comp 54

76 Else

77 I = I1

78 End If

79 

80 IM = 0

81 CompNo = Int(OutSht.Cells(I, 3))

82 Cat = OutSht.Cells(I, CatCol)

83 Cat3 = Left(Cat, 3)

84 If Cat = "" Or CompSt(CompNo) = 1 Then

85 GoTo nexti2:

86 End If

87 If Cat = "ConsElas" Then 'Other costs are IM

88 IM = 1

89 CompSt(CompNo) = 1

90 ElseIf Cat3 = "IND" Then 'Other costs are part IM, part fixed

91 IndNo = Int(Right(Cat, Len(Cat) - 4))

92 If CompSt(IndNo) = 1 Then

93 II = CompRow(IndNo)

94 If OutSht.Cells(II, JOCol + 6) = 0 Then

95 F = 1

96 Else

97 F = OutSht.Cells(II, JOCol + 5) / OutSht.Cells(II, JOCol + 2)

98 End If

99 ' Stop

100 OutSht.Cells(I, JOCol + 5) = F * OutSht.Cells(I, JOCol + 2)

101 OutSht.Cells(I, JOCol + 6) = (1 - F) * OutSht.Cells(I, JOCol + 2)

102 CompSt(CompNo) = 1

103 GoTo nexti:

104 Else
21

105 temp = Format(I1) + "(" + Format(CompNo) + ")" + ", missing comp "

106 Debug.Print Format(NPass) + ", Can't calc line " + temp + Format(IndNo)

107 NBad = NBad + 1

108 GoTo nexti2:

109 End If

110 Else

111 IM = 2 'Other costs are fixed

112 CompSt(CompNo) = 1

113 End If

114

115 OutSht.Cells(I, JOCol + 5) = 0

116 OutSht.Cells(I, JOCol + 6) = 0

117 JIMCol = JOCol + 4 + IM

118 OutSht.Cells(I, JIMCol) = OutSht.Cells(I, JOCol + 2)

119 ' Stop

120 nexti:

121 'Check whether IM + fixed = Other; if not, stop

122 Dif = OutSht.Cells(I, JOCol + 2) - OutSht.Cells(I, JOCol + 5)

123 If Abs(Dif - OutSht.Cells(I, JOCol + 6)) > 1 Then

124 Stop

125 End If

126 nexti2:

127 Next I1

128 nextii:

129 ' Stop

130

131 'Calculate indirect costs

132 For I1 = 1 To NKeys

133 IndNo = KeySht.Cells(I1, 3) 'Component number for this key

134 If CompSt(IndNo) = 1 Then

135 GoTo nexti1:

136 End If

137 I = CompRow(IndNo)

138 For K = 1 To 6 'Initialize SumInd array to zero

139 SumInd(I1, K) = 0

140 Next K

141 NCom = KeySht.Cells(I1, 4)

142 For J1 = 1 To NCom 'Sum up NCom input keys

143 CompStr = KeySht.Cells(I1, J1 + 4)

144 CompNo = Int(Mid(CompStr, InStr(CompStr, ":") + 1))

145 I2 = CompRow(CompNo)

146 If CompSt(CompNo) = 0 Then 'Can't calculate this component

147 temp = " " + Format(I) + "(" + Format(IndNo) + ")" + ", Can't calc line "

148 Debug.Print Format(NPass) + temp + ", missing compi " + Format(CompNo)

149 NBad = NBad + 1

150 ' Stop

151 GoTo nexti1:

152 End If

153 If IndNo = 525 And (CompNo = 74 Or CompNo = 79 Or CompNo = 194) Then

154 ' Stop 'Check calculation of key 525, update partial sum

155 SumInd(I1, 3) = SumInd(I1, 3) + OutSht.Cells(I2, JOCol + 3)

156 SumInd(I1, 2) = SumInd(I1, 2) + OutSht.Cells(I2, JOCol + 2)

157 SumInd(I1, 6) = SumInd(I1, 6) + OutSht.Cells(I2, JOCol + 6)
22

158 ' Stop

159 Else

160 For K = 1 To 6 'Update partial sum for key

161 SumInd(I1, K) = SumInd(I1, K) + OutSht.Cells(I2, JOCol + K)

162 Next K

163 End If

164 Next J1

165 For K = 1 To 6 'Write key results into OutSht row (from 265 to 279)

166 OutSht.Cells(I, JOCol + K) = SumInd(I1, K)

167 Next K

168 OutSht.Cells(I, JOCol + 4) = OutSht.Cells(I, JOCol + 1) _

169 / OutSht.Cells(I, JOCol + 3) 'Check attrib cost %

170 CompSt(IndNo) = 1

171 Dif = OutSht.Cells(I, JOCol + 2) - OutSht.Cells(I, JOCol + 5)

172 ' Stop

173 If Abs(Dif - OutSht.Cells(I, JOCol + 6)) > 1 Then

174 ' Stop

175 OutSht.Cells(I, JOCol + 6) = Dif 'Key fixed cost = Other - IM

176 ' Stop

177 End If

178 nexti1:

179 Next I1

180

181 Debug.Print "NPass=" + Format(NPass) + ", NBad=" + Format(NBad)

182 ' Stop

183 If NBad = 0 Then GoTo nextj: 'Check for any incomplete components or keys

184 Debug.Print

185 GoTo start0: 'If so, go to beginning of next pass

186 nextj:

187 Stop

188 Next J

189 End Sub

190

191 Public Function ExcelCol(ColNo)

192 ' Convert numeric column number to Excel format

193 Dim E1 As String, Colm26 As Integer, Cold26 As Integer

194 If ColNo <= 26 Then

195 ExcelCol = Chr(Asc("A") + ColNo - 1)

196 ElseIf ColNo > 26 Then

197 Colm26 = ColNo - 26 - 1

198 Cold26 = Int(Colm26 / 26)

199 E1 = Chr(Asc("A") + Cold26)

200 ExcelCol = E1 + Chr(Asc("A") + (Colm26) Mod 26)

201 End If

202 End Function