How to use least squares regression in forecasting

During Service and Sales subject we were working on forecasting. One of the techniques used was least squares regression. I have prepared a video explaining how to use  excel spreadsheet and how to interpret all different parameters.

Download from here the excel spreadsheet

Excel functions translations

You can find below a useful translation of every Excel function from Spanish to German:

Spanish	German
–	-
ABRIR.ARCHIVO	DATEI.ÖFFNEN
ABRIRA	DÖFFNEN
ABS	ABS
ACOS	ARCCOS
ACOSH	ARCCOSHYP
AGREGAR.BARRA	MENÜLEISTE.EINFÜGEN
AGREGAR.BARRA.HERRAMIENTAS	SYMBOLLEISTE.EINFÜGEN
AGREGAR.COMANDO	BEFEHL.EINFÜGEN
AGREGAR.MENU	MENÜ.EINFÜGEN
AGRUPAR	GRUPPIEREN
AHORA	JETZT
ALEATORIO	ZUFALLSZAHL
AÑO	JAHR
APL.TITULO	ANW.TITEL
ARCHIVOS	DATEIEN
AREAS	BEREICHE
ARGUMENTO	ARGUMENT
ASC	ASC
ASENO	ARCSIN
ASENOH	ARCSINHYP
ATAN	ARCTAN
ATAN2	ARCTAN2
ATANH	ARCTANHYP
AYUDA	HILFE
BDCONTAR	DBANZAHL
BDCONTARA	DBANZAHL2
BDDESVEST	DBSTDABW
BDDESVESTP	DBSTDABWN
BDEXTRAER	DBAUSZUG
BDMAX	DBMAX
BDMIN	DBMIN
BDPRODUCTO	DBPRODUKT
BDPROMEDIO	DBMITTELWERT
BDSUMA	DBSUMME
BDVAR	DBVARIANZ
BDVARP	DBVARIANZEN
BINOM.CRIT	KRITBINOM
BUSCAR	VERWEIS

Multistage inventory control

Inventory control should full fill the following objectives:

• To determine the optimal inventory policy for the whole system.
• To keep the cumulative production line close to cumulative demand line.

Multistage inventory is also known as Multi-Echelon system, where we have different layers of inventory:

Managing inventories also implies to accept a level of uncertainty at different stages of the planning process. According to Galbraith uncertainty is
the difference between the amount of information required to perform a task and the amount of information already possessed.

There are different algorithms to manage multistage inventories:

• Kacprzyk and Staniewski (1982):
  • Infinite planning horizon.
  • Fuzzy system, with fuzzy inventory levels, inputs and outputs.
  • Algorithm that determines an optimal strategy to determine the reinstatement of existent inventory levels.
• Park (1987):
  • Economic Order Quantity (EOQ) model from a fuzzy set theory perspective.
  • The order and inventory costs are modelled by trapezoidal fuzzy numbers.
  • Rules to transform the fuzzy cost information in precise inputs for the EOQ model.
• Hojati (2004):
  • Probabilistic-parameter EOQ model
  • Fuzzy parameter EOQ model
  • Simulation to compare the results.
• Porter et al. (1995):
  • Genetic algorithm to solve an inventory-production-distribution problem.
  • The objective is to determine optimal stock levels, production quantities, and transportation quantities to minimize total system costs.
• Samanta and Al-Araimi (2001):
  • Based on fuzzy logic for inventory control.
  • Periodic revision of the inventory with a variable order quantity.
  • Combines fuzzy logic with a proportional-integralderivative (PID) control algorithm.
  • Product inventory at the desired level.
  • Demand variations and the dynamics of the production system.

One of the main conclusions is that there is an intrinsic relation between inventory contrl/management and the production system. Different production systems require different inventory systems.

Definitions:

• Inventory inaccuracy: The discrepancy between the actual and the estimate inventory

Keywords:

• Control: Centralized or Decentralized
• Lot size
• Reorder point logic
• Safety stock: Large or small
• Demand pattern
• Ordering pattern
• Inventory cycles

Inventory planning hierarchy

 

Sources:

• Mula. J., R. Poler, J.P. García-Sabater, and F.C. Lario, 2006, “Models for production planning under uncertainty: A review,” International Journal of Production Economics, Vol. 103, 271–285.
• Premises Inventory control in a distribution environment Information replaces inventory “DRP is simply the application to distribution inventories” MIT Open Course Ware
• Inventory systems in practice – MIT Open Course Ware
• Stanley B. Gershwin, “Single-stage, multiple part type, MTS production”, MIT Open Course Ware
• Stanley B. Gershwin, “Multi-Stage Control and Scheduling”, MIT Open Course Ware

Basic inventory principles

First it’s important to remarck that inventory control is an inexact science. This will give us an introduction to  basic inventory principles:

Importance of inventory costs.

What are the typical costs involved? (inventory cost drivers):

• Facility costs
• Human capital: The cost of labor to maintain storage
• Finance costs.
• Management costs.
• Procurement costs: costs of purchasing.
• Inventory turnover.
• Sock accuracy
• Service levels: critical goods, non critical goods, scheduled delivery

Price, Value and Cost

By reading Philip Kotler Principles of Marketing, we can extract 3 definitions that can help on this topic:

Price:

The amount of money charged for a product or service, or the sum of the values that consumers exchange for the benefits of having or using the product or service.

Click on the image to enlarge

Cost:

Costs set the floor for the price that the company can charge for its product. The company wants to charge a price that both covers all its costs for producing, distributing and selling the product, and delivers a fair rate of return for its effort and risk. There are different classifications of costs. The more general one is the one that defines fiexed costs and variable costs. Fixe costs do not vary with production or sales level while variable costs vary directly with the levelo of production.

Value:

Buyers’ perceptions of a product value.

Defining prices:

Usually a combination of the 3 previous concepts is used to determine the price of a product. This concept should consider:

• Product costs.
• Competitors’ price and other external and internal factors.
• Consumer perceptions of value.

According to that we can define 2 main pricing strategies:

• Cost based pricing: Product driven process. The company designs what it considers to be a good product, totals de costs of making the product and sets a price that covers cots plus a target profit.
• Value-based pricing: Customer driven process. The company sets its target price based on customer perceptions of the product value. The targeted value and price then drive decisions about product design and what costs can be incurred. As a result pricing begins with analysing consumer needs and value perceptions and a price is set to match consumers’ perceived value

Soutvr: Philip Kotler – Principles of Marketing, 3rd European Edition. p. 587

On the next video, we can see an interesting lecture from Philip Kotler at the London Business Forum where he states that:

Our job is to create, communicate and deliver value to a target market at a profit.