OPIM3103 hw1
a. (5 points) Download the homework Excel data file and save the downloaded file as fac18002HW1.xlsx.
The Excel file contains three worksheets, “Name and ID”, “Stacy’s Travel”, and “Salary”. b. (5 points) Enter your name and NetID in the “Name and ID” sheet;
For sheet “Stacy’s Travel”, you are required to track all of the winter holiday specials that you sell as an agent at Stacy’s Travel Agency. Customers can purchase a package with or without airfare (flight). You are required to collect from each customer a down payment which is based on 15% of his/her total purchase amount. All customers are required to pay his/her balance after down payment on a monthly payment plan for one year, which you calculate based on a standard interest rate (shown in cell B3).
c. (5 points) Assign the name “Specials” to range A16:C20;
d. (5 points) Insert a function to display the current date in cell B2;
e. (5 points) Use VLOOKUP function in column E for all customers to display the package cost for each
customer, based on their selected package (inside your VLOOKUP function, use range name
“Specials” you defined in c. above);
f. (10 points) In column F, use a combination of IF and VLOOKUP function to decide whether a
customer’s flight cost should be 0 (if the customer does not need a flight) or find and display the corresponding flight cost for the customer based on his/her selected package, again, use range name “Specials” you defined in c. above;
g. (5 points) In column G, for each customer, calculate the total cost of the purchase;
h. (5 points) In column H, for each customer, create a function to show the amount of down payment;
i. (5 points) In column I, for each customer, create a function to display the balance, which is the
difference between the total purchase cost and the down payment;
j. (5 points) In column J, for each customer, create a function to calculate the customer’s monthly
payment, using appropriate relative and absolute cell references. Monthly payments are based on 12 months and the standard interest rate displayed in cell B3 (use cell address, do not directly type this interest rate in your formula);
k. (5 points) In cell J16, use COUNIF function to display the total number of customers who need flight;
l. (5 points) In cell J17, use a combination of the functions MAX, INDEX, and MATCH to display the
customer name with the highest total purchase cost;
m. (5 points) Format all monetary values to be currency (USD) with 2 decimal digits (if you choose
accounting format, the cells with 0 flight cost will automatically change to “$ -“, which is ok);
n. (5 points) Create a 3-D Pie Chart (position it anywhere on the sheet) to display the Total Purchase Cost (in terms of %) for each customer. The chart looks similar to the following. Use Chart Style 4.
The sheet “Salary” stores employee information for a company. Your job is to help the company achieve the following tasks:
o. (5 points) Insert a “Pay Grade” column between Salary and Performance. Create a formula for the Pay Grade column to decide whether an employee’s salary is either “High” or “Low”. If an employee’s salary is greater than or equal to the average salary of all employees, the pay grade is “High”, otherwise it’s “Low”. Use a formula to do this, do not manually enter “High” or “Low” for each employee;
p. (10 points) Design a mechanism for the user to find a particular employee by enter her ID. See the screenshot below.
The user enters an employee ID into cell L2, then cells L4, L5, L6, L7, and L8 should display the employee’s first name, last name, gender, pay grade, and performance. Note: you can decide to use either Vlookup or a combination of Index and Match to design this search mechanism;
q. (10 points) Continue from p above, in cell L10 create a function to decide whether the found employee deserves a raise. The criteria is, if the employee’s Pay Grade is “Low”, and the Performance is “Excellent”, then the employee deserves a raise (put a “Yes” here). Otherwise, no raise (put a “No” here).
