We have an Excel Model, already defined and working manually , with formulas, etc.
The objective is to use monthly data, for example 2008 data and make projections for 3 years: 2009, 2010 and 2011
Based on historical data (36 months) we use 12 different projection systems. Actually ten (10) systems use previous year data (2008) to project 2009 values. The other 2 systems use the a) Average values; and b) weighted average values.
Data to be projected is on an Excel sheet. 36 columns by 2000 rows
Each row contains an account which is identified by a code number (1, 2, 4, 6,or 8 digits), a name (up to 60 – 80 characters) and the 36 months of data.
Each original data element is in the form [url removed, login to view]
The purpose of my requirement is to automate what we are doing manually now.
1. Select and account number (normally 8 digits). All other are consolidating accounts
a. 6 digit consolidated accounts with 8 digits
b. 4 digit accounts consolidates accounts with 6 digits
c. 2 digit accounts consolidate accounts with 4 digits
2. Bring the account name and the 36 months of data
3. Place the monthly data on 3 different rows (2208, 2007 and 2006)
a. Create 2 additional rows
1. One row calculate monthly variation in $
2. Another row calculates monthly variation in % (one decimal)
4. Perform the 12 projections as follows:
1. A year % growth. The % growth is divided into 12 and the resulting % is applied to each month of the previous year. For example 12% growth. 12%/12 = 1% per month is applied to each month of 2008 to calculate each month of 2009
2. A fixed % per month. For example 2% applied to every month of 2008 to calculate every month of 2009
3. A year $ growth. The $ growth is divided by 12 and applied to each month. For example [url removed, login to view] growth. $[url removed, login to view] = 1.000.000. So $[url removed, login to view] is added to each value of 2008 to calculate 2009 figures.
4. A fixed $ per month. For example $[url removed, login to view] applied to every month
5. Yearly % increment. The total of 2008 is increased by the specified percentage. The resulting value is divided by 12. Each 2009 month has the same value.
6. Yearly % increment. The total of 2008 is increased by the specified amount. The resulting value is divided by 12. Each 2009 month has the same value.
7. Historic average. The average of the 3 historic years is increased by a fix percentage
8. Same as system 7 by taking the weighted average for the 3 years
9. $ of Increment to weighted Average. A fixed amount is incremented to each weighted average
10. The user enters a different % of increment for each month
11. The user enters a different $ of increment for each month
12. The user enter a different value for each month
5. Make 3 graphs using the projections
a. Graph 1. Linear, showing the 36 historical months. Show trend
b. Graph 2. Shows all 12 projections alternatives
c. Graph 3. Shows values for the 3 historical years and the first projected year
The interface show all 12 systems.
The actual interface show the user 6 data tables. Some are to input criteria and other to show results:
1) Table 1. Has 6 Variables: Name of User, Name of Business, Cost center code, Name of person responsible; Account code to be projected
2) Table 2 is a summary of the historical and Projected information with the total, the yearly average and the %
3) Table 3 displays all 36moths of historical data in 3 groups, one per each year. Each group shows; a) monthly values in one row; $ variation (month 1 – month -1) in another row and % variation in another row.
a. There are 8 other rows that display the following for each moth
i. Total 2006 to 2008
ii. Average 2006 top 2008
iii. $ Variation 2006 vs. 2007
iv. % Variation 2006 vs. 2007
v. $ Variation 2007 vs. 2008
vi. % Variation 2007 vs. 2008
vii. Average 2006 – 2008
viii. Weighted Average 2006 – 2008
4) Table 4. It is used to input the parameters for each of the 12 projection systems.
5) Table 5. Displays the projections. Each system has 12 months of projected data, a total, a monthly average and a % Growth over previous year (2008)
6) Table 6. Display a summary of the 6 years: Total for the year, Monthly and % variation, and $ variation
Looking at the table with the 12 projection alternatives and to the graphs, the user decides which of the 12 options he/she prefers to use for the particular account that is projecting.
Clicks the option and the model takes the 12 months of projected information to the Data sheet.
It uses the same criteria to project the second and the third years
Places in the data sheet (in the case of excel or database if other) the 36 projected moths.
Then the purpose is to build 4 different reports using the data that has been projected.
The user should be able to choose the units to be used: $, $000, $ Millions.
Report building should be done by bringing to the report the projected data.
The user types the account number and the system should bring the monthly values
I would like to see 2 alternatives:
1) Using the actual Excel Model and developing the Macros and VBA needed to automate the existing model. This will make it a Windows Project ;
2) A design on Ruby or any other platform, using whatever database may be suitable. This will make an open source project
I have attached the actual excel model. All field in green are input data. All other are calculated