Excel Dashboard for predictive KPI analysis

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ---------------- OBJECTIVES ---------------- We want to solve following challenge: Determine automatically the number of new car, which can be bought on a given month based on monthly produced total cashflow of all owned cars. We define intially following values manually, which will be used later on to produce results automatically to solve our challenge: - Signle Car Acquisition cost - Monthly income per car - Initial number of car owned - Lifespan in month of each car (default 30 months) --> Is the duration where each car is in our car pool and produce monthly income - Table should offer the option to switch amounts between different currencies like Euro, XOF --------------------------------------- PREDICTIVE KPI DASHBOARD --------------------------------------- We need to get following predictive KPI on Dashboard: 1. Single Car's total Revenue --> Is the expected revenue generated by a car at the end of its lifespan 2. Single Car's total ROI --> Is the expected return of investment generated by a car at the end of its lifespan 3. How many other new cars a single car can produce during its lifespan based on monthly income per car and Single Car Acquisition cost 4. How many other new cars the current number of owned cars can produce from a time "t" over the REST lifespan of each car available in the car pool 5. Targeted "total available_cashflow" after a given time "t+x" - How many new cars from "total available_cashflow" at time "t" can be bought until given time "t+x"? - Show also the months where cars are bought - Show number of available cars with rest lifespan at the end of that given time - Show how many other new cars the current number of owned cars can produce over the REST lifespan of each car available in the car pool ------------------------------ VARIABLE DEFINITION ------------------------------ *************** Monthly Income *************** "Monthly Income" in current month is equals to [SUM(current "Monthly income per car")] ********************************** *total available_cashflow* & "Expenses" ********************************** "total available_cashflow" in current month is equals to [ SUM(Previous "total monthly_income"+current "total monthly_income"- Expenses) ], where "Expenses" is amount spend to buy new cars in current month Check IF "available_cashflow" is enough to buy X cars, THEN increase number of owned cars by X, Create X new lines, which represents the monthly income produced by each new owned cars during car's lifespan When "X" cars are bought the amount (X*"Car Acquisition Price") used to buy them is deducted from "total Available Cashflow" in that current month of acquisition, but the montly produced cashflow increase by X*"Monthly income per car". ****************** Cumulated cashflow ****************** "Cumulated cashflow" in current month is equals to [ SUM(Previous "total monthly_income"+current "total monthly_income"] ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++