In this exercise we will utilize MS Excel to build a simple model, and then use functions such as GoalSeek and What-If to perform advanced analytics, in this specific case model-based analytics or predictive modeling. In step 1 you will build the model, and in step 2 you will use the model to answer several questions. Step 1, building the model. You are helping as friend who wants to build a used car business. Your friend needs to build a five-year business plan and submit it to the bank. Here are the facts and assumptions your friend provides as input: 1. Overhead cost per year, which includes rent for a lot and office trailer, advertising, internet and telephone - $45,000 initially. 2. Average price to acquire used cars - $15,000 (we assume all cars are bought and sold in the same year) initially. 3. Initial sales volume - 20 cars. 4. Average cost to repair, detail, and re-condition each car - $2,000. 5. Average Sales price: $22,500 The Bank requires a 5-year plan. For years 2-5 you assume the following growth rates: 1. Increase for costs 5% per year on top of previous year (applies to overhead and conditioning). 2. Increase in purchase price for cars: 2% on top of previous year. 3. Sales volume increase: 10% (when you build the model, make sure you round the number to 0 digits as you can't sell fractions of a car) 4. Sales price increase: 2.5% on top of each previous year. Step 2, Questions: 1. Based on the facts of the model, what is the profit margin percentage in years 1 and year 5, what is the average margin over 5 years? Profit margin is calculated (Avg. Sales price - Avg cost per car)/Avg. Sales Price. 2. What is the gross profit or EBITDA (Earnings before Interest, Tax, Depreciation, and Amortization) in year 1 and year 5, what is the average EBITDA? 3. What is the breakeven point in year 1 - meaning, how many cars must be sold to cover the overhead costs. (Answer this with a goal seek, where profit is zero and target is # of cars) 4. What-if: All other things equal, what happens to overall EBITDA if overall costs increase by 7% in years 4 and 5? (Create a What-if scenario according to the tutorial) 5. What-if: All other things equal, what happens to EBITDA if sales price suddenly decreases in years 3-5 by 5% each year from our assumed initial levels of the original model? (Create a Whatif scenario according to the tutorial) 6. What-if: All other things equal, what happens if the landlord increases rent and our overhead costs goes up by $5,000 for years 4 and 5? (Create a What-if scenario according to the tutorial) 7. The bank requires a profit margin / EBITDA of 25%. How can you get to 25% EBITDA? This is a goal-seek challenge with Profit margin as the variable and either cost or revenue as the target. You can also use a combination of cost and revenue (meaning reduce some cost and increase some revenue to get to the desired margin). Answer this question for each of the five years using the Excel solver tutorial in the resource section. 8. Reflect on this exercise and answer why it is difficult to use this predictive modeling exercise in the real world