EXCEL: FORECASTING SALES, COST AND PROFIT

EXCEL: FORECASTING SALES, COST, AND PROFIT

Amiel M. Cadeliña 

At Mapua University we have Engineering Economics and in Sacred Heart College they have Business Analytics and both tackle company’s supply chain. Both schools give exercises where Excel records are the source data. In this example, we tackle a record in Excel of a company’s sales results. In this pandemic time, many small and medium enterprises are facing difficulty and some of them closed their operation. The objective of this post is to help these small and medium enterprises do forecasting of their Sales, Cost, and Profit using Excel. Learning it may help them fix what’s gone wrong.

In the Figure below, fictitious data on Consumer and Non-Consumer Electronics is given. The figure shows the actual revenue and the actual direct costs of supporting each product line during each month from January 2019 through July 2021, in row 3 through row 33. The worksheet gets the profit figures simply by subtracting the costs from the revenues.

I’m using exponential smoothing for costs, and regression for revenues, simply to illustrate the methods.

 

Steps in doing the forecast:

Compute exponential smoothing.

1.      In cell P4 type =C3

2.   In cell P5 type =$P$1*C4+$P$2*P4

3.   Drag the P5 cell downward up cell P33

4.     In cell P35 type = $P$1*C33+$P$2*P33

5.      Forecast Revenue of Consumer electronics, in cell A35 type Forecast for August 2021.

6.    Then in cell B35, type =TREND(B3:B33,C3:C33,C35)

7.     Then in cell C35, type your result in number 4 by typing =P35

8.      Then in cell D35, type =B35-C35

Result

 

You now made a Revenue, Cost, and Profit Forecast. Congratulations!