Create simple formulas directly in a cell much like in Excel

Description

This requirement allows users to apply formulas to a specific cell using a formula builder or simply typing in operators while using a sheet view of a CPM tool.

Example Use Case

Scenario: An organization relies heavily on Excel for their day-to-day forecast and budget modeling. As they transition to CPM software, they expect similar functionality in terms of creating simple formulas directly in a cell and in a syntax that is understandable.

Solution: The CPM software allows easy formulas to be inserted such as adding up two cells or applying a function. For example, the use can calculate the Revenue Growth Rate by simply writing a formula like [(Current Revenue - Previous Revenue) / Previous Revenue] in a cell.

Considerations

This might sound like the most obvious functionality ever, causing buyers to gloss over it when evaluating a solution. We've included this requirement for exactly this reason. Some products, especially OLAP tools, can not do this in an easy way. They might not allow formulas inside a cell at all. If they do, the syntax is so complex that nobody can understand it except a consultant. They might include many functions that are accessible by a formula assistant but are still incredibly difficult to comprehend and build.

Something as simple as adding up two cells isn't terribly difficult. However, consider something that should be simple such as averaging out some data for a year:

/* Assuming 'Sales' is your account and 'FY2023' is your target year */
FIX ("FY2023")
/* Aggregate the data to ensure all data is rolled up correctly */
AGGREGATE(@DESCENDANTS("Sales"));
/* Calculate the average for the year */
"Avg Sales" = @AVGRANGE("Sales", @CHILDREN("Jan"), @CHILDREN("Dec"));
ENDFIX


Yikes. For the average Excel user, this syntax will be challenging to understand, especially for something simple. This is why many OLAP systems rely on an Excel add-in. The add-in still includes Excel syntax and can be used for quick math like the scenario above.

Questions to Ask a Vendor

• Formula in a Cell: Can you show us how to add a formula into an editable cell, and what tools are available to do that?
• Continuity: What happens if a top-down value is added? Will it erase our formula?
• Syntax: Are we able to include sophisticated functions in the cell, or only simple mathematical operators?