Reference & Financial Functions Worksheet Name: _________________ Lab Day/Time: _____________ . Orders Inventory Warranty Types You own a small Computer Retailing company that sells computer systems. You have created a spreadsheet that stores basic sales information. Inventory: Lists the computer & upgrade costs for each system identified by product number and product name. Order: Lists basic customer information such as, their name, computer purchased, and warranty cost (if chosen) by the customer. Warranty Types: Lists the warranty costs. The cost of the warranty is based on the type of warranty chosen and the total computer cost in column G of the Inventory worksheet, (Computer +Upgrades). The cell range B2:E4 has been named Warranty. The warranty costs are based on whether or not the customer purchased the warranty and the cost of the computer as follows. If the customer purchased the Extended 3 Year Warranty Only If the cost of the cost of the computer is less than $1,000, the cost of the warranty is $25. If the cost of the computer is greater than or equal to $1,000 and less than $2,000, the cost of the warranty is $35. If the cost of the computer is greater than or equal to $2,000 and less than $5,000, the cost of the warranty is $45. If the cost of the computer is greater than or equal to $5,000, the cost of the warranty is $60. If the customer purchased the Extended 3 Year Warranty and Accidental Damage or Thef If the cost of the computer is less than $1,000, the cost of the warranty is $40. If the cost of the computer is greater than or equal to $1,000 and less than $2,000, the cost of the warranty is $55. If the cost of the computer is greater than or equal to $2,000 and less than $5,000, the cost of the warranty is $70. If the cost of the computer is greater than or equal to $5,000, the cost of the warranty is $80. Page | 1 Reference & Financial Functions Worksheet 1. Write an Excel formula in cell Orders!D2, which can be copied down the column to determine the corresponding computer name based on the product number in the Inventory worksheet. If the product number does not exist in the inventory, display the word, \"INVALID\". 2. Write an Excel formula in cell Orders!G2, which can be copied down the column, to determine the corresponding computer cost based on the product number in the Inventory worksheet. (Assume all clients will purchase the computer and the associated upgrades.) If the product number does not exist in the inventory, display the word, \"INVALID\". 3. Write an Excel formula in cell Orders!H2, which can be copied down the column, to determine the warranty cost of the computer based on the warranty type (if chosen) and the cost of the computer. If the product number does not exist in the inventory, display a 0 in the cell. (Use the named range Warranty in your formula.) Note: If the customer chooses to purchase a warranty, they can only select one of the options. 4. Write an Excel formula in cell Orders!I2, which can be copied down the column, to determine the total cost of the purchase. (Total Computer Cost + Warranty) Page | 2 Reference & Financial Functions Worksheet Trips Comm Pricing Page | 3 Reference & Financial Functions Worksheet You are travel agent and are using an Excel workbook to track trip information for each trip your clients book with your company. Trips: This worksheet contains basic cost information about each trip, including the wholesale price, whether or not (T/F) the client paid in advance, the selected package (A, B, C, D), the type of trip (Luxury, All Inclusive, Basic), the selected ground transportation (Car, Shuttle, Taxi), and the number of people going on the trip. The retail price, agent's commission and ground cost will be calculated based on the items previously listed. (All costs are based on trips originating from Port Columbus.) Comm: This worksheet lists the agent's commission, which is based on whether or not the client will pay for the trip in advance, and the type of trip selected. (Luxury, All Inclusive, Basic) For example, if the client pays for the trip in advance (pre pay) and the trip type is Luxury, the travel agent will receive a 6% commission. (The commission is based on the retail price of the trip, excluding the ground transportation cost.) Pricing: This worksheet contains basic pricing information used in the calculation of the retail cost of the trip. It includes the wholesale markup, the discount for packages A and B, and the cost of the ground transportation. The retail price of the trip (wholesale price + markup + Agent's commission) is based on the Mark Up table. Trips with a wholesale price less than $500 will have a markup of $50, trips with a wholesale price greater than or equal to $500, but less than $700 will have a markup of $75, trips with a wholesale price greater than or equal to $700, but less than $1,500 will have a markup of $115, trips with a wholesale price greater than or equal to $1,500, will have a markup of $150. (The markup is added to the wholesale price and is used when calculating the retail price of the vacation.) For example, the wholesale price of the trip in cell Trips!C3 is $850, so the markup for that trip will be $115. Package A and Package B trips receive a discount based on the type of trip selected. If the trip type is luxury, the discount is 7% of the wholesale price, if the trip is all inclusive, the discount is 6% of the wholesale price, and if the trip is basic, the discount is 5% of the wholesale price. The cost of the ground transportation is based on the trip location and the type of transportation. (Car, Taxi, Shuttle) For example, the transportation cost for a car in Boston is $250. Notes All calculations and discounts should be calculated per person first and then multiplied by the number of people going on the trip. All clients will always choose a valid package, trip type, type of transportation, and destination. Meaning, all information on the Trips worksheet will be in each respective table. The range Pricing!A3:B6 is named MarkUp. The range Pricing!B2:D4 is named Commission. o Please use the named ranges in formulas where appropriate. . Page | 4 Reference & Financial Functions Worksheet 1. Write an Excel formula in cell Trips!I3, which can be copied down the column, to determine the retail price of the trip. The retail price is based on the wholesale price, the mark up, the agent's commission, and the number of people going on the trip. (Use the named ranges MarkUp & Commission in your function.) 2. Write an Excel formula in cell Trips!J3, which can be copied down the column, to determine the cost of the ground transportation for the corresponding trip. 3. Write an Excel formula in cell Trips!K3, which can be copied down the column, to calculate the trip discount for the corresponding trip (if any). 4. Write an Excel formula in cell Trips!L3, which can be copied down the column, to determine the total cost of corresponding trip. Page | 5 Reference & Financial Functions Worksheet Above is a spreadsheet analyzing several possible car financing options for purchasing the respective vehicle. For each of the different car scenarios, you will be asked to calculate either the loan payment, selling price, loan duration, interest rate, future value, or present value. You should assume the loan will be paid off in all the scenarios. (FV=0). Use cell references wherever possible. 1 Page | 6 Reference & Financial Functions Worksheet 1. Based on the values in row 3, write an Excel formula in cell H3 to determine the monthly payment of the Ford Taurus. 2. Based on the values in row 4, write an Excel formula in cell F4 to determine how many years it will take to pay off the loan for the Thunderbird. 3 Based on the values in row 5, write an Excel formula in cell C5 to calculate the annual percentage rate of the loan for the Ford Windstar. 4 The salesman has told you that for only $320 a month and no money down you can own a Ford Mustang. The salesman, however, failed to mention the actual selling price of the vehicle. Write an Excel formula in cell E6 to calculate the selling price of this vehicle. (Note: Because the down payment is 0, you do not need to factor in a down payment.) 5 Your great uncle Zeus has died unexpectedly and left you a bank CD in his will. The CD was purchased 10 years ago for $12,000 and pays 6% per year compounded annually. Write a formula in cell J3, which can be copied down the column, to determine (True/False) if you have enough money to purchase the corresponding car without any financing. Page | 7