Operating Expenditure - Opex Drivers

5 posts / 0 new
Log in or register to post comments.
Last post
6 years ago
#1
Edward Riches X 2
ER
Operating Expenditure - Opex Drivers

Hi Everyone,

I am trying to create a operating expenditure drivers module, where opex amounts are calculated as a % of an opex category (similar to the Operating Expenditure (Capex Drivers) module).

I am struggling to create a lookup table reference all opex cateogries to create a drop down category selection tool. Once I have worked that out plan is to simply follow the structure of the Operating Expenditure (Capex Drivers) module. 

Any help would be appreciated!

Thanks

6 years ago
Michael Hutchens A+ 189

Hi Edward,

So just to be clear, you're wanting to allow model users to enter an operating expenditure assumptions for each category as a % of another operating expenditure category in the same operating expenditure module?

If so, this is pretty easy to do, as it's a lot like the Operating Expenditure (% of Revenue) module, only the % assumptions are based on the operating expenditure categories within the same module. The issue to avoid is users inadvertently creating a circular reference by choosing to enter assumptions for a category as a % of itself!

If my understanding of what you want is correct, please let me know what time series you would like the module to be based on (e.g. annual, monthly, quarterly, annual historical & forecast, etc.). If you can upload the module as far as you've got, that would be even easier.

If I'm missing something please clarify for me.

Thanks, M.

6 years ago
Edward Riches X 2
ER

Thanks Michael,

Let me head down the rabbit hole and tinker for a few days. If I don't have any luck I'll get in touch.

Cheers,

6 years ago
Edward Riches X 2
ER

M,

Attached is how far I have got so far.

Attachments: 
Operating Expenditure (Allocated).modm
6 years ago
Michael Hutchens A+ 189

Hi Edward,

This is looking good, you've worked out how to add a lookup component and a second categories group for the direct operating expenditure.

I presume you're planning on inserting this module into a modular workbook which already contains an operating expenditure module for direct operating expenditure, then have both of them feed into the financial statements?

I've just tried this and it works well. Probably the only minor things I'd note are:

  1. You need to apply Output Percentage style to the % of Operating Expenditure outputs categories cell block.
  2. I'd group away a lot of the rows that don't need to be visible. We've set a bad example here on the Modano website but 2018 content will be much more compact.

Let me know if you need any help, looks like you're on top of it mate.

M.

Attachments: 
Modular Workbook-Operating Expenditure-Allocated.xlsb