Hello

I am presently stuck on Chapter 5 level 3-Creating a Cost Estimate Form for CKG Auto of the textbookSucceeding in Business with Microsoft Excel 2013.

Please provide me Step by Step Solution for that.

Below is the problem

" As part of its product line, CKG Auto 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 they can expect. They frequently want to compare two or more purchase options to see how much a car costs to run 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 gas and insurance.

Fuel economy information posted on each cars window is usually stated within a range of 5 to 10 miles per gallon. For example, CKG Autos compact car, the Voltage, lists 30-35 miles per gallon in the city and 37-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 CKG Auto dealerships have asked for an easy way to provide operating cost information to their customers, much the same way they can give them base car costs and option prices.

To this end, CKG Auto has decided to develop an Excel workbook 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.

Your task is to work with the Costs.xlsx workbook, which is located in the Chapter 5 folder. Open the workbook and save it as CKG Operating Costs.xlsx. Develop a worksheet named Estimate Form that salespeople can complete to help their customers calculate annual operating costs for a selected vehicle. CKG Auto completed a preliminary analysis summarizing the data required from the salespeople, the information needed for the calculations, and the desired data outputs. The estimate form should compare the operating costs of the selected vehicles. When this form is complete, it should display the data inputs and outputs, and provide space to compare up to five vehicles. If less than five vehicles are listed, blank cells should be displayed in lieu of error messages."

Salespeople will enter the following data about the customer and use the data provided in the Test Customer Profile for purposes of calculations:

• Expected number of driving miles per year
• Type of driving: Highway (speeds of 55 mph and over), Mixed (balance of highway and city driving), or City (speeds of 45 mph and below)
• State of residence
• Residential status: City, Suburban, or Rural
• Driving safety record: Excellent, Average, or Poor
• Gas price adjustment percentage, which is a multiplier that accounts for major changes in gas prices (to calculate this multiplier, divide the regular gas price at your local gas station by 4)

Test Customer Profile

• Expected number of driving miles per year: 22,500
• Type of driving: Mixed
• Purchasers state: CA
• Purchasers residential status: City
• Purchasers driving safety record: Excellent
• Gas price adjustment percentage (to calculate the percentage, divide the current gas price in your neighborhood by 4.0).

Vehicle Inputs Input on Estimate Form

In the Estimate Form worksheet, salespeople will enter the following data about each vehicle (design the worksheet for up to five vehicles):

• Vehicle model number
• Engine: number of cylinders (4, 6, 8, or turbo)

Test Vehicles Profile

• Model 2: 4 cylinders
• Model 3: 6 cylinders
• Model 6: 8 cylinders
• Model 9: turbo

Data Tables Provided in Costs.xlsx Workbook

Data for car models, mileage, regions, gas pricing, and insurance that will be required to obtain the necessary output is available in the Costs.xlsx workbook on the following worksheets:

• Models, which lists available car models including description, available engines, weight class, and expected maintenance base costs for the TEXTBOOK SOLUTIONS first three years EXPERT Q&A (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 is the number of recommended miles per oil change and the cost per oil change.
• Mileage, which 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.
• States, which lists states and their associated regions. (Use Region to determine gas pricing.)
• Gas Prices, which includes regional yearly average gas prices based on $4 per gallon in the Northeast. This value should be adjusted based on the multiplier supplied as part of the user inputs (current gas price/$4), assuming prices change proportionally throughout the country. A multiplier of greater than 1 increases the price, and a multiplier of less than 1 reduces the price. The estimated $/gallon is equal to the $/gallon from the Gas Prices table (which varies by region) times this multiplier.
• Insurance, which lists regional insurance rate estimates for new cars. These base prices can then be adjusted based on driver safety records and residential status.
• Create appropriate named ranges on the worksheets as needed.
• Estimate Form Outputs
• The Estimate Form worksheet should include the following outputs (for each car model) for up to five selected cars:
• Car make and description based on model number input
• Estimated annual cost of gas based on the selected type of driving, weight class, annual miles traveled, gas price, and engine type
• Estimated annual insurance premium based on the owners region of residence, driving record, and residential status (Hint: Include an intermediate calculation in the form that looks up the region number associated with the state of residence, and then refer to the cell that contains this result in the insurance formula.)
• Estimated average annual maintenance cost for the first three years of operation, based on the selected car model and annual miles traveled (total 3-year maintenance costs divided by 3 plus the cost of 1 year of oil changes); to the base maintenance cost, add $39 per oil change for each 3,500 miles driven
• Estimated total cost of operation per year
• 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. The form should work without displaying error messages where there are no models. For example, if the model number is blank, the formulas should leave the description field blank. Use named ranges to simplify your formulas

Create intermediate calculations (formulas) and insert worksheets, as needed, to develop this form. Format your Estimate Form to make sure that it 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.

(Hint: Some formulas may require several levels of nesting and complex function arguments. It is strongly recommended that you create additional intermediate calculations for values you might use over and over again or to obtain values that might help simplify longer calculations.)

Save and close the CKG Operating Costs.xlsx workbook.

I need help finding a formula for this question.

Thanks & Best Regards.