In spreadsheets, goal seeking is the ability to calculate backward to obtain an input that would result in a given output. This can also be called what-If analysis. It can be attempted through trial and error or by more logical means. A what-if question in a spreadsheet allows you to ask what the effects are of changing one or more values and provides you with results from different scenarios. For example, consider a hypothetical situation in which you own and operate your own business. - You might be considering giving your employees a bonus for their outstanding work this year. If you gave bonuses, what would the effect be on your annual net profit? A spreadsheet with correct formulas could forecast the effect on your profit with different scenarios - a $500 bonus vs. a $1,000 bonus, etc. - You might own a company vehicle which is rather old and in need of constant maintenance. A spreadsheet with correct formulas could forecast the effect on your profit with different scenarios - spending the money to fix the company truck and hoping it will last another year vs. trading for a brand-new vehicle, etc. Spreadsheets can be just as useful on a personal basis. - Use this hypothetical scenario to answer the what-if questions on the next page. You need a vehicle to replace the 10-year-old car (with 100,000 miles) that you're driving now. You'd prefer a used vehicle but would consider a new one if the forecasted results show you could afford it. - You'll be taking out a loan. For the purpose of this worksheet, assume the following: Interest Rate - Used Car: The average interest rate for a used vehicle is 4.9%. Currently, used car loans typically have higher interest rates that new car financing because there's more uncertainty as to the value of the car and lenders can demonstrate that used car borrowers defoult more frequently on their auto loans, regardless of their credit score. Interest Rate - New Car: The average interest rate for a new vehicle is 3.65%, Down Payment - New or Used Car: Assume you will make a 10% down payment based on the purchase price. - Trade-in Value - Use $0 as the trade in value; you have other plans for your old vehicle. - Sales Tax - For this worksheet, use Springfield's tax rate which we'll say is 8.1% Interest Rate-Used Car: The average interest rate for a used vehicle is 4.9%. Currently, used car loans typically have higher interest rates that new car financing because there's more uncertainty as to the value of the car and lenders can demonstrate that used car borrowers default more frequently on their auto loans, regardless of their credit score. Interest Rate - New Car: The average interest rate for a new vehicle is 3.65%. Down Payment - New or Used Car: Assume you will make a 10% down payment based on the purchase price. Trade-in Value - Use $0 as the trade in value; you have other plans for your old vehicle. Sales Tax - For this worksheet, use Springfield's tax rate which we'll say is 8.1% Title, Registration, and Other Fees: Use \$250; do NOT include all fees in loan. - YOUR GOAL IS TO KEEP YOUR PAYMENT AT OR BELOW $300. Navigate to https://www.calculator.net/auto-loan-calculator.htmI A link to the calculator is also available in Modules > Unit 3 Microsoft Excel > Loan Amortization Schedule. Using the calculator, enter the correct information in each of the following scenarios. Indicate * whether or not you could meet your GOAL with each vehicle you're considering. Keep in mind that even though you can't see it, a spreadsheet is working in the backaround to recalculate the results you enter
