Chapter 5, Problem 69P

Methods of Cost Analysis: Account Analysis, Simple and Multiple Regression Using a Spreadsheet (Appendix A)

Caiman Distribution Partners is the Brazilian distribution company of a U.S. consumer products firm. Inflation in Brazil has made bidding and budgeting difficult for marketing managers trying to penetrate some of the country’s rural regions. The company expects to distribute 450,000 cases of products in Brazil next month. The controller has classified operating costs (excluding costs of the distributed product) as follows:

Although overhead costs were related to revenues throughout the company, the experience in Brazil suggested to the managers that they should incorporate information from a published index of Brazilian prices in the distribution sector to forecast overhead in a manner more likely to capture the economics of the business.

Following instructions from the corporate offices, the controller’s office in Brazil collected the following information for monthly operations from last year:

These data are considered representative for both past and future operations in Brazil.

Required

1. a.      Prepare an estimate of operating costs assuming that 450,000 cases will be shipped next month based on the controller’s analysis of accounts.
2. b.      Use the high-low method to prepare an estimate of operating costs assuming that 450,000 cases will be shipped next month.
3. c.       Prepare an estimate of operating costs assuming that 450,000 cases will be shipped next month by using the results of a simple regression of operating costs on cases shipped.
4. d.      Prepare an estimate of operating costs assuming that 450,000 cases will be shipped next month by using the results of a multiple regression of operating costs on cases shipped and the price level. Assume a price level of 145 for next month.
5. e.       Make a recommendation to the managers about the most appropriate estimate given the circumstances.

To determine

Prepare an estimate of operating costs assuming that 450,000 cases will be shipped next month based on the controller’s analysis of accounts.

Explanation of Solution

Operating cost:

Operating cost is the total cost of the production. It includes direct and indirect cost of the production. Operating profit is calculated by deducting the operating cost from the revenue of the business.

Calculate the estimated overhead cost:

$\begin{array}{c}\text{Estimated overhead = Fixed overhead + Variablem overhead}\\ \text{= Fixed overhead +}\text{}(\text{Variable cost per unit }\times \text{ Number of units)}\\ \text{= }\$3,100,000+\text{ (}\$7.00\text{ (1)}´\times 450,000)\\ =\text{ \$6,250,000}\end{array}$

Thus, the estimated overhead cost is $6,250,000.

Working note 1:

Calculate the variable cost per unit:

$\begin{array}{c}\text{Variable cost per unit = }\frac{\text{Total variable cost}}{\text{Unit produced}}\\ =\frac{\$3,150,000\text{ (2)}}{450,000}\\ =\text{ \$7 per unit}\end{array}$

Working note 2:

Prepare a schedule to show the allocation of fixed and variable cost:

Particulars	Operating cost (a)	Fixed cost (b)	Variable cost     $\text{c = }\left(\text{a}-\text{b}\right)$
Supplies	$350,000	$0	$350,000
Supervision	$215,000	$150,000	$65,000
Truck expenses	$1,200,000	$190,000	$1,010,000
Building lease	$855,000	$550,000	$305,000
Utilities	$215,000	$125,000	$90,000
Warehouse labor	$860,000	$140,000	$720,000
Equipment lease	$760,000	$600,000	$160,000
Data processing equipment	$945,000	$945,000	$0
Other	$850,000	$400,000	$450,000
Total cost	$6,250,000	$3,100,000	$3,150,000

Table: (1)

To determine

Use the high-low method to prepare an estimate of operating costs assuming that 450,000 cases will be shipped next month.

Explanation of Solution

High-low cost method:

High-low cost method helps in separating the fixed and variable cost from the total cost. It is calculated by comparing the highest and lowest level of activities and the cost of these activities.

Show the cost equation of fixed and variable cost with the overhead cost:

$\begin{array}{c}\text{Operating cost = Fixed cost + Variable cost}\\ \text{= \$6,499,442 (1) + (\$7}\text{.622(2) }\times \text{ Number of cases)}\end{array}$

Thus, the cost equation is $\text{Operating cost = \$6,499,442 + (\$7}\text{.622 }\times \text{ Number of cases)}$.

Working note 1:

Calculate the fixed cost:

$\begin{array}{c}\text{Fixed cost = Total cost - Variable cost}\\ \text{= }\$6,362,255\text{ - \$3,292,713 (3)}\\ =\text{ \$6,499,442}\end{array}$

Calculate the highest and lowest activity:

Particular	Cases	Cost
Highest activity	432,000	$6,362,255
Lowest activity	345,000	$5,699,139

Table: (2)

Working note 2:

Calculate the variable cost (unit) with the help of high-low cost method:

$\begin{array}{c}\text{Variable cost = }\frac{\mathrm{Co}\text{st of highest activity }-\text{ Cost of lowest activity}}{\text{Highest activity units }-\text{ Lowest activity units}}\\ =\frac{\$6,362,255–\$5,699,139}{432,000–345,000}\\ =\text{ \$7}\text{.622 per case}\end{array}$

Working note 3:

Calculate the variable cost ($) with the help of high-low cost method:

$\begin{array}{c}\text{Variable cost (\$) = Number of cases}\times \text{ Rate per unit}\\ \text{= 432,000 }\times \text{ \$7}\text{.622(2)}\\ \text{= \$}3,292,713\end{array}$

To determine

Prepare an estimate of operating costs assuming that 450,000 cases will be shipped next month by using the results of a simple regression of operating costs on cases shipped.

Explanation of Solution

Regression analysis:

Regression analysis is used to show the relationship between the cost and the activity. It is used to estimate the cost at various level of activity.

The most important step in the calculation of regression analysis is to establish a logical relationship between the cost and the activity. The activity (independent variable) is placed on the right-hand side and the cost (dependent variable) is placed on the left-hand side of the graph.

Calculate the regression analysis to compute the cost equation:

Regression Statistics
Multiple R	0.980345
R Square	0.961076
Adjusted R Square	0.957184
Standard Error	39850.14
Observations	12
ANOVA
	Df	SS	MS	F	Significance F
Regression	1	3.92E+11	3.92E+11	246.9132	2.24E-08
Residual	10	1.59E+10	1.59E+09
Total	11	4.08E+11
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	3411468	166203	20.52591	1.66E-09	3041145	3781791	3041145	3781791
X Variable 1	6.707649	0.426873	15.71347	2.24E-08	5.756518	7.658781	5.756518	7.658781

Table: (3)

The regression analysis of the company provides the following details:

$\begin{array}{c}\text{Intercept of x}\text{= }3,411,468\\ \text{x}\text{variable}\text{1}\text{=}6.707649\\ \text{R}\text{square}\text{=}0.961076\end{array}$

Calculate the cost equation:

Put the values in the cost equation:

$\begin{array}{c}y=\text{ f + vx}\\ \text{Operating cost = \$3,411,468 + (\$6}\text{.71 }\times \text{ number of cases)}\end{array}$

Thus, the operating cost equation is:

$\text{Operating cost = \$3,411,468 + (\$6}\text{.71 }\times \text{ number of cases)}$.

To determine

Prepare an estimate of operating costs assuming that 450,000 cases will be shipped next month by using the results of multiple regression of operating costs on cases shipped and the price level.

Explanation of Solution

Regression analysis:

Regression analysis is used to show the relationship between the cost and the activity. It is used to estimate the cost at various level of activity.

The most important step in the calculation of regression analysis is to establish a logical relationship between the cost and the activity. The activity (independent variable) is placed on the right-hand side, and the cost (dependent variable) is placed on the left-hand side of the graph.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.990475
R Square	0.981042
Adjusted R Square	0.976829
Standard Error	29315.83
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	2	4E+11	2E+11	232.8623	1.78E-08
Residual	9	7.73E+09	8.59E+08
Total	11	4.08E+11
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	3176995	144048.2	22.05509	3.83E-09	2851136	3502855	2851136	3502855
X Variable 1	8857.728	2877.157	3.078639	0.013169	2349.147	15366.31	2349.147	15366.31
X Variable 2	4.418915	0.807028	5.475543	0.000392	2.593292	6.244539	2.593292	6.244539

Table: (4)

The regression analysis of the company provides the following details:

$\begin{array}{c}\text{Intercept of x}\text{= }3,176,995\\ \text{x}\text{variable}\text{1}\text{=}8857.728\\ \text{x}\text{variable}\text{ 2 = }4.418915\\ \text{R}\text{square}\text{=}\begin{array}{r}\hfill 0.981042\end{array}\end{array}$

Calculate the cost equation:

Put the values in the cost equation:

$\begin{array}{c}y=\text{ f + vx + uz}\\ \text{Operating cost = \$}3,176,995\text{ + (\$}8857.728\times \text{ Price level) + (}4.418915\times \text{ Cases)}\end{array}$

Thus, the operating cost equation is $\text{Operating cost = \$}3,176,995\text{ + (\$}8857.728\times \text{ Price level) + (}4.418915\times \text{ Cases)}$.

To determine

Make a recommendation to the managers about the most appropriate estimate given the circumstances.

Explanation of Solution

Recommendation to the manager:

The multiple regressions seem to be more useful as per the adjusted R². But the inclusion of price index may not be dependent on the cost of the product instead on the growth of the business. So correlating the price with the cost will not be very useful.

Simple regression is easy to compute, and it shows the clear relationship between the cost and the revenue so it should be considered for the cost estimation.

Thus, management should consider the simple regression for the cost estimation.