Spring 2013MIS120

AmericanBusinessSchool

MIS 120 EXCEL TEST, March 2013

Preliminaries

Log on using your password, anddownload the file corresponding to your group and where you sit. This assignment is mandatory; you cannot change the file you will work on.

Download the file to your personal space (drive U)and change its name. This is how: if it was called 03.xlsx (for most of you, it is not!), and if your name was Bill Gates (I happen to know that none of you has that name!), then you would change the name to 03_Gates.xlsx.

Misnaming a file will result in points off.

Also, please do not put your files in a subfolder, save them directly on U.

If you work on your laptop, make sure I have your work on my USB key by the end of class.

Save your work every 5 minutes!

All work ends 10 minutes before the end of class. I will not consider files saved later than that.

The setup

You have a list of members of a health club. Each person selected the type of membership (the most extensive list is Economical, Individual, Family, Deluxe, and Gold, but the exact list depends on which version of the source file you got) and whether he or she wants to rent a locker. These data are already present in the file. Another data already present is the subscription period – for how many years (1,2,or 3) he or she is subscribing.

Depending on the type of membership, the person will pay a certain amount per year and, if he is renting a locker, an annual locker fee which also depends on the type of membership. These data figure under the list of members. Also depending on the type of membership, the person will pay a fixed amount as a down payment, and pay the rest in installments at a certain interest rate.

The interest rate will depend on the person’s income level (we are in a social country!). The purpose is to calculate the monthly payment for each member.

The number of “Income levels” differs from file to file. The examples I show in this instruction sheet are taken from a file with 4 income levels.

Part 1

The computation

  1. You can think if you prefer perform the operation below on the data sheet after transforming it into an Excel table, or if you prefer leaving it as a range.
  2. All calculations must be adaptable to any change in the data. No number (like the interest rate or the number of periods etc.) should figure in the formulas as such: all such numbers should be implemented as references to the cells where they figure.
  3. Put a function in the upper cell of the “Annual Total” column (just below the title “Annual Total”) so as to determine the total annual cost as a function of the membership type from the table figuring under the membership list. This can be done using a VLOOKUP function (simpler) or a combination of IF functions (more complicated). If you are using the VLOOKUP function, do not forget that the first column of the lookup table must be sorted alphabetically. (Do not mix up the lookup table with the data table! I am not asking you to sort the data alphabetically!)
  4. Put another function in the upper cell of the “Down Payment”column so as to determine the downpayment amount as a function of the membership type, using the same lookup tablefiguring under the membership list. This also can be done with a VLOOKUP or a combination of IF functions.
  5. Put a function in the upper cell of the “Locker Fee” column so as to determine the locker fee as a function of the membership type from the same tablefiguring under the membership list. This also can be done with a VLOOKUP or a combination of IF functions.
  6. Put a function in the upper cell of the “Annual Total” column so that if the person rents a locker, the Annual Total equals Annual Cost (already calculated) + Locker Fee, and if he or she does not, it simply equals Annual Cost. This function is easiest implemented as an IF function.
  7. Put in the upper cell of the“Total Due” column the total amount of money due over the membership period, i.e. Annual Total times the number of years of membership.
  8. The Balance in the “Balance” column is Total Due minus Down Payment.
  9. The interest rate in the “Interest Rate” column can be obtained by a VLOOKUP function (or a combination of IF functions) from the table figuring at the bottom of the data sheet. It is determined by the Income Level from the “Income Level” column.
  10. And finally, the Monthly Payment in the upper cell of the “Monthly Payment” column must be given by a PMT function. The present value is equal to Balance (with a minus sign!), and you have already obtained the annual interest rate and you know the number of years during which you must pay. Pay attention to the fact that it is a monthly payment, not yearly! The number of periods is not the number of years, and the monthly interest rate is not equal to the annual interest rate.
  11. Replace the last name of the last customer by your last name.
  12. If you transformed the data into an Excel table, it is filled automatically. If you did not, copy the formulas (but careful – do NOTcopy the values, like Income Levels!) to all remaining rows of the membership list. When you do so, pay attention to the fact that the two lookuptables under the data sheet should be either referred to by an absolute reference (with dollar signs), or be named and referred to by their names.
  13. Now you can put the statistics: the totals(for the columns for which the totals make sense) and the Summary statistics below the list.
  14. Format the table according to your taste, but in any case making sure that
  15. all dollar amounts are expressed in dollars, with no decimal signs for any amount except for monthly payment and Summary Statistics where you display the cents;
  16. all interest rates are expressed as percentages (%)
  17. all columns have a width appropriate for what figures there.

I can show how I formatted the list (except that I am not showing how I formatted the lookup tables). Your formatting can be quite different.In this picture, I pretended there are just eight customers to keep the picture small.

  1. Name the sheet “Customers”, and freeze the panes so that the column headers are always visible.
  2. Make a copy of the sheet, put it on the right of the Customers sheet, and name it Customers by Membership type.
  3. In the new sheet, sort first by Membership type, then by Last name.
  4. Create subtotals as averages for each Membership type. Choose which averages make sense in your opinion. Do not forget that you cannot use a subtotals feature in an Excel table. Collapse the resulting outline so as to have only the averages.
  5. Suppose that all the Gym’s current expenses are covered in some other way, and all the sums (monthly payments) obtained from the members, taken together, are invested at a bank at an annual interest rate of 10%, compounded monthly. On another sheet, which you’ll call Gym’s Investments, put a table showing how much the Gym has at the end of each month during a year. Compare with what it would accumulate without the interest.

Here’s an example of such table:

Part 2

You know how to do PivotTables. Create tables like that (not necessarily exactly like that, you can be inventive, but make sure your tables are interpretable; the numbers in these examples may have nothing to do with the numbers in the table I printed above, I am just showing how it can look).

Produce graphs from thetables you obtained but retain only those graphs that are reasonable (i.e. can be easily read and interpreted). Some pivot tables, though perfectly informative, result in graphs which are more difficult to read than the tables that are their origin.

Excel Test1/3March19, 2013