Financial Calculators

Programming Each Cell

The following source code as been simplified, compared to the original version. This program populates one cell in a "SAS" spreadsheet. The purpose is to demonstrate that SAS can handle very complex formula, just like Excel.

The major difference between SAS and Excel is speed and automation.
The SAS version will always cost less when compared to Excel

class cell73 / (description='DC Handling Cost: Picking');  
    public num page                / (sendEvent='N');      
    public num row                 / (sendEvent='N');      
    public num column              / (sendEvent='N');      
                                                           
    public num outboundBoxCount    / (sendEvent='N');      
    public num percentOrderCommit  / (sendEvent='N');      
    public num manualLaborPerHour  / (sendEvent='N');      
    public num pieceRatePerHour    / (sendEvent='N');      
    public num indirectCostPerHour / (sendEvent='N');      
    public num orderCount          / (sendEvent='N');      
    public num countPerBox         / (sendEvent='N');      
    public num averageWagePerHour  / (sendEvent='N');      
                                                           
    public num spreadsheet [*,*,*]  / (sendEvent='N');     
                   
    runInterface: method;                                  
        spreadsheet[page, row, column]=                   
          (((((outboundBoxCount * percentOrderCommit) *    
               manualLaborPerHour) /                       
               pieceRatePerHour) /                         
               (1-indirectCostPerHour))   +                
             (((((outboundBoxCount * percentOrderCommit) * 
                  manualLaborPerHour *                     
                  orderCount) /                            
                  countPerBox) /                           
                  pieceRatePerHour) /                      
                  (1-indirectCostPerHour))) *              
             averageWagePerHour;                           
    endmethod;
endclass;

Performance

The original SAS application contains several hundred cells. Data sources were automatically filtered, populated, and formatted for the SAS application on the backend server. The fully assembled calculator ran to completion in a few minutes. The entire process could be monitored through a GUI, but was usually completed without any human intervention.

The Excel spreadsheet required up to eight hours of copy-and-paste updates from 10+ RDBMS sources. The spreadsheet needed between 24 and 36 hours to complete, depending on the number of cells in the spreadsheet.. The process was debugged and rerun repeatedly. With so many data sources it was very easy to make a copy-and-paste error. Global parameters and configuration setting were compared to hardcopy documentation and were also prone to error.

Diagnostics & Debugging

Diagnostic and debugging code is within each program, but is insulated from standard process.

The systems analyst can view the results from each cell, one at a time. System performance is never an issue because the diagnostic requires a separate command to operate. More importantly, diagnostic data is pushed into a different data channel.
    
class cost73 / (description='DC Handling Cost: Picking'); 
    public diagnostic / (sendEvent='N');
 
    runInterface: method;
    endmethod;
 
    eventhandler runDiagnostic / (sender='*', event='diagnostic data');
 
    runDiagnostic: method;
        insertn(diagnostic, page, -1, description);
        insertn(diagnostic, row, -1, description);
        insertn(diagnostic, column, -1, description);
        insertn(diagnostic, , -1, description);
        insertn(diagnostic, spreadsheet[page, row, column], -1, description);
    endmethod;
endclass;                                                  

Multiple Data Sources

The SAS spreadsheet pulled summary and detail data from several RDBMS, automatically.

With the Excel spreadsheet, nothing is automatic.
Extra VB coding is necessary to summarize.

class cost73 / (description='DC Handling Cost: Picking');  
    public num page                / (sendEvent='N');      
    public num row                 / (sendEvent='N');      
    public num column              / (sendEvent='N');      
                                                           
    public num outboundBoxCount    / (sendEvent='N');      
    public num percentOrderCommit  / (sendEvent='N');      
    public num manualLaborPerHour  / (sendEvent='N');      
    public num pieceRatePerHour    / (sendEvent='N');      
    public num indirectCostPerHour / (sendEvent='N');      
    public num orderCount          / (sendEvent='N');      
    public num countPerBox         / (sendEvent='N');      
    public num averageWagePerHour  / (sendEvent='N');  
endclass;

Summary

Server-side SAS objects are the perfect replacement for desktop Excel

With SAS objects, the entire calculation is in one location. Traditional Base/SAS programming tends to spread pieces and parts of the computation across Data Steps and procedures.