Analyse prédictive dans Excel: Régression multiple

J’ai déjà publié, sur ce blogue, un article concernant la régression linéaire simple dans Excel. Nous y avions notamment vu comment utiliser la fonction DroiteReg (Linest) et la fonction Tendance (Trend). L’article suivant se concentre plutôt sur la régression linéaire multiple. Nous allons retrouver l’usage de la fonction DroiteReg (Linest) mais également l’usage de l’option de régression présente dans le menu Données, sous Analyse de données (Data Analysis), lorsque le add-In "Analysis Toolpak" est activé dans Excel.

Exemple de départ

Dans notre exemple, nous avons les unités produites pour 3 produits différents et le coût de production total pour une certaine période d’observations.

Analyse de données (Data Analysis)

Nous cherchons à obtenir les données de régression multiple. Autrement dit, nous cherchons à comprendre si le nombre d’unités produites, pour chacun des produits à un pouvoir prédictif sur le coût de production total (on peut supposer que oui, mais dans quelle mesure!). Pour faire l’analyse de régression, nous irons donc dans le menu Données (Data) et nous choisirons le sous-menu Analyse de données (Data Analysis). Ensuite, nous sélectionnerons l’option Régression (Regression).

Nous indiquerons à Excel où se trouve notre plage de données Y (les coûts de production) et notre plage de données X (les unités produites pour les 3 produits). Nous lui indiquerons également que nous souhaitons obtenir l’information concernant les résidus.

Nous cliquerons ensuite sur OK pour obtenir le résumé suivant, incluant une analyse ANOVA et une analyse de résidus.

Fonction DroiteReg (Linest)

Nous aurions également pu utiliser la fonction DroiteReg (Linest), tel qu’illustré dans l’article Analyse prédictive dans Excel: Régression linéaire simple. Nous aurions ainsi obtenu l’ordonnée à l’origine et les coefficients de régression, tel qu’illustré sur l’image suivante.

À l’aide de l’ordonnée à l’origine et des coefficients de régression obtenus, nous aurions pu ensuite prévoir le coût de production, en fonction du # d’unités prévues pour chacun des 3 produits.

Nous aurions également pu sélectionner une plus grande plage dans Excel afin d’obtenir une série d’autres statistiques intéressantes, toujours par le biais de la fonction DroiteReg (Linest).

Quelles variables sont importantes?

La question qu’il faut se poser est : quelles sont les variables qui ont réellement un pouvoir prédictif sur le coût de production. Pour procéder à cette ananlyse, nous pouvons d’abord observer le "p-value", qui varie entre 0 et 1. Généralement, un "p-value" égal ou inférieur à 0,05 indique que la variable a un réel pouvoir prédictif. En fait, plus le "p-value" est petit, plus la variable a un potentiel prédictif intéressant. Dans notre exemple, on peut conclure que la variable 1 (le # d’unités produites pour le produit 1) a réellement un pouvoir prédictif intéressant (près de 99.95%) sur le coût de production alors que les deux autres variables ont un pouvoir prédictif beaucoup moins intéressant. De même, le R Square indique que les 3 variables réunies arrivent à prédire 73% du coût de production.

Calendrier de formations

Pour en savoir davantage sur nos formations ou pour vous inscrire, vous pouvez consulter notre calendrier de formation.

Sophie Marchand, M.Sc., CPA, CGA, MVP
514-605-7112
Experte en modélisation financière et intelligence d’affaires

Vous avez des questions?

Posez toutes vos questions sur le forum du CFO masqué.