IS1 - EN - Exercise 4 (2011)Ejercicio Inglés
|Universidad||Universidad Internacional de Cataluña (UIC)|
|Grado||Administración y Dirección de Empresas (ADE) English Programme - 1º curso|
|Año del apunte||2011|
|Fecha de subida||05/06/2014|
Vista previa del texto
IS1 – EN – Exercise 4 – Instructions
IS1 – EN – Exercise 4 - Instructions
Professors: Gabriel FERANDEZ BORSOT & Javier SANCHEZ URETA
Geraldine Leirós Martínez
* Read the instructions below carefully and follow them accurately. Failure to follow instructions
accurately has negative impact on the mark. Be especially careful about what you deliver: you
should upload a zipped file, not a single Excel document.
* As you’ll see, the overall exercise consists in a set of exercises you should do framed within a fictional case.
* In some exercises you’ll be asked to calculate some values. You should always obtain these values sing formulas within the Excel spreadsheet. Any other external method of calculation won’t be considered acceptable.
* Obviously not all the exercises have the same contribution to the final score. Beside the number of each exercise you can see its contribution to the final score (out of 10).
* Once you finish the exercise, check that you saved everything correctly and that the exercise folder contains the latest version of all the files you have used. To deliver the correct files is your responsibility.
* After finishing all the exercises follow the instructions you’ll find at the end.
* Remember that the deadline for submitting your resolution is the one the teacher specified in class.
* Finally, as you should know, the official version of Microsoft Office for this subject is 2007. And the directions of this document are given for that version. However, there is no problem for you to do this exercise with any other version of the program (e.g. 2008 for Mac). It should only be recent enough to allow you to perform all the requested operations.
EXPLANATION OF THE CASE OF STUDY Ms. Awarenson is the owner of a small shop of organic fruits and vegetables. She has worked very hard all along the year 2010, opening every day without exception. Now, after having saved her business from the crises she has decided to devote more time for her personal and family matters. To make this possible she will hire a clerk for the store, who will substitute her two days every week.
Nonetheless she is worried about decreasing the quality of service because the new worker won’t have her experience neither her devotion, so to minimize the impact of it she wants to choose wisely which days of the week the new clerk will be substituting her.
In order to make a more informed decision, Ms. Awarenson has decided to hire our services as analytical consultants. As we’ll see, she is giving us data about the sales for each day of year 2010, and she wants us to determine (using exclusively numerical analysis) which days of the week the potential impact of service impoverishment would be minimal. She has the underlying assumption that the behavior of the consumers follows stable weekly patterns and it seems logic to us given she is selling perishable products that need to be bought in a regular basis.
Additionally, as a result of her long experience, she has observed that these patterns vary significantly depending on the quarter of the year. She wants us to take this into account, so our final conclusion should include different recommendations for each quarter.
As we’ll see now, we will arrive to a conclusion through a certain analytical process which is detailed in the following steps.
1 of 9 IS1 – EN – Exercise 4 – Instructions STEPS OF THE EXERCISE 1.- (0 points) Change the name of the folder that contains this document. The name you’ll give to the folder will be your full name, according to the following structure: IS1_EN_Exercise4_lastname1_lastname2_name Therefore, if your full name were “Adelina Mas Torres”, you’d name the folder as “IS1_EN_Exercise4_mas_torres_adelina”. (If you don’t have two lastname’s just use one.) From now on you’ll put all the new files and documents in this folder.
WARNING: Notice that you should close this document before attempting to change the name of the folder or the operating system will not allow you to do so! You will open this document again after changing the name of the folder.
2.- (0 points) Open the Excel spreadsheet “IS1 - EN - Exercise 4 - Data.xlsx” that you will find in the same folder as this document. You’ll see it has 3 sheets, with the following information: a. “Products”: it contains a list of the basic products that Ms. Awarenson sells in the shop, with their selling price in € per Kg.
b. “Sales”: it contains the amount of Kg of each product that has been sold each day of year 2010.
c. “Quarters”: it contains the list of months associated with each quarter of the year. As you’ll see it will be very helpful.
3.- (0,5 points) First we want to prepare the sheets so we can manage the information more easily. Add to each table the filter functionality and freeze panes (“inmovilizar paneles”) so we will always see the headers of the columns. Additionally assign a different color to the label/tab of each sheet.
4.- (0,5 points) Our plan to arrive to the requested conclusion consists on creating a table which summarizes the average income per day of the week and quarter. In it we will see clearly which two days of week have the minimum value for each quarter.
To do so we add a new sheet we will call “Averages” and in it we will create a table with this layout (font is Calibri, size 11): 2 of 9 IS1 – EN – Exercise 4 – Instructions 5.- (0,5 points) Now we are going to calculate the daily income. To do so we’ll add a column in the sheet “Sales” and we’ll build a formula. The result should look like this: Notice the income column have been formatted so the numbers always show two decimal positions and has the point as a decimal separator.
6.- (0,5 points) Add a row with the totals at the end of the sales data, and format them as required so the result look like this: 7.- (1 point) Ms. Awarenson says that the days with an income of 700 € or more are totally anomalous and shouldn’t be taken into account in our calculations because they would bias the results. To exclude these days when we calculate averages we need to add an additional column in the sheet “Sales” and using a conditional function assign to each row the text “NORMAL” if income is less than 700 € for that day, or “ANOMALOUS” if not.
The result should look like this: 8.- (0,5 points) To check that we did last step correctly, apply a filter on column “NORMAL/ANÓMALOUS”, selecting only the “ANOMALOUS” values. Then remove this filter and add another on the column “Income (€)” with the condition of “greater than or equal to” 700 €. Both results should be identical. Make a screenshot of the result with the second filter applied and insert this screenshot in this document right below the following box.
INSERT THE SCREENSHOT BELOW THIS BOX (IN THE BLANK SPACE), IN THIS WORD DOCUMENT 3 of 9 IS1 – EN – Exercise 4 – Instructions 9.- (0,5 points) Add a column to sheet “Sales” to show the day of the week corresponding to each row. You should put first letter uppercase because this is how we have it in the table of the sheet “Averages”. The result should be this: 10.-(1 point) Now we need to obtain a column with the quarter of the year corresponding to each row. We’ll do this through two steps: 1. Add a column in sheet “Sales” with the month of the date, like this: 2. After that, add a new column beside. In this column we will obtain the quarter using the information of the sheet “Quarters”. To do so you can use a combination of the functions “INDICE” & “COINCIDIR” or instead use function “BUSCARV” modifying the table of the correspondences as needed. The result should look like this: 11.-(2 points) Now we have all the information ready to be used for the calculations of the sheet “Averages”. Notice you should build just one formula in the first blank cell (the one which corresponds to “Lunes” and “Quarter 1”), using references adequately so you can drag the formula to the rest of cells.
To build the formula consider the following tips: 1. The average is the result of dividing the aggregated income for the specific day of the week and quarter BY the number of days which correspond to that day of the week and quarter.
4 of 9 IS1 – EN – Exercise 4 – Instructions 2. You should include only the rows which display “NORMAL” in the “NORMAL/ANÓMALOUS” column.
3. To calculate the aggregated income for a specific day of the week and quarter you can use the function SUMAR.SI.CONJUNTO() including the three conditions: day of the week, quarter, and “NORMAL”.
4. To calculate the number of days which correspond to a specific day of the week and quarter you can use the function CONTAR.SI.CONJUNTO() including the three conditions: day of the week, quarter, and “NORMAL”.
5. If you find it too complicated you can do it through steps, creating one table for the amount of income, another for the number of days, and finally another with the division.
The finished table should look like this, after having applied the suitable formatting for the numbers: Notice we have manually configured the yellow background for the pair of days with minimum average for each quarter, because they constitute the conclusion of our study.
12.-(0,75 points) Now we’ll build a chart so Ms. Awarenson can visualize the results easily. Add a new sheet called “Chart” and make a chart of cones which looks like the following: 5 of 9 IS1 – EN – Exercise 4 – Instructions Notice the scale of vertical axis has been configured so it ranges from 250 € to 340 €, and maybe you’ll have to apply a 3D rotation to achieve the desired perspective.
13.-(1 point) Now we are going to repeat the calculations but using a PivotTable. Add a new sheet called “PivotTable” and create a PivotTable in it so it shows the information of average income per day of the week and quarter. Notice you’ll have to add a filter so you include only the days with value “NORMAL” in the column “NORMAL/ANOMALOUS”. The result should look like this: Maybe after creating it you’ll have to format the numbers and apply a predefined style to the table so it looks like the model above.
14.-(0,75 points) Create a PivotChart to represent the information of the previous table. Make it in a new sheet called “PivotChart”. The Chart should look like this: 6 of 9 IS1 – EN – Exercise 4 – Instructions Notice the scale of vertical axis has been configured so it ranges from 250 € to 340 €, and maybe you’ll have to apply a 3D rotation to achieve the desired perspective.
Notice also that applying filters per quarters you can make the chart to show the information of only one quarter at a time, where it is much easier to determine the two days with minimum average: 15.-(0,5 points) If you take a quiet look to the results we have obtained, ¿do you think that the initial hypothesis considering that the behavior of the customers followed a weekly pattern varying per quarters has been confirmed? Consequently, ¿do you think that the analysis of the averages per day of the week and quarter constitutes a solid argument to choose the days Ms. Awarenson will assign to her substitute? Write your answer in the box below giving reasons for your positions: 7 of 9 IS1 – EN – Exercise 4 – Instructions WRITE YOUR ANSWER HERE: In the first place, I don’t think that there’s a weekly pattern varying per quarters followed by the consumer; perhaps on the second quarter, but even that one isn’t that far away from the average.
In the second place I strongly think that the analysis by quarters is not a good one. Maybe she could divide the year among other considerations; for example: by seasons. It wouldn’t be that different, but I think the results would differ and therefor they would be more effective.
Finally, even though these values seem to be too close to the average per day to be a strong parameter to take into account. I think that she could take off Tuesdays and Wednesdays, which are circa 6% and 3% respectively under the average of income.
Personally I’d rather know first, the relationship between these results and the affluence of costumers; because if what’s happening here is, that not enough people go into the store, it wouldn’t matter that much, if the person in charge is someone different than Ms. Awarenson.
IMPORTANT REMARK: this exercise presents a fictitious case for the sole purpose of providing a frame to practice certain features of Excel. The criteria to make decisions in a real case are much more complex and should take into account aspects which cannot be easily formalized for a numerical analysis, as the preferences of Ms. Awarenson, the days the preferred customers use to buy, the human sensitivity versus the preferences of the clerk, etc.
8 of 9 IS1 – EN – Exercise 4 – Instructions INSTRUCTIONS TO FINISH AND SEND THE EXERCISE 1.- Perform a general review.
2.- Ensure that you have saved all the documents you've created or used in the exercise.
3.- Make sure the folder you renamed in the step nr. 1 contains all the documents used in the exercise.
(REMARK: the folder may also contain one or more documents of type ~$1 - EN Exercise 4 – Instructions.doc or ~WRL0001.tmp. Do not worry, they have been created by Word and they will disappear when you close the program.) 4.- Create the compressed file corresponding to the folder that contains all the documents.
5.- Check that you have compressed the folder exactly as required: when you decompress the file it should create just ONE SINGLE FOLDER (whose name should be the one indicated in step nr. 1), which contains this document and all the other documents or files used in the exercise.
WARNING: if once you have generated the compressed file you change anything you should rebuild the compressed file again! 6.- Upload the compressed file to the corresponding folder of the intranet (folder “Exercise 4” within folder “IS1 – Assignment Drop Boxes”, in the section “Materials” of the course’s space in the intranet).
7.- Ensure that the file you have uploaded is correct. To do so, download it, unzip it, and check that it creates only one folder (with the name indicated in step nr. 1 of this document) which contains the last version of the files, with your resolution.
WARNING: keep a copy of your resolution of the exercise just in case there was any technical problem! 8.- About one week after the deadline of the exercise the scores will be published in the intranet, as “Notas parciales”. If your score appears as "NP" it means that the teachers have not been able to locate your resolution of the exercise (probably due to any technical problem with the upload). In that case send an email to the teacher explaining the situation.
9 of 9 ...