Please show formulas as well, thank you

Your domestic organization, headquartered in Nashville, TN has job opportunities in several cities. You need a model that compares buying power adjusted for cost of living differences for the different locations. To put it simply a dollar won't buy as much in New York City as it does in Nashville because of basic cost of living differences such as housing, food, entertainment, prevailing local transportation needs, etc. Your task is to help employees compare the relative financial status of various job locations. Your model should use Nashville as the baseline city for comparison since it's your headquarters. The different locations, along with their cost of living multiplier are: Atlanta, Chicago, Dallas, Indianapolis, and New York - with Nashville, as the baseline value. Some locations will offer a one-year, start-up bonus - New York, 6,000 bonus and Chicago, \$2,500 bonus. Assume all locations will receive a 3.5% pay raise after the first year of employment. You have documented each position in an Excel worksheet. On this sheet, you have recorded the positions, the annual salary, the cost of living multiplier (which you obtained from a website) and estimates of a monthly car payment including insurance, assuming for some of these jobs the employee will need to purchase a car. You have also recorded information regarding bonuses (a one-time payment when hired) offered to the employee. Now, you need to finalize the worksheet. Complete the following: 1. Open the Job Market.xlsx file and then save it as Job Comps.xlsx. 2. In cells D2:D7, calculate the associated annual adjusted salary. This adjusted salary is the starting annual salary divided by the cost of living adjustment. 3. In cell D10, write a formula to calculate the average adjusted salary of all of the positions 4. In cell D11, write a formula to determine the value of the lowest adjusted salary. This formula should automatically update if any of the data inputs are later changed. 5. In cell D12, write a formula to determine the value of the highest adjusted salary. This formula should automatically update if any of the data inputs are later changed. 6. In cell E8, write a formula to determine the number of positions that include a bonus. 7. In cells G2:G7, calculate the value of the adjusted financial package accounting for a possible bonus and car expenses (note the car expenses listed are monthly). 8. In cells H2:H7, calculate the value of the adjusted financial package for each position over a two-year period, including bonuses. Assume that the employee will receive a 3.5% raise after the first year of employment in the new position, however the raise amount may change. 9. Display all dollar values without cents, using appropriate formatting per course guidelines. Format the cost of living multipliers with two decimal places displayed, and align these values on the decimal point. For items 10, 11, and 12 use the appropriate function to allow for values to update dynamically. 10. In cells 12:17 display the rank of the adjusted salary (based on values in D2:D7). 11. In cells J2:J7 display the rank of the year 1 adjusted net compensation (based on values in G2:G7. 12. In cells K2:K7 display the rank of the net adjusted year 2 financial package (based on values in H2:H7. 13. Use Conditional Formatting to dynamically fill the lowest value (e.g. 1) green (the lowest rank value is the most desirable or highest salary) and the highest value (e.g. 6) with red (the highest rank value is the least desirable or lowest salary). For each of the rank columns (e.g. 12:17, J2:J7, and K2:K7). Note, this will require two rules; don't use a solution that uses a scale on the colors. 14. Save and close the Job Comps .xlsx workbook. Check Figures A Little Help - Occasionally I will provide "check figure" values to give you "A Little Help." They are calculated values, therefore do not simply enter the value directly into the cell. Check Figures Atlanta Net Adjusted Year 1=$70,455 Chicago Net Adjusted Year 1=$70,800 New York ranks 6|4|6 Average =$69,171 \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|} \hline & A & C & D & E & F & G & H & I & J & K \\ \hline 1 & \begin{tabular}{c} Starting \\ Annual \\ Salary \end{tabular} & \begin{tabular}{c} Cost of Living \\ Adjustment \end{tabular} & \begin{tabular}{c} Adjusted \\ Starting \\ Salary \end{tabular} & Bonus & \begin{tabular}{c} Monthly Car \\ Expenses \end{tabular} & \begin{tabular}{c} Net \\ Adjusted \\ Year 1 \\ Financial \\ Package \end{tabular} & \begin{tabular}{c} Net \\ Adjusted 2 \\ Year \\ Financial \\ Package \end{tabular} & \begin{tabular}{c} Initial \\ Annual \\ Adjusted \\ Salary \\ Rank \end{tabular} & \begin{tabular}{l} Rank of Year 1 \\ Adjusted Net \\ Compensation \end{tabular} & \begin{tabular}{c} Rank of Net \\ Adjusted 2 \\ Year \\ Financial \\ Package \end{tabular} \\ \hline 2 & Atlanta & 1.10 & & & $425 & & & & & \\ \hline 3 & Chicago & 1.25 & & $2,500 & $450 & & & & & \\ \hline 4 & $75,250 & 1.10 & & & $375 & & & & & \\ \hline 5 & Indianapolis & 0.95 & & & $375 & & & & & \\ \hline 6 & Nashville & 1.00 & & & $350 & & & & & \\ \hline 7 & New York & 1.75 & & $6,000 & $0 & & & & & \\ \hline 8 & & \# Locations wit & Bonus & & & & & & & \\ \hline 9 & Assumptions & Adjusted Sala & y Summary & & & & & & & \\ \hline 10 & Raise \% & Average Salary & & & & & & & & \\ \hline 11 & & Low Salary & & & & & & & & \\ \hline 12 & & High Salary & & & & & & & & \\ \hline \end{tabular} Your domestic organization, headquartered in Nashville, TN has job opportunities in several cities. You need a model that compares buying power adjusted for cost of living differences for the different locations. To put it simply a dollar won't buy as much in New York City as it does in Nashville because of basic cost of living differences such as housing, food, entertainment, prevailing local transportation needs, etc. Your task is to help employees compare the relative financial status of various job locations. Your model should use Nashville as the baseline city for comparison since it's your headquarters. The different locations, along with their cost of living multiplier are: Atlanta, Chicago, Dallas, Indianapolis, and New York - with Nashville, as the baseline value. Some locations will offer a one-year, start-up bonus - New York, 6,000 bonus and Chicago, \$2,500 bonus. Assume all locations will receive a 3.5% pay raise after the first year of employment. You have documented each position in an Excel worksheet. On this sheet, you have recorded the positions, the annual salary, the cost of living multiplier (which you obtained from a website) and estimates of a monthly car payment including insurance, assuming for some of these jobs the employee will need to purchase a car. You have also recorded information regarding bonuses (a one-time payment when hired) offered to the employee. Now, you need to finalize the worksheet. Complete the following: 1. Open the Job Market.xlsx file and then save it as Job Comps.xlsx. 2. In cells D2:D7, calculate the associated annual adjusted salary. This adjusted salary is the starting annual salary divided by the cost of living adjustment. 3. In cell D10, write a formula to calculate the average adjusted salary of all of the positions 4. In cell D11, write a formula to determine the value of the lowest adjusted salary. This formula should automatically update if any of the data inputs are later changed. 5. In cell D12, write a formula to determine the value of the highest adjusted salary. This formula should automatically update if any of the data inputs are later changed. 6. In cell E8, write a formula to determine the number of positions that include a bonus. 7. In cells G2:G7, calculate the value of the adjusted financial package accounting for a possible bonus and car expenses (note the car expenses listed are monthly). 8. In cells H2:H7, calculate the value of the adjusted financial package for each position over a two-year period, including bonuses. Assume that the employee will receive a 3.5% raise after the first year of employment in the new position, however the raise amount may change. 9. Display all dollar values without cents, using appropriate formatting per course guidelines. Format the cost of living multipliers with two decimal places displayed, and align these values on the decimal point. For items 10, 11, and 12 use the appropriate function to allow for values to update dynamically. 10. In cells 12:17 display the rank of the adjusted salary (based on values in D2:D7). 11. In cells J2:J7 display the rank of the year 1 adjusted net compensation (based on values in G2:G7. 12. In cells K2:K7 display the rank of the net adjusted year 2 financial package (based on values in H2:H7. 13. Use Conditional Formatting to dynamically fill the lowest value (e.g. 1) green (the lowest rank value is the most desirable or highest salary) and the highest value (e.g. 6) with red (the highest rank value is the least desirable or lowest salary). For each of the rank columns (e.g. 12:17, J2:J7, and K2:K7). Note, this will require two rules; don't use a solution that uses a scale on the colors. 14. Save and close the Job Comps .xlsx workbook. Check Figures A Little Help - Occasionally I will provide "check figure" values to give you "A Little Help." They are calculated values, therefore do not simply enter the value directly into the cell. Check Figures Atlanta Net Adjusted Year 1=$70,455 Chicago Net Adjusted Year 1=$70,800 New York ranks 6|4|6 Average =$69,171 \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|} \hline & A & C & D & E & F & G & H & I & J & K \\ \hline 1 & \begin{tabular}{c} Starting \\ Annual \\ Salary \end{tabular} & \begin{tabular}{c} Cost of Living \\ Adjustment \end{tabular} & \begin{tabular}{c} Adjusted \\ Starting \\ Salary \end{tabular} & Bonus & \begin{tabular}{c} Monthly Car \\ Expenses \end{tabular} & \begin{tabular}{c} Net \\ Adjusted \\ Year 1 \\ Financial \\ Package \end{tabular} & \begin{tabular}{c} Net \\ Adjusted 2 \\ Year \\ Financial \\ Package \end{tabular} & \begin{tabular}{c} Initial \\ Annual \\ Adjusted \\ Salary \\ Rank \end{tabular} & \begin{tabular}{l} Rank of Year 1 \\ Adjusted Net \\ Compensation \end{tabular} & \begin{tabular}{c} Rank of Net \\ Adjusted 2 \\ Year \\ Financial \\ Package \end{tabular} \\ \hline 2 & Atlanta & 1.10 & & & $425 & & & & & \\ \hline 3 & Chicago & 1.25 & & $2,500 & $450 & & & & & \\ \hline 4 & $75,250 & 1.10 & & & $375 & & & & & \\ \hline 5 & Indianapolis & 0.95 & & & $375 & & & & & \\ \hline 6 & Nashville & 1.00 & & & $350 & & & & & \\ \hline 7 & New York & 1.75 & & $6,000 & $0 & & & & & \\ \hline 8 & & \# Locations wit & Bonus & & & & & & & \\ \hline 9 & Assumptions & Adjusted Sala & y Summary & & & & & & & \\ \hline 10 & Raise \% & Average Salary & & & & & & & & \\ \hline 11 & & Low Salary & & & & & & & & \\ \hline 12 & & High Salary & & & & & & & & \\ \hline \end{tabular}