Rappel sur les formules : Application au Plan de financement

Une association envisage de réaliser un investissement de 30 000€. Mais, elle hésite entre un autofinancement de cet investissement ou le recours à l'emprunt.

Pour l'aider à choisir, vous allez calculer les recettes futures estimées sur les 4 prochaines années.

La formule pour calculer l'annuité constante est :
C : capital emprunté, i : taux d'intérêt annuel, n : nombre d'années.

Exercice

  • Télécharger le document et renommer le TD-NOM-plan-financement.xlsx.
  • Remplir le premier tableau dans la feuille appelée Emprunt. En bleu les données du problème et en vert les cases à remplir.

Le VAN est un indicateur financier qui permet de mesurer la rentabilité d'un projet d'investissement.

  • Si un VAN<0 : les flux espérés par l'investissement ne permettent pas la récupération du capital investi. Le projet est donc non rentable.
  • Si un VAN=0 : le coût de l'investissement est compensé par les flux de trésorerie actualisés.
  • Si un VAN>0 : L'investissement crée de la valeur après récupération totale du capital investi. Le projet est donc rentable.

Exercice

  • Remplir le deuxième tableau dans la feuille appelée Comparaison. En bleu les données du problème et en vert les cases à remplir.
  • Comparer les valeurs actuelles nettes (VAN) des 2 modes de financements. Quelle vous semble être la meilleure stratégie ?

Principe du publipostage

L'objectif est d'envoyer des courriers papier (publipostage) ou électroniques (e-mailing) à un groupe de personnes. Ces documents ont une base commune mais sont personnalisés. Par exemple, on écrira "Monsieur" ou " Madame" selon le cas. Dans cet exercice, nous allons nous focaliser sur des courriers papiers.

Il y a donc une partie fixe (commune à tous les documents) et des éléments variables. Pour composer ces documents, on utilise un traitement de texte pour la partie fixe dans laquelle seront insérés des champs alimentés par un classeur. Dans le classeur, ces champs correspondent à des colonnes dont la première cellule contient le nom du champ.
Pour un e-mailing, un des champs contiendra les adresses électroniques des destinataires.

L'exemple à traiter

Voici un message à envoyer à un ensemble d'étudiants par courrier postal. (les champs sont en italique).

Destinataire : Madame Laure DURIER

Objet : Remise de diplôme

   Chère Madame Laure DURIER,
   Nous avons le plaisir de vous annoncer que le Jury de fin d'année vous a délivré le diplôme : Licence d'Archéologie.
   Toutes nos félicitations !
   Vous pourrez venir rechercher votre diplôme d'ici un mois auprès des services administratifs.

            Cordialement.
            Le secrétariat de l'UFR3.

Quels sont les champs en présence  ?

  • Civilité : Chère Madame
  • Prénom : Laure
  • Nom : DURIER
  • Diplôme : Licence d'Archéologie.
  • UFR : UFR3
Nous pouvons maintenant créer les éléments (classeur et texte) qui vont permettre de produire les messages.

Les données variables

Elles sont réunies dans un classeur : une colonne correspond à un champ et la première ligne contient le nom des champs : Adresse, Civilité, etc. L'ordre des colonnes n'importe pas.

Exercice

  • Créer un document à l'aide d'un tableur.
  • Remplir les colonnes avec des données de vos camarades (au moins 5-6 lignes).
  • Enregistrer ce classeur sous le nom Champs_Publipostage_NOM.xls.

Voici un exemple : les données, en particulier les adresses, n'existent pas !

Message

Exercice

  • Ouvrir un nouveau document texte cette fois.
  • Taper le texte du message, sauf les champs.
  • Activer le publipostage Menu Publipostage sur Word et Menu Insertion > Champs > Autres... sur OpenOffice.
  • Insérer les champs dans votre document.
  • Pour envoyer les messages, suivre les différentes étapes proposées par le logiciel que vous utilisez.

Il se peut enfin que le logiciel ne soit pas configuré pour envoyer des messages. Il faut alors lui indiquer vos nom et adresse ainsi que le serveur d'envoi de courrier.

Si tout a bien marché, vous avez dû générer plusieurs courriers !

Carte

Excel permet de créer des cartes choroplèthes. Il s'agit de cartes thématiques où les régions sont colorées ou remplies d'un motif qui montre une mesure statistique, tels la densité de population ou le revenu par habitant. Ce type de carte facilite la comparaison d'une mesure statistique d'une région à l'autre.
carte issue de Wikipedia

Pour pouvoir correctement représenter vos données sur une carte, il est primordial de respecter les règles suivantes :

  • Mettre les données géographiques (e.g. les pays) et les données à afficher sur des colonnes côte-à-côte (e.g. la population).
  • Pour représenter des données géographiques à une échelle inférieure à un pays comme des régions ou des départements, il est indispensable de rajouter le nom du pays comme donnée géographique.
  • Les entête des colonnes doivent être : Pays, Province (pour les régions) ou Départements (au pluriel) et Valeur.

Exercice

Enquête

Cet exercice TD est largement inspiré du TP de V. Godard. Ce TD a pour objectif de revoir les bases du tableur en traitant les données issues d'une enquête.

Exercice

  • Télécharger ce document et ouvrez-le avec OpenOffice ou Excel. Les données manipulées sont les données d'une enquête.
  • Enregistrer-le sous le nom TDB4-NOM-tableau.xls.
  • Renommer la feuille "Saisie" à votre nom.

La 1ère feuille de calcul est un tableau de saisie correspondant à des réponses de personnes à des sondeurs pour un même questionnaire.

La 1ère colonne correspond aux numéros des questionnaires. La 2ème colonne correspond aux initiales du sondeur. Les colonnes suivantes correspondent aux différentes réponses des sondés aux questions posées par les sondeurs.

La 1ère ligne correspond au nom des variables. La 2ème ligne correspond au nom des modalités. Par exemple, la variable sexe a 2 modalités : Femme et Homme. La 3ème ligne correspond au 1er enregistrement (réponses d'un sondé à un questionnaire)

Pré-traitements du fichier

Ce fichier a été saisi manuellement et est donc... entaché de multiples erreurs.

Exercice

  • Uniformiser (e.g. initiales des sondeurs)
  • Recoder (e.g. remplacement des "X" et "x" par des 1)
  • Vérifier les problèmes de saisie (e.g. des personnes peuvent-elles être à la fois des hommes et des femmes ?).

Tri et Filtrage

Exercice

  • Combien y a-t-il eu de questionnaires passés après le jeudi  ?
  • Combien d'hommes de plus de 30 ans ont répondu au questionnaire  ?
  • Y a-t-il eu des femmes de 20 ans le lundi dans la même situation vis-à-vis de leur perception de la difficulté de leur travail ?
  • Quel jour de la semaine SD a-t'il enquété  ?
  • Afficher les questionnaires enquêtés après le jeudi ET issus de l'UFR 4

Formatage conditionnel

Le formatage conditionnel permet d'associer une présentation à une plage de cellules selon la valeur de son contenu.

Exercice

  • Définissez un format conditionnel pour la colonne âge : la couleur de fond sera verte si la valeur de la cellule est inférieure à 20, orange si inférieure à 40 et rouge sinon.

    Nous allons maintenant générer des graphiques pour représenter les résultats de l'enquête.

Graphique en barre

Ci-dessous 3 exemples de graphique en barre.

  • Un graphique à barres ou colonnes simples sert à comparer plusieurs éléments, ici les ventes de vendeurs.
  • Un graphique à barres ou colonnes multiples sert à comparer plusieurs séries de données, produisant des barres distinctes pour chaque intervalle. Il permet de représenter deux dimensions. Ici, les ventes pour 3 villes sont décomposées selon la contribution des 4 vendeurs.
  • Un graphique à barres ou colonnes horizontales tronçonnées permet de gagner de la place.

Exercice

  • Créer une nouvelle feuille et renommer la "Barre".
  • Supprimer les doublons :
    • Récupérer par copier-coller la colonne contenant les initiales des sondeurs et trier la colonne par ordre alphabétique.
    • Dans une 2ème colonne, utiliser la formule =si(A2=A1;1;0). Etirer la.
    • Sélectionner-la, copier-la puis coller-la en utilisant l'option collage spécial > uniquement les valeurs. Toutes les premières apparitions d'une initiale prennent les valeurs 0 et les autres 1.
    • Trier les données par rapport à la 2ème colonne. Ne conserver que les lignes contenant 0.
    • Cette technique est bien pratique pour éliminer les doublons.
  • Créer un nouveau tableau avec 2 colonnes : Sondeur Nombre de personnes sondés.
  • Utiliser la fonction nb.si pour remplir ce tableau.
  • Représenter le graphique en barre associé.

Secteur

Les secteurs sont utiles pour représenter la relation entre une partie et un tout, ici la répartition des salaires dans des classes de revenus.

Exercice

  • Créer une nouvelle feuille et renommer la "Secteur".
  • Créer un nouveau tableau ayant pour colonnes les différents jours : Lundi Mardi Mercredi Jeudi Vendredi Samedi
  • Sur la deuxième ligne de ce tableau, trouver la formule pour compter le nombre de réponses par jour pour tous les sondeurs.
  • Représentez le graphique secteur associé. Quel est le jour ayant le plus de réponses  ?
  • Faire de la même manière des secteurs pour les autres variables UFR, Sexe, Bourse, Travail, Ponctualité...
  • Pour la variable Age, discrétiser les variables en 4 classes au préalable. Pour cela trouver la valeur maximum et minimum des valeurs des âges avec les fonctions MAXIMUM et MINIMUM.
  • Identifier les valeurs permettant de couper cet intervalle en 4 sous intervalles égaux.
  • Ajouter une colonne à droite de la variable Age et utiliser la fonction SI pour associer à chaque âge un intervalle.
  • Créer un nouveau tableau ayant pour colonnes les 4 intervalles et utiliser-le pour générer le secteur.
  • Recommencer avec la variable Nombre d'heures.

Création d'une enquête

Des sites spécialisés comme framaforms.org, Google form, etc. permettent de créer des formulaires et de les analyser.

Exercice

  • À partir du site de votre choix, créer un questionnaire correspondant à celui que nous venons de traiter.