Need help with this excel assignment please.

Please note that you will be allowed to submit this assignment 3 times into MyITLab. It will record the highest score of your 3 attempts. I realize that these formulas can be done multiple different ways. For each of these formulas I have included 4-5 different variations that I think would work. If you have a formula that gives you the right answer but MyITLab marks you off for it then I apologize for not thinking of your version. If you think yours should be included as a correct version please email it to me and I will consider adding it to the mix. Regardless, you can still look at your grading feedback comments for a hint on which formula I thought was "best". As part of its product line, Armstrong Motor Works (AMW) has nine basic models, each with different options and features. Although many car buyers are concerned about the initial cost of a car, customers are also becoming increasingly concerned about the yearly operating expenses in order to help to determine their total cost of ownership (TCO). They frequently want to compare two or more purchase options to see how much a car costs to operate each year. Although they might be willing to spend an extra $5,000 to purchase a sports utility vehicle or luxury car with many options, customers might reconsider when they calculate the annual cost of depreciation, gas, maintenance and insurance. For instance, did you know that for the average new car you can expect to lose 20-25% of the value in the first year? That means that if you were to buy a $20,000 upon graduation and drove it ~15,000 miles for the first year, your car would be worth $15,000 on it's first birthday (source: edmunds.com). You might as well be rolling down your window every Monday on your way to work and tossing out a $100 bill onto the pavement! Don't get me wrong, I want you to have a new car but only a few years down the road when you throwing away 100 is a minor part of your net worth. Get a 2-3 year old used car for your first 5-10 years. ... but I digress... The fuel economy information posted on each cars window is usually stated as an approximate expectation for city and highway driving. For example, AMWs compact car, the Speck Hybrid, lists 35 miles per gallon in the city and 42 miles per gallon on the highway. This and other car sticker information is difficult to translate into annual costs for gasoline, maintenance, insurance, and other operating costs. Many AMW dealerships have asked for an easy way to provide actual operating cost information to their customers, much the same way they can give them base car costs and option prices. To this end, AMW has decided to develop an Excel workbook application that can calculate costs associated with the first three years of operating a new car, including gas expenses, maintenance, and insurance premiums. Although these costs are estimates, they will give customers a good understanding of what to expect. For your benefit, the marketing dept. has provided you a mockup of what they want this form to look like. It is included in your file and is called Car Operating Cost Estimate Form that is on the "TCO Estimate" sheet. Your task is to work with this form and develop it into spreadsheet that salespeople can complete to help their customers calculate annual operating costs for one to five vehicles. AMW completed a preliminary analysis summarizing the data required from the salespeople, the information needed for the calculations, and the desired data outputs. After the user enters in the customers data into the blue cells (I4 through I8) and what cars and their engine sizes that they want to compare, the estimate form should do all of the work compare the operating costs of the requested vehicles. When this form is complete, it should display the data inputs (shaded in light blue) the outputs generated by your awesome formulas and provide space to compare up to five vehicles. If fewer than five vehicles are listed, blank cells should be displayed in lieu of ugly error messages. This means that you must have formulas in all cells for all 5 models! Customer Information Blue Data Inputs on top of Estimate Form Users should be able to enter the following data about the customer and use the test customer profile for purposes of calculations: -Expected number of driving miles per year -Type of driving: HW (speeds of 65 mph and over), City (speeds of 45 mph and below), or Combo (balance of highway and city driving) State of residence -Driving safety record: Excellent, Average, or Poor Test Customer Profile In the blue data input fields initially put in the following sample data (after you get everything working you should experiment by putting in different values to make sure that everything works but before you turn it in please put these values back in for grading purposes): - Expected number of driving miles per year (Cell I4): 25,000 - Type of driving (Cell I5): Combo - Purchasers state (Cell I6) : IL - Purchasers driving safety record (Cell I8): Average - Region (Cell I9)- Do not put anything into this cell for now. You will use a VLOOKUP formula here in a later step that brings the region number from the States table onto this form after the user enters in the 2 letter state code into cell I6. Based upon the above, the auto-grader will be looking for certain values to be calculated by your awesome form. Ultimately, several of your formulas on this page will require region number. Whenever they do, just have them reference this cell (I9). Vehicle Inputs Vehicle Inputs (Model # and # Cylinders) on Bottom Half of Estimate Form In the Estimate Form worksheet, users should enter the following data about each vehicle (the worksheet is designed to function for up to five vehicles): - Vehicle model number (user inputs should be 1-9 corresponding to the 9 available models on the Models worksheet) - Engine: number of cylinders (4, 6, 8, or turbo) Test Vehicles Profile In the fields that you generated above put in the following sample data: 1st Line (Row 11): Model=1 & #Cylinders = 4 2nd Line (Row 12): Model=4 & #Cylinders = 6 3rd Line (Row 13): Model=5 & #Cylinders = 8 4th Line (Row 14): Model=7 & #Cylinders = turbo 5th Line (Row 15): leave blank (but there should be formulas in this row in columns B, C, E, F, G, H, and I. If you did step 6 correctly this row should not produce any formula errors when you leave A15 & D15 blank) Data Tables Provided in Total Cost of Ownership.xlsx Workbook Data for car models, mileage, regions, and gas pricing that will be required to obtain the necessary output is available on the following worksheets. Named ranges have already been included for your usage. By naming your ranges, this will simplify your formulas and allow you to look at them and more easily make sense of them. For instance, you could have a VLOOKUP formula that looks up the value from cell A11 in the cells of the table A4:L12 on the Models worksheet and it would look like: VLOOKUP(A11,Models!A4:L12,2,FALSE) However, if you name that cell range with the name of MODELS (I actually did it for you on this assignment) then the formula looks much cleaner: VLOOKUP(A11,MODELS,2,FALSE) Sheets included in the spreadsheet and a brief description of what is included on each reference sheet. ModelsThis worksheet lists available car models including list price, car model, description, expected depreciation values, weight class, engine options, and expected maintenance base costs for the first three years (excluding oil changes). For some models these maintenance costs are zero, reflecting dealer incentives to buyers that include free maintenance over a specified period. Also included on this worksheet is the number of recommended miles per oil change and the cost per oil change. MileageThis worksheet includes the gas mileage schedules for each driving usage type. Each schedule lists the gas mileage in miles per gallon based on the weight class and engine. StatesThis worksheet lists states and their associated regions. GasPricesThis worksheet includes regional average gas prices Estimate Form Outputs The Estimate Form worksheet should include formulas in columns, B, C, E, F, G, H & I that calculate the following outputs (for each car model) for up to five selected cars:	2
2	Create formulas in B11 and C11 that render the car model and description based on model number input (This could use a VLOOKUP formula). Copy these formulas down. Make sure to "fix" these cells so that they do not display an error message if there is no model number provided in column A for that line of the form.	5
3	For Column E: Estimated average annual maintenance cost for the first three years of operation, based on the selected car model and annual miles traveled. This should be the average of the 1st, 2nd and 3rd year base maintenance costs PLUS the cost of the required number of oil changes for 1 year. Your oil change cost portion of the formula should take into account the number of expected driving miles from the user-provided customer data input along with the Oil Change Information cells on the Models worksheet. Please note that it is impossible to have a fraction of an oil change so you'll need to ROUND that portion of your formula. Make sure to "fix" these cells so that they do not display an error message if there is no model number provided in column A for that line of the form. Formatting for ALL remaining steps: as we learned in Module 1 all fields that contain money should be formatted as Currency with 0 decimal places with the $ sign showing on the top row only. Hint: For ROUNDing your oil changes, which flavor of the ROUND formula (ROUND, ROUNDDOWN, ROUNDUP) makes sense? In the above example, 29,000 / 5,000 would be 4.8 right? Can you have 4.8 oil changes in a year? No. So do we ROUND? If you were driving this car and you got to the end of the year and you were still 1,000 miles away from the next 6,000 mile checkup would you run out and get one just to make it an even 5 per year? Not unless you have more money than sense! So ROUND doesn't fit our needs. That leaves ROUNDUP & ROUNDDOWN. Which one gives you the right number of oil changes?	11.5
4	For Column F: Estimated annual cost of gas based on the selected type of driving, weight class, annual miles traveled, gas price, and engine type (The easiest way to do this is to use and INDEX function with embedded VLOOKUP and MATCH functions). Make sure to "fix" these cells so that they do not display an error message if there is no model number provided in column A for that line of the form. Hint: A version of this step is demonstrated in the optional F2F lecture capture from a last semester at around the 42:00 minute mark.	11.5
5	For Column G: Estimated average depreciation over the 3 years that the customer will own the car. Make sure to "fix" these cells so that they do not display an error message if there is no model number provided in column A for that line of the form.	9.5
6	For Column H: Estimated annual insurance premium based on the owners driving record. Insurance rates vary widely across cities, driver age, credit rating and a host of other variables so for simplicity sake, the owners own self-reported driving record will determine the insurance premium based upon the following table. Self-Reported Driving Record - Estimated Annual Insurance Premium Excellent - $1,400 Average - $1,800 Poor - $2,200 Referencing cells K3:L5 Use either a nested IF statement or a VLOOKUP function to render these values into Column H.	8
7	For Column I: Create a formula that calculates the Annual cost of operation per year that adds the Maintenance, Gas, Depreciation, and Insurance values calculated in the previous arduous steps.	2.5
8	Make sure the formulas you enter can be copied to other cells in the same column as needed for each model selected. Your formulas should also be flexible enough to easily accommodate anticipated changes to this data. For instance, if you change the state, the number of cylinders, etc. does the spreadsheet still display the proper operating costs? The form should work without displaying error messages where there are no models. For example, if either the model number or the # of Cylinders are blank, the formulas should leave the description or any of the other cells with your awesome formulas blank. Also, use named ranges to simplify your formulas. You can create intermediate calculations in cells out to the right of the input form and/or additional worksheets, as needed, to "develop" this form but you will need to consolidate all of your intermediate calculation formulas into the one cell prior to submitting. Be certain your Estimate Form is easy to read and use. Highlight the data inputs so the user can easily recognize what is needed to complete the form. Be sure to test your data to verify that the Estimate Form works for different combinations of buyer profiles and cars but prior to submission put it back into the state that is referenced in step 1.	0