■■ Comment puis-je déterminer la relation entre la production mensuelle et les coûts d’exploitation mensuels?

■■ Avec quelle précision cette relation explique-t-elle la variation mensuelle des coûts d’exploitation de l’usine?

■■ Quelle est la précision de mes prévisions?

■■ Lors de l’estimation d’une relation linéaire, quelles fonctions puis-je utiliser pour obtenir la pente et interception de la ligne qui correspond le mieux aux données?

Supposons que vous dirigiez une usine qui fabrique de petits réfrigérateurs. Le siège social national vous indique le nombre de réfrigérateurs à produire chaque mois. À des fins de budgétisation, vous souhaitez prévoir vos coûts d’exploitation mensuels et avez besoin de réponses aux questions qui sont au centre de cet article.

Chaque analyste d’entreprise devrait avoir la capacité d’estimer la relation entre les variables commerciales importantes. Dans Microsoft Excel, la courbe de tendance, qui est abordée dans ce chapitre ainsi qu’à l’article 54, «Modélisation de la croissance exponentielle» et dans l’article 55, «La courbe de puissance», est souvent utile pour déterminer la relation entre deux variables. La variable que les analystes tentent de prédire s’appelle la variable dépendante. La variable que vous utilisez pour la prédiction est appelée variable indépendante. Voici quelques exemples de relations commerciales que vous voudrez peut-être estimer:

La première étape pour déterminer comment deux variables sont liées consiste à représenter graphiquement les points de données (à l’aide du diagramme de dispersion) de sorte que la variable indépendante se trouve sur l’axe des x et la variable dépendante sur l’axe des y. Avec le graphique sélectionné, cliquez sur un point de données (tous les points de données sont ensuite affichés en bleu), cliquez sur Ligne de tendance dans le groupe Analyse de l’onglet Disposition des outils de graphique, puis cliquez sur Plus d’options de ligne de tendance (ou cliquez avec le bouton droit et sélectionnez Ajouter une ligne de tendance). Vous voyez la boîte de dialogue Format Tendance, qui est illustrée à la figure 1.

FIGURE 1 Voici les options de la boîte de dialogue Format de la ligne de tendance.

Si votre graphique indique qu’une ligne droite est un ajustement raisonnable aux points, choisissez Linéaire. Si le graphique indique que la variable dépendante augmente à un rythme accéléré, l’exponentielle (ou peut-être la puissance) correspond probablement à la relation. Si le graphique montre que la variable dépendante augmente à un taux décroissant, ou que la variable dépendante diminue à un taux décroissant, la puissance est probablement la plus pertinente.

Cet article se concentre sur l’option linéaire. L’article 54 traite de l’option exponentielle et l’article 55 couvre l’option d’alimentation. L’article 62, «Utilisation de moyennes mobiles pour comprendre les séries chronologiques», traite de la courbe de moyenne mobile, et l’article 85 «Tarification des produits en utilisant la demande subjective», traite de la courbe polynomiale. (La courbe logarithmique a peu de valeur dans cette discussion, donc elle n’est pas abordée.)

Comment puis-je déterminer la relation entre la production mensuelle et les coûts d’exploitation mensuels?

Le fichier, illustré à la figure 2, contient des données sur les unités produites et les coûts d’exploitation mensuels de l’usine pour une période de 14 mois. Vous souhaitez prédire les coûts d’exploitation mensuels des unités produites, ce qui aide le directeur de l’usine à déterminer le budget de fonctionnement et à mieux comprendre le coût de production des réfrigérateurs.

FIGURE 2 Voici les données de fonctionnement de l’usine.

Commencez par créer un graphique XY (ou un nuage de points) qui affiche la variable indépendante (unités produites) sur l’axe des x et la variable dépendante (coût mensuel de l’installation) sur l’axe des y. La colonne de données que vous souhaitez afficher sur l’axe des x doit être située à gauche de la colonne des données que vous souhaitez afficher sur l’axe des y. Pour créer le graphique, sélectionnez les données dans la plage C2: D16 (y compris les étiquettes dans les cellules C2 et D2). Cliquez sur Scatter dans le groupe Graphiques de l’onglet Insertion et sélectionnez la première option (Scatter with Only Markers) comme type de graphique. Vous voyez le graphique illustré à la figure 3.

FIGURE 3 Voici un diagramme de dispersion du coût d’exploitation en fonction des unités produites.

Lorsque vous regardez le nuage de points, il semble raisonnable qu’une ligne droite (ou relation linéaire) existe entre les unités produites et les coûts d’exploitation mensuels. Vous pouvez voir la ligne droite qui correspond le mieux aux points en ajoutant une ligne de tendance au graphique. Cliquez dans le graphique pour le sélectionner, puis cliquez sur un point de données. Tous les points de données sont affichés en bleu, avec un X couvrant chaque point. Cliquez avec le bouton droit, puis cliquez sur Ajouter une ligne de tendance. Dans la boîte de dialogue Format de la ligne de tendance, sélectionnez Linéaire, puis sélectionnez l’équation d’affichage sur le graphique et  les cases à cocher Afficher la valeur R au carré sur le graphique, comme illustré à la figure 4.

FIGURE 4 Sélectionnez les options de ligne de tendance.

Après avoir cliqué sur Fermer et ajouté des étiquettes d’axe et un titre de graphique, vous voyez les résultats affichés dans la figure 5. Un titre a été ajouté au graphique et aux étiquettes pour l’axe x et l’axe y en sélectionnant Outils de graphique et, dans l’onglet Conception, en sélectionnant Ajouter un élément de graphique.

FIGURE 5 Il s’agit de la ligne de tendance terminée.

Si vous souhaitez ajouter plus de points décimaux aux valeurs de l’équation, vous pouvez sélectionner l’équation de la ligne de tendance et, après avoir sélectionné Format dans les outils de graphique, choisissez Format de sélection. Après avoir sélectionné Nombre, vous pouvez choisir le nombre de décimales à afficher. 

Comment Excel détermine-t-il la ligne la mieux adaptée? Il choisit la ligne qui minimise (sur toutes les lignes

qui pourrait être dessinée) la somme de la distance verticale au carré de chaque point à la ligne. La distance verticale de chaque point à la ligne est appelée une erreur ou résiduelle. La ligne créée par Excel est appelée la ligne des moindres carrés. Vous minimisez la somme des erreurs quadratiques plutôt que la somme des erreurs car, en additionnant simplement les erreurs, les erreurs positives et négatives peuvent s’annuler. Par exemple, si vous ajoutez des erreurs, un point 100 unités au-dessus de la ligne et un point 100 unités au-dessous de la ligne s’annulent. Si vous corrigez les erreurs, cependant, Excel utilise le fait que vos prévisions pour chaque point sont erronées pour trouver la ligne la mieux adaptée.

Ainsi, Excel calcule la ligne droite la mieux adaptée pour prédire les coûts d’exploitation mensuels à partir de unités mensuelles produites comme suit:

(Coût d’exploitation mensuel) = 37894,0956 + 64,2687 (Unités produites)

En copiant la formule 64.2687 * C3 + 37894.0956 de la cellule E3 vers la plage de cellules E4: E16, vous calculez le coût prévu pour chaque point de données observé. Par exemple, lorsque 1 260 unités sont produites, le coût prévu est de 123 118 $. (Voir figure 2.)

Vous ne devez pas utiliser une ligne des moindres carrés pour prédire les valeurs d’une variable indépendante qui se trouvent en dehors de la plage pour laquelle vous avez des données. La ligne de cet exemple ne doit être utilisée que pour prédire les coûts d’exploitation mensuels de l’usine pendant les mois où la production se situe entre environ 450 et 1300 unités.

L’ordonnée à l’origine de cette ligne est de 37 894,10 $, ce qui peut être interprété comme le coût fixe mensuel. Par conséquent, même si l’usine ne produit pas de réfrigérateurs pendant un mois, ce graphique estime que l’usine devra toujours engager des coûts de 37 894,10 $. La pente de cette ligne (64,2687) indique que chaque réfrigérateur supplémentaire produit augmente les coûts mensuels de 64,27 $. Ainsi, le coût variable de production d’un réfrigérateur est estimé à 64,27 $.

Les erreurs (ou résidus) pour chaque point de données ont été calculées dans les cellules F3: F16. L’erreur pour chaque le point de données a été défini comme la différence entre le point et la ligne des moindres carrés. Pour chaque mois, l’erreur est égale au coût observé moins le coût prévu. La copie de la formule D3-E3 de F3 vers F4: F16 calcule l’erreur pour chaque point de données. Une erreur positive indique qu’un point est au-dessus de la ligne des moindres carrés et une erreur négative indique que le point est au-dessous de la ligne des moindres carrés. La somme des erreurs a été calculée dans la cellule F1 et a donné –0,03. En réalité, pour toute ligne des moindres carrés, la somme des erreurs doit être égale à 0. (–0,03 a été obtenue car l’équation a été arrondie à quatre décimales.) Le fait que la somme des erreurs à 0 implique que la ligne des moindres carrés a la propriété intuitivement satisfaisante de diviser les points en deux.

Dans quelle mesure cette relation explique-t-elle avec précision la variation mensuelle des coûts d’exploitation de l’usine?

De toute évidence, chaque mois, les coûts d’exploitation et les unités produites varient. Une question naturelle est: «Quel pourcentage de la variation mensuelle des coûts d’exploitation s’explique par la variation mensuelle des unités produites?» La réponse à cette question est la valeur R2 (0,69 indiquée dans la figure 5). Vous pouvez affirmer que la relation linéaire explique 69% de la variation des coûts d’exploitation mensuels. Cette implique que 31% de la variation des coûts d’exploitation mensuels s’explique par d’autres facteurs. En utilisant la régression multiple (voir les chapitres 57 à 59), vous pouvez essayer de déterminer d’autres facteurs qui influencent les coûts d’exploitation.

Les gens demandent toujours ce qu’est une bonne valeur R2. Il n’y a vraiment pas de réponse définitive à cette question. Avec une variable indépendante, bien sûr, une valeur R2 plus grande indique un meilleur ajustement des données qu’une valeur R2 plus petite. Une meilleure mesure de l’exactitude de vos prédictions est l’erreur standard de la régression, qui est décrite dans la section suivante.

Quelle est la précision de mes prévisions?

Lorsque vous ajustez une ligne aux points, vous obtenez une erreur standard de la régression qui mesure l’écart des points autour de la ligne des moindres carrés. L’erreur standard associée à une ligne des moindres carrés peut être calculé avec la fonction STEYX. La syntaxe de cette fonction est STEYX (yrange, xrange), où yrange contient les valeurs de la variable dépendante et xrange contient les valeurs de l’inde  variable antérieure. Dans la cellule K1, l’erreur standard de la ligne d’estimation des coûts dans le fichier Costestimate.xlsx a été calculée à l’aide de la formule STEYX (D3: D16, C3: C16). Le résultat est illustré à la figure 6.

Environ 68% des points devraient se trouver à l’intérieur d’une erreur standard de régression (SER) de la ligne des moindres carrés, et environ 95% des points devraient être à moins de deux SER de la ligne des moindres carrés. Ces mesures rappellent la règle empirique des statistiques descriptives décrite dans l’article 42, «Synthèse des données à l’aide de statistiques descriptives». Dans cet exemple, la valeur absolue d’environ 68% des erreurs devrait être de 13 772 $ ou moins, et la valeur absolue d’environ 95% des erreurs devrait être de 27 544 $ (ou 2 * 13 772) ou moins. En examinant les erreurs de la colonne F, vous pouvez voir que 10 points sur 14, soit 71%, des points se trouvent dans une SER de la ligne des moindres carrés, et tous (100%) des points sont dans deux SER standard d’ une ligne des moindres carrés. Tout point situé à plus de deux SER de la ligne des moindres carrés est appelé une valeur aberrante. La recherche des causes des valeurs aberrantes peut souvent vous aider à améliorer le fonctionnement de votre entreprise. Par exemple, un mois au cours duquel les coûts d’exploitation réels sont 30 000 $ plus élevés que prévu serait une valeur aberrante. Si vous pouviez déterminer la cause de cette valeur aberrante élevée et l’empêcher de se reproduire, vous amélioreriez clairement l’efficacité de l’usine. De même, considérons un mois au cours duquel les coûts réels sont inférieurs de 30 000 $ aux prévisions. Si vous pouviez déterminer la cause de cette valeur aberrante à faible coût et vous assurer que cela se produise plus souvent, vous amélioreriez l’efficacité de l’usine.

FIGURE 6 Il s’agit du calcul de la pente, de l’ordonnée à l’origine, des valeurs R au carré (RSQ) et de l’erreur standard de régression.

Lors de l’estimation d’une relation linéaire, quelles fonctions puis-je utiliser pour obtenir la pente et interception de la ligne qui correspond le mieux aux données?

Les fonctions Excel PENTE (yrange, xrange) et ORDONNEE.ORIGINE. (yrange, xrange) renvoient respectivement la pente et l’ordonnée à l’origine de la ligne des moindres carrés. Ainsi, la saisie de la formule PENTE (D3: D16, C3: C16) dans la cellule I1 (voir figure 53-6) renvoie la pente (64.27) de la ligne des moindres carrés. Entrant le La formule ORDONNEE.ORIGINE. (D3: D16, C3: C16) dans la cellule I2 renvoie l’ordonnée à l’origine (37 894,1) de la ligne des moindres carrés. Par ailleurs, la fonction RSQ (yrange, xrange) renvoie la valeur R2 associée à une ligne des moindres carrés. Ainsi, la saisie de la formule RSQ (D3: D16, C3: C16) dans la cellule I3 renvoie la valeur R2 de 0,68882 pour la ligne des moindres carrés.