Optional Tables and Info-Explorer are a natural fit. E.g. store custom data related to budgets and forecasts within Optional Tables, and use Info-Explorer to slice, dice and update.
Liberate your information from limitations
Custom data stored within Sage 300
Includes Spreadsheet Add-In with Write-back
Why use it?
Do you store and maintain extra data outside of Sage 300 (e.g. KPIs, budgets)?
- Are you compromising decision making by holding key data externally, where it can’t be readily shared or included in reports?
- Wouldn’t you rather have custom, multi-dimensional data structures within your Sage 300 database, enabling better control and visibility, while also allowing you to view and manipulate data in Excel then write it back to Sage 300?
What is it?
Optional Tables frees you from the limitations of your standard Sage 300 (Accpac) data. Use it when you need to record extra information, such as budgets and forecasts.
It lets you create new fields (facts) in any module of Sage 300 (including third party modules developed in the SDK), and link them through to multiple Sage 300 master files (dimensions). You are creating custom data structures within your Sage 300 database, stored as Optional Tables.
You then have the power to use this information in many ways: Manage KPIs, budgets, forecasts, actual results and non-financial data at any level; Report at the level you define, with the combination of dimensions and facts you specify, using financial, non-financial and your own custom data.
Included with Optional Tables is the Orchid Spreadsheet Add-in for Microsoft Excel®. You can read standard Sage 300 data and Optional Tables data, manipulate it in Excel, and write data back into your custom Optional Tables in Sage 300. As a budgeting tool, you can develop budget models within Excel, distribute spreadsheets for the collection of budget data, and centrally update Sage 300 with your budgets at the click of a button - while keeping a full history of budget revisions and changes with descriptive notes. Users do not need access to or training in Sage 300, they can complete their budgets in Excel.
HOW IT WORKS
Data can be entered directly into Sage 300 with Optional Tables data entry screens. One screen is designed for time-sensitive information such as budgets and forecasts, another screen is optimized for static master file input. Or create your own screen. Data entry can also be via the Spreadsheet Add-In.
- Hint: Consider using Orchid Info-Explorer to slice and dice this additional data for user-friendly, intuitive analysis and budgeting.
Using Microsoft Excel and a wizard, you set up the formulae for reference data for the report specification – the data required from Sage 300. After populating your worksheet with this data users can format, manipulate and update it as required, treating it as a normal standalone worksheet.
With write-back to Sage 300, you are also able to directly update Sage 300 Optional Tables. This removes the need for manual data entry or imports, saving time and increasing accuracy.
- Hint: Consider using Orchid Info-Explorer with writeback as an alternative solution for budget preparation, using cubes and write-back to the database, instead of spreadsheets.
Business Scenario Example
Used together, Optional Tables and Info-Explorer provide a very affordable yet powerful tool for budgeting and forecasting, e.g.:
- Use Spreadsheet Add-in to build a detailed bottom-up budget at the level you need, e.g. Customer & Item rather than just GL level, and store it in an Optional Table.
- Pull both the Optional Tables budget data and Sage 300 actual sales data into an Info-Explorer cube, and perform slice and dice analysis.
- Within the cube make a top-down budget adjustment, using a spread formula to apply the change to Customer and Item budgets.
- Use Info-Explorer’s write-back feature to update both the Sage 300 GL budget and the Optional Tables detailed budget fields.
- A platform for efficient & flexible budgeting and forecasting.
- Secure, multi-user, controlled access to critical custom data.
- Key data is stored in Sage 300, not external databases, spreadsheets etc.
- Backup data along with standard Sage 300 data.
- Developed in the Sage 300 SDK with standard Sage 300 security, customisation capabilities, import/export, data integrity checking and more.
- A foundation for better information and Business Intelligence.
- Better information means better reports, and better decisions.
Using Optional Tables as a repository for extra data allows you to do all your Sage 300 customization through VBA. You can store data the Sage 300 way without having to take the plunge into Sage 300 View development with C++.
- Use Microsoft Excel for budget data collection, reporting and write back.
- Increased accuracy, no need to copy and paste.
- More efficient, faster, less checking needed.
- A foundation for budgets, forecasts, KPI reports or non-financial data tailored to match your reporting requirements.
- Store multiple budget revisions / changes with descriptive notes.
- Flexible data entry options - manual entry, data import, or use the included Spreadsheet Add-In with write-back direct to your Optional Tables in Sage 300.
- Define each Optional Table with up to 10 Sage 300 views (Dimensions).
- Define an unlimited number of fields per Optional Table.
- Built-in wizard to fast track spreadsheet development.
- Include additional budget, forecast and subsidiary ledger information into your Financial Reports by embedding Optional Tables read and write functions into the Excel spreadsheets, using Sage 300's Financial Reporter functions.
Optional Tables 2020 requires Sage 300 2020 System Manager and Transaction Analysis & Optional Fields. Spreadsheet Add-in requires Microsoft Excel 2003 or later (32-bit).