Mapping Content

15 posts / 0 new
Last post
Jun Yan A+ 124
Mapping Content

Hello all!

I was meant to start doing more sharing, but I'm sure like everyone else, work takes over!

Here's a technique that hopefully others may find useful. This is the default way we build our modular files as we tend to work with clients whose charts of accounts never line up with the way they run their forecasts. This method allows us to extract the general ledger in any format, and then we are able to map detailed driver based forecasts to align with their historical accounts. This is quite nifty for things like S&W, where often Xero only shows Base S&W and Super.

We take this approach as a lot of our models get provided to financiers that want to see things line up, so they can scrutinise at a GL level.

The technique is nothing new, as it's effectively the same method that gets applied to things like AR, AP, Fixed Assets etc. in the current generic financial content library. The Screencap below is the module setup, with a central Mapping Module that brings in historical data (GL + Historical numbers) as well as the detailed drivers.

Modano Structure

The following screencap shows how this structure works in a modular workbook for revenue only. In a full modular build, this same methodology can be applied across most income statement and balance sheet items. The balance sheet side is a bit more complicated and has more considerations, so the income statement is a good place to start.

Detailed Drivers

Stepping into the detailed forecasts (screencap below), you can see that each module is a category - funnily enough, another approach to deal with Modules as Categories...

The module allows for many categories, you can duplicate / mirror the modules and create as much detail as you need, so long as you provide the "Revenue (Drivers)" link out so that the detailed forecasts get passed into the Revenue Mapping module. We have a library of different drivers with this default link built in, which we can leverage depending on the client's industry.

The next step is to then select an appropriate mapping for each category (screencap below). In this case, the links are on a category basis, but this can readily be changed to Totals to help with reducing the number of SUMIFs. This may be useful if each module is meant to reflect separate products.

The mapping module contains the historical data as well as the forecast data, now mapped to line up against historical ledger accounts. This mapping module also contains the standard Modano Revenue Link-out, which allows it to work within the generic financial model library. Better practice would be to improve the labelling that comes from the detailed forecast modules. I will get into different techniques of doing that in the next post.

The end result of this method is to see an income statement that shows revenue categories aligned to historical chart of accounts.

Happy to step through in detail if people have questions. Thought I'd share something that we've found to be supremely helpful. More than 90% of our builds involve mapping, either entirely or in pieces.

Hope that helps generate some ideas!

Until next one, thanks all.

Jun

Michael Hutchens A+ 189

Hey Jun,

This is really cool, I'm just trying to understand the use case... So in your example your historical income statement contains 3 revenue categories - Apples, Bananas and Avacados - but you want to forecast multiple revenue streams with drivers before allocating them to these categories. Is this understanding correct?

M.

Jun Yan A+ 124

Hey Mike,

Short and general answer for use case is to tidy up detailed forecasts back into their respective historical chart of accounts line item. Another way to look at it is that it offers an alternative solution for categories of categories.

This is noticeable for something like S&W where accounting systems tend to deliver two lines, often times S&W + Super. On the forecasts side, the default is to do it at an FTE or roles/category level so if you expand the financial statements you see the category mismatch. Not a problem when you roll up, but if you want to examine the linkages between historical data and forecast numbers in detail, then it can be an issue.

In this example assume the historical accounts are Apples, Bananas & Avocados, but I want to drive each of those with different techniques (Amounts, Units X Price). On top of that I have different drivers, in the example different channels (Retail, Wholesale, Direct). It probably may have helped if I made the channels different, for example you may not have direct for avocados / wholesale or you may have a fourth channel for Apples / Bananas.

Hope that helps.

Jun

Michael Hutchens A+ 189

Got it. Nice.

We've done a similar thing in our upcoming professional services modules because income statement revenue is way too basic for the detail required for forecasting. So I can see what you mean now.

M.

Fernando Prieto A 1
FP

Extremely helpful technique especially as you said when you want the clients chart of accounts to match the forecast.

It also helps as clients also want their budgets tied into their chart of accounts and typically external users follow it easier if it all lines up.

Q: Jun - what is the heading of your second post?

Jun Yan A+ 124

Hey Fernando,

Thanks for reading through, glad it was useful.

I'll be honest, I haven't had a chance to get around to that second post, it's been rather hectic. I'll be focusing on creating more content to release this year as it's a big focus for us.

Having said that, I have started the year before saying this, and not having it happen... but I need to keep to that this year!

Jun

Jun Yan A+ 124

Hello community,

Finally found some time to pull together some content to share focusing on modelling ideas / techniques.

In this example, I'm trying to do a variant of the above example, but there are a few differences.
Here, I am starting with a mixture of historical financial data as well as historical operational data to supplement the forecasts.
I then bring the historical operational data in as a reference point for the forecasts. Note: the new Hist then Forecast time series is useful for this type of forecasting, so I might do a variant using that time series as an example.

You'll see I drive the revenue logic separately for the two revenue accounts, and then bring them back together in the mapping.

I have excluded the other financial statement items, just to keep the example clean, but I might add a few extra line items like Cost of Sales in the next example.

Hope this provides something useful! Until next time.

Jun

 

blakem X 1
BM

THis is awesome Jun.  

 

Will be really useful the revenue mapping and I can see a use case for detailed drivers to calculate production shift costs and then simplify  them to a production costs line for the forecast financials.

Have you created these modules so that they can be inserts into generic models? 

Jun Yan A+ 124

Hey Blake,

Great, I'm glad it can assist in some way!

Yes, our generic financial model is built with a full suite of these modules across the Chart of Account 6 set to manage each P&L section. We also have an equivalent set to manage the "other" balance sheet items (non-debt, equity, payroll tax, etc.). Doing it this way means we can create as many drivers as we want, and then re-align them back onto the historical financial statement chart of accounts.

The added benefit of doing it this way is if we have centralised calculations like salaries and wages, we can retain the centralisation to calculate the full expense, but still pass out subtotals / totals or individual line items into various parts of the financial statements. A common example that we do a lot, is having a centralised salaries & wages calculation module, but we want these to be allocated to a mixture of Cost of Sales versus Opex, this infrastructure allows us to pass the splits out and then fold them back up into the financial statements. I might do a demo of this next if it's useful.

Jun

blakem X 1
BM

A demo would be awesome as I would like to build this into my financials 

Still getting my head around how to bring this in 

Jun Yan A+ 124

Hey Blake,

Apologies for the tardiness, week got a bit ahead of me. Here we go.

I've attached an example of Salaries & Wages being split in context, using the mapping methodology above. I've included the full Income Statement (Chart of Account 6) so you can see the end result.

The first module links diagram is the centralised Salaries & Wages Mapping piece.

A few keys things to note, this "Salaries & Wages Mapping" module would replace the existing Salaries & Wages module from the standard COA 6 models. The Monthly Income Statement would also need to be replaced as it contains the relevant "Driver" link-ins. In this instance, we've had to customise the Income Statement category block for salaries & wages, by giving it a separate unique link that comes from our mapping content. You can ignore the Module dependency link that's just something we've included to ensure we include all relevant modules with our core mapping pieces.

The main idea is that this Mapping module brings in the Historical Income statement Salaries & Wages link, which allows it to bring the necessary categories from the historical COA, as well as the historical financial data. We then stitch this historical data together with the mapped detail lines. This is exactly the same as what we've done in the two revenue examples above.

 

The link diagram below relates to the Salaries & Wages content:

The module here allows the normal S&W calculations you'd find, but there is a separate recalculation piece down the bottom to allow an allocation of the base & salaries and superannuation into cost of sales / operating expenditure. You'll see the links Cost of Sales (Drivers) and Salaries & Wages (Drivers). These are standard links for us in our library, and we use these generic links across all our content, so it means no matter what driver module you develop, inserting it will directly connect into the mapping modules for tidy up and connection into the Income Statement.

One thing to note about this Salaries & Wages module is that we've maintained the existing link-outs for Salaries & Wages as they will still be relevant for things like Annual Leave, Long Service Leave, Payroll Tax, Corporate Tax, etc. The benefit is we don't then need to swap all the other modules out and can still use the existing content.

 

The Cost of Sales Mapping Link diagram is below:

Once the module is in place, it is taking the cost of sales expense recognition and allocating it into cost of sales on the Income Statement. Again, it is stitching the forecast granular detail back into the historical cost of sales categories.

 

A snip of the Mapping sheet is below:

The current mapping is at a category level, which means there might be some duplication, especially if you have things at a headcount level, but nothing is stopping you from using just Totals in the mapping. It will depend on the granularity of your historical data and whether you need to allocate into Teams / Departments or other granularities.

 

As a general learning tip. One thing I found super helpful and it was probably because it was the only option at the time, was learning Modano using "Separate Assumptions and Outputs" content. This is something I get the team to learn with to this day, even though our general content is all Single Component. The reason is it helps you to practice learning that blocks that are Assumptions can also be Outputs, and this thinking is very useful when developing reusable content in Modano. You quickly learn to realise that the more you can maintain the existing content without breaking it too much, really allows you to the develop content that "Links-In".

Anyways, hope this helps a bit.

Jun

matt.tapps A+ 1
MT

Hey Jun,

This looks awesome!

I was trying to replace my Salary & Wages modules with what you have attached above but ran into an error about the time series not matching. Do you have any ideas how to address this? I'd love to be able to use your solution to COGS Salary & Wages allocations as this is something I've wanted to do in the past and have only achieved by jankily editing my financial statement modules along with the RevEx modules.

Cheers,

Matt

Jun Yan A+ 124

Hey Matt,

Not quite sure what the Time Series error is unfortunately. The module content itself is locked from export, as this is meant purely for demonstration purposes.

I'd say it's worthwhile following the steps / links in the post (diagrams) to try and create the content yourself. It will help you generally with customisation in future, as the concept is the same across pretty much all parts of the financial statements. If you get stuck on any of the steps, happy to update the post with more detail to flesh out the step.

I will prep a video in short order that runs through this in a bit of detail, but definitely try and build the module from scratch, create your own set of links and then replace from an existing model.

I'll do a few more examples in the coming weeks to try and give more context.

Jun

matt.tapps A+ 1
MT

Hey Jun,

Fair enough about the above - I did notice it was locked from export.

I'll have to find some time to play with the above and get it working as it would be extremely functional for a lot of our clients!

Cheers,

Matt

Jun Yan A+ 124

Great stuff Matt!

Yes, it's one of the most commonly requested customisations for us, so once you get it working, it'll definitely come in handy.

Jun