The project is to convert the attached Excel model to one that utilizes a relational data base. We would like to have an object-based application that allows us to build the models (like the one attached), which are customized to each client, in a simpler fashion.
In essence, a sales projection is input(C3), monthly sales forecasts are input (F4,T4,M4 etc) and then related expense percentages added (D22, D23 etc) and it creates a monthly forecast. When Actuals are input (H8,H9,H10 etc), it compares the actual to forecast on a dollar and percentage basis (columns J,K etc). The user can immediately tell if the amount or the percentage for each line item is above or below forecast based on color (red and green). YTD totals are included at the end.
One addition to the attached model we would like to add is the ability to forecast an expense by month (eg, D22 applies a cost for the entire year that is amortized monthly). The desire would be to forecast (for example) Auto Insurance at 1.2% of revenue in January and 1.2% for June only.
Further, the current model is given to clients and they manage the model themselves. The ability to export to HTML or XML and charge a subscription for access would be a welcome addition.
The attached is an example of a single branch. The application needs to allow for branches to be added, which then requires a Consolidated view.
Further, data is collected from a client as an export from Quickbooks. We also have a template that massages and redirects this data for input into the attached model, so once this assignment is done, the template will need to be completed.
