You have been given the assignment to read Beer School: Bottling Success at The Brooklyn Brewery. Upon completion of reading this informative book dealing with this real life rags to riches entrepreneurial….

## Compute the monthly cost of insurance.

- Open the start file
**EX2019-ChallengeYourself-3-3**. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. - If the workbook opens in Protected View, click the
**Enable Editing**button in the Message Bar at the top of the workbook so you can modify it. - The registration fee information in cells
**B11:C17**on the**Data**sheet will be used in lookup formulas later in this project. Name the range**RegistrationFees**to make it easier to use later. - Return to the
**Purchase**worksheet. - Calculate the average MPG for each vehicle.
- Enter a formula in cell
**C10**using the AVERAGE function to calculate the average value of**C8:C9.**Use only one argument. - Copy the formula to the appropriate cells for the other vehicles.
- Excel will detect a possible error with these formulas. Use the
**SmartTag**to ignore the error.*Hint:*Use the**SmartTag**while cells**C10:F10**are selected and the error will be ignored for all the selected cells.

- Enter a formula in cell
- Calculate the registration fee for each vehicle.
- Enter a formula in cell
**C13**to look up the registration fee for the first vehicle. Use the vehicle type in cell**C4**as the*Lookup_value argument.*Use the**RegistrationFees**named range as the*Table_array*argument. The registration fees are located in column**2**of the data table. Require an exact match. - Copy the formula to the appropriate cells for the other vehicles.

- Enter a formula in cell
- Determine whether or not you will need a loan for each potential purchase.
- In cell
**C15**, enter a formula using an IF function to determine if you need a loan. Your available cash is located on the*Data*sheet in cell**A3**. If the price of the car is less than or equal to your available cash, display**“no”.**If the price of the car is more than your available, cash, display**“yes”.**Use absolute references where appropriate—you will be copying this formula across the row. - Copy the formula to the appropriate cells for the other vehicles.

- In cell
- Calculate how much you would need to borrow for each purchase.
- In cell
**C16**, enter a formula to calculate the price of the vehicle minus your available cash (from cell**A3**in the**Data**worksheet). Use absolute references where appropriate—you will be copying this formula across the row. - Copy the formula to the appropriate cells for the other vehicles.

- In cell
- Calculate the monthly payment amount for each loan.
- In cell
**C20**, enter a formula using the PMT function to calculate the monthly loan payment for the first vehicle.*Hint:*Divide the interest rate by 12 in the*Rate*argument to reflect monthly payments. *Hint:*Multiply the number of years by 12 in the*Nper*argument to reflect the number of monthly payments during the life of the loan.*Hint:*Use a negative value for the loan amount in the*Pv*argument so the payment amount is expressed as a positive number.- Copy the formula to the appropriate cells for the other vehicles.

- In cell
- Compute the monthly cost of gas.
- In cell
**C21**, enter a formula to calculate the number of miles you expect to drive each month. Divide the value of number of miles (cell**A5**from the**Data**sheet) by the average MPG for the vehicle multiplied by the price of a gallon of gas (cell**A6**from the**Data**sheet). - Copy the formula to the appropriate cells for the other vehicles.
- If cells
**D21:F21**display an error or a value of 0, display formulas and check for errors. - If you still can’t find the error, try displaying the precedent arrows.
*Hint:*The references to the cells on the*Data*sheet should use absolute references. If they do not, the formula will update incorrectly when you copy it across the row.

- In cell
- Compute the monthly cost of maintenance.
- In cell
**C22**, enter a formula to calculate the monthly maintenance cost: Divide cell**C12**by 12. - Copy the formula to the appropriate cells for the other vehicles.

- In cell
- Compute the monthly cost of insurance.
- In cell
**C23**, enter a formula to calculate the monthly insurance cost: Divide cell**C14**by 12. - Copy the formula to the appropriate cells for the other vehicles.

- In cell
- In cells
**C24:F24**, compute the total the monthly cost for each vehicle. - Determine which vehicles are affordable.
- In cell
**C26**, enter a formula using the IF function to display**“yes”**if the total monthly cost (cell**C24**) is less than or equal to the total monthly amount available for vehicle expenses (*Data*sheet, cell**A4**). Display**“no”**if the total monthly cost is not less than or equal to the total monthly amount available. - Copy the formula to the appropriate cells for the other vehicles.
- Display formulas and use the error checking skills learned in this lesson to track down and fix any errors.

- In cell
- Complete the Analysis section using formulas with statistical functions. Use named ranges instead of cell references in the formulas.
- Price
- MPG City
- MPG Highway
- Average MPG
- Maintenance/Year
- Registration Fee
- Insurance/Year
- Amount to Borrow
- APR
- Years
- Loan Payment
- Gas
- Maintenance/Month
- Insurance/Month
- Total Monthly

*Hint:*Select cells**B7:F24**and use Excel’s**Create from Selection**command to create named ranges for each row using the labels at the left side of the range as the names.*Hint:*Open the**Name Manager**and review the names Excel created. Notice that any spaces or special characters in the label names are converted to _ characters in the names.*Hint:*To avoid typos as you create each formula, try using Formula AutoComplete to select the correct range name.