Hello Sara,
Your e-mail to John is excellent.
I like the way you have touched several areas that he needs to understand before he starts using the what-if analysis, which is a very vital tool and also called sensitivity analysis, which is a process that changes values in cells of a worksheet and comparing at how such changes will affect the formula in specified cells to aid in decision making since the business will understand the impact that can be caused by changing one or more variables. The excel tools such as goal seek can calculate backward to obtain an input that would result in a given output. For instance, if John knows the number of monthly repayments that he can commit every month, he uses this goal-seeking tool to determine the amount to qualify for a specified period within a given time and particular interest. Besides, other excel tools like data tables, which enable a person to change values in some of the cells and get different answers, are useful because they will apply the already set formula to compare different scenarios and select the best. A person can use a single table or multiple tables to analyze the data. For instance, John can insert the data that he may have collected and used the tables and what-if analysis function to analyze the data after setting the formula that he needs to use and provide clear advice to the manager as requested. The scenario manager is already built-in excel and can allow John to deal with several variables at once. It will provide calculations and choose the best scenario out of many analyzed. Also, another tool built-in and can help in what-if analysis is the solver, which allows finding optimal values, i.e., the minimum and maximum amounts subjected to constraints set in the excel worksheet. I concur with you that applying these tools will help him analyze even complex data and come up with the best decision to advise the manager about his performance.