- Subject:
- Manufacturing
- Material Type:
- Module
- Provider:
- Ohio Open Ed Collaborative
- Tags:

- Language:
- English
- Media Formats:
- eBook

# Excel Workbooks

# Process Analysis and Make or Buy

# Break Even Analysis

**Break-even Analysis for Processes**

**Break Even (Power Point)**

**Break Even (Excel)**

Manufacturing companies commonly use a method called *Break-even Analysis* to determine the minimum production quantity required to be profitable. In its basic form, there are two variables to consider for determining an optimal quantity Q of production: Total Cost (TC) of producing Q units and the Total Revenue (TR) generated by producing and selling Q units. The difference between the total revenue and total cost is called *profit* (P) and companies decide the volume of production based on the profitability of their processes.

Total Cost includes all cost related to production of a product at a given level of quantity (output). Total Cost consists of Fixed Costs (FC) and Variable Costs (VC). Fixed costs remain constant regardless of the quantity produced. Examples of fixed costs include monthly rent payments for storage, insurance premiums, and utilities. Variable costs vary in direct proportion with the quantity being produced. If production quantity increases the variable cost increases proportionally and vice versa. Examples of variable costs include packaging, raw materials, and labor.

For a given quantity Q of production:

TC = FC +VC

And VC = c x Q where c is the variable cost per unit produced.

Since profit is the difference between TC and TR for a given quantity Q of production,

We can indicate P as

P= TR - TC

The quantity where TC is equal to TR (thus P is zero) is called Break-Even Point (BEP).

The figures below show an example of the relationships among TC, TR and BEP.

**Example:**

The president of Jergens Inc. in Ohio is considering adding a new line of toggle clamp production which will require a special equipment to lease for $6,000 a year. Variable cost of producing one clamp is $2.00 and each toggle clamp will be sold for $7.00. How many toggle clamp should be produced and sold to make the leasing of equipment worthwhile?

This question can be answered by determining the break-even quantity where total revenue will be equal to total cost.

Total revenue is calculated as the 7.00x Q

Total cost is $6,000(total fixed cost) + Q x 2.00 (total variable cost)

Thus TC= TR implies $7.00x Q = $6,000+ Q x $2.00

Q (at break-event point is) = $6,000/$5 = 1,200 clamps per year

If the company sells less than 1,200 clamps then they will lose money and if they produce and sell more than 1,200 they make profits.

# Forecasting

**Forecasting Demand **

**Forecasting (Excel)**

Manufacturing companies determine how much to produce based on the prediction of future demand. Especially in a make-to-stock environment where companies rely on demand planners to estimate anticipated demand, the accuracy of the prediction becomes very important. The technique that is used to predict demand based on the analysis of historical data and the trend is called *Forecasting*. There are variety of different forecasting techniques available, but in general they can be classified as either qualitative or quantitative. Qualitative techniques are based on factors that cannot be directly measured, such as judgements, and expert opinions. Quantitative forecasting techniques use hard data from the company’s past performance to predict the future demand. The common assumption with the quantitative techniques is that what has happened in the past will repeat, and the economic environment that existed in the past will continue in the future. The most commonly used quantitative forecasting techniques are called *Time-series forecasts. *Time-series forecasts identify the patterns in the historical data. The patterns can be described as a trends, cycles, and seasonal variations. There can also be irregular variations in the data. The following graphs show each instance.

*Trend: *A continuous increase or decrease in the data. A visual observation of a trend in data help managers plan on investing in new equipment, or increasing or decreasing production capacity.

*Irregular variation:* It is also called noise in the data. It is an unusual sudden increase or decrease in data. It can be due to error in recording, or an event that is not a typical behavior. During analysis, planners usually remove these anomalies.

*Cycles:* Over a period of a year, waves of increase or decrease that regularly repeat. Typically economic, financial, and agricultural conditions follow this pattern.

*Seasonal variations: *Short term and regular variations in data. For example, movie ticket sales on Fridays, or sales of graduation cards in May are examples of demand that follow seasonal patterns.

Once the underlying behavior of data is determined, the next step is for planners to choose the proper forecasting technique that can be used for that particular pattern of data.

When the data contains only trend then simple time series forecasting techniques use averages and recent values of the historical data. For example, the *n-period* *Moving Average *forecasting technique uses the *n* most recent actual data and averages to calculate the forecast for time period *t*.

**Example:** To calculate the forecasting value for the sixth day for the number of ice creams ordered, we can use the historical data for the ice cream orders.

Days Ice Cream Orders

1 44

2 38

3 41

4 40

5 46

If the planner is interested in the last three days’ data, then the *3 period moving average* technique would calculate the forecast for number of ice cream orders as (41+40+46)/3= 42.33

Thus the sixth day ice cream orders is expected to be 42.33, or approximately 43 orders. If the actual number of orders in day six turns out to be 36, then the moving average forecast for day seven will be (36+46+40)/3= 40.66, or approximately 41 orders. In the moving average method, as the new actual data becomes available, it is added to the calculation of the forecast, and the oldest data is dropped from the calculation. In this particular example, as soon as we know what the actual demand for the sixth period is, which is 36, it is added to the calculation for forecasting the seventh period, and the 41 which is the oldest three period value is dropped from the calculation.

A version of the moving average method is called *Weighted moving average*. It is similar to the moving average technique, except it assigns different weights to data in different time periods. Typically if the planners wants the data in most recent periods to have more impact to the forecast, then they can assign more weight to the most recent values.

**Example:**

In the previous example, if the weights are assigned as follow:

Days Ice Cream Orders Weights

1 44

2 38

3 41 0.10

4 40 0.30

5 46 0.60

Then the sixth day forecast can be calculated as: 0.60(46)+0.30(40)+0.10(41) = 43.7 or approximately 44 ice cream orders. Because the most recent period has the highest weight, its value has more impact to the sixth period forecast.

Several other forecasting techniques are available to companies, such as Exponential smoothing and Regression analysis. When deciding which forecasting technique to use, planners need to evaluate the performance of a given technique by calculating its forecast error and the cost of collecting and analyzing data.