Projection and Report System
$250-750 USD
Paid on delivery
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 [login to view URL]
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 12.000.000 growth. $12.000.000/12 = 1.000.000. So $1.000.000 is added to each value of 2008 to calculate 2009 figures.
4. A fixed $ per month. For example $2.000.000 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
UPDATED SPECS. – No new functionality – Just Clarifications
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 for the 3 historical years; and b) weighted average values for the 3 historical years.
The actual historical data will be provided by the user on an Excel sheet or flat file. 36 columns by 2000 rows, with the corresponding headings. Same as shown in the Excel file that has been attached.
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 xxx.xxx.xxx.xxx
The purpose of my requirement is to automate what we are doing manually now with Excel.
1. The user specifies the first year to be projected. For example 2009. Then the system assumes which are the 3 historical years (2008, 2007, 2006)and the other 2 projected years (2010 and 2011).
2. 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
3. Bring the account name and the 36 months of data
4. Place the monthly data on 3 different rows (2208, 2007 and 2006)
a. Create 2 additional rows
1. One row calculates monthly variation in $
2. Another row calculates monthly variation in % (one decimal)
5. Only the first projected year (for example 2009) uses the 12 projection systems and show the detail. As it is shown on the Excel I included.
6. 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 12.000.000 growth. $12.000.000/12 = 1.000.000. So $1.000.000 is added to each value of 2008 to calculate 2009 figures.
4. A fixed $ per month. For example $2.000.000 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
7. 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
8. Projection for years 2010 and 2011 are done by just specifying a percent grow, which applied to all accounts. This percent may be different for 2010 and 2011. No monthly details for the 2 additional projected years.
9. 4. Input data: Historical data either on an excel similar to the attached one, or a flat file produced by the Company system
The interface shows all 12 systems. Interface info will be in Spanish. We will provide all texts.
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
7) Projection for years 2010 and 2011 are done by just specifying a percent grow, which applies to all accounts. This percent may be different for 2010 and 2011.
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 should be able to print the reports
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. In this case it will be a desktop application
2) A design on Ruby or any other platform, using whatever database may be suitable. In this case graphs will be generated on time.
I have received a message from some bidders asking me to post an Excel 2003 Worksheet XLS file instead of 2007. However when I tried converting the file it exceeded the allowed file size. Should any one else have that need, then post a message and I will send the file through that system, because it seems that when sent directly it goes through
Project ID: #438406
