The client wishes to see workings for each business case at the same time - i.e. using the efficient datatable modelling method is NOT a requirement.
The requirement is for 3 sets of assumptions, 3 sets of workings, and 3 Income Statements within the same model.
As there are 3 cases (Base, Scenario 1, Scenario 2) the requirement is 3 Income Statements (Outputs), 3 cases of Assumptions & 3 Workings (Outputs) sets, and modules within each scenario.
The Income statements for each case would have : Revenue, Labour Costs, and Other Opex
What approach is best for the above?
Thanks
Nick
Hi Nick,
This is a perfect opportunity to utilize the module mirroring capability in Modano. I've used to it build the attached example in 3 minutes, here's how:
Step 1. Insert Modules
Insert whatever modules you need for one scenario. I've inserted Revenue, Operating Expenditure, Other Revenue & Expenses and Income Statement modules, and I've done so directly from a module suite suite so I can insert them all together (click the Show non-module files hyperlink in the Insert from Web dialog and select Module suites as the file type to insert directly from module suites), as shown below:
Step 2. Mirroring Modules
Use the Mirror Modules tool (Modano tab, Mirror button) to mirror all 4 inserted modules to create your 2nd scenario, as shown below.
You could alternatively use the Duplicate Module tool, but mirroring modules means that non-assumption, non-category changes to any of the mirrored modules will flow through all related modules - e.g. if you change the revenue outputs formula, it will flow through all other mirrored revenue modules. Hence, I usually mirror rather than duplicate when creating parallel scenarios:
Run the Mirror Modules tool again to mirror the newly-mirrored modules again, thereby creating your 3rd scenario:
Step 3. Links Amendment
Load the Project Manager, and manually de-link the precedent modules from each income statement that are not part of its scenario, as shown below for the 1st scenario income statement. We'll be releasing an upgrade to the Mirror/Duplicate Modules shortly that won't make this necessary, but for now it's a semi-manual process.
Step 4. Workbook Structure
Still in the Project Manager, add time series assumptions and outputs sheets for each scenario, then drag the assumptions and outputs for each scenario onto these sheets before changing their titles and tabs, as shown below:
And voilà, you have parallel scnario analysis!
Thank you very much Mike.
This is very powerful - I can separate assumptions and outputs for each company / scenario using new tabs within Project Manager / Content / Add.
The remaining issue is the treatment of shared costs - eg head office costs that need to be allocated to each company using a % share of costs (eg 30/30/20/20).
My current solution is to add a salaries and wages module relating to the corporate salaries to each of the 4 companies, and then enter say 30% of the salary costs as hard coded assumptions for company 1 etc. ie each company has 2 salaries and wages modules - 1 for its own specific salary costs and another for its share of the corporate salaries and wages.
An alternative is to create a separate tab for shared costs and add a salaries and wages module and split the costs into the 4 companies. However, I am struggling to link each companies share of the corporate salary costs to the relevant companies salaries and wages module.
Hi Nick,
I would set up a new module like you suggest to keep all shared costs in this module.
What are the struggles your facing with linking these costs to the relevant companies?
Cheers
T
Hi Tarjei
I have created a shared Cost Module for Salaries.
The shared cost module is then linked to the Individual company salary cost modules.
The total cost (100%) has been linked to each company.
I have then attempted to edit the formula by referencing a % share output within the shared salary cost module.
This then creates the "Modano Freeform Formula Warning"
The explanation given is "All link in formulas within a row must reference cells within the same row within the same precedent module"
I have also attempted creating a split of the costs on the shared cost output module for each company. When i attempt to edit the formula link to the shared cost module 100% of the cost is being linked through initially), to the share of the cost working in the shared cost module eg. Co 1 30% times total cost, it again throws up a Freeform Formula Warning.
I dont know whether to accept the warning and move on, or how to remedy the problem
Sorry, I meant to say that I have linked the shared pay costs module to each companies income statement module.
Currently each company is being charged 100% of the shared cost instead of their share (30%,30%,20%,20%).
When I attempt to edit the formulas to adjust for the share %, I receive a freeform formula warning
Hi Nick,
Based on my understanding of what you mean I would set up a category block in your shared module linking IN the company name for each company (unless you have done this already) listing each Company, and then use this category block to do the allocation of costs before you individually link each category OUT to the corresponding company. This way, if you decide to either add or remove a company (by mirroring one of the existing companies) in the future, it will automatically add a line for shared cost allocation in the shared module.
Sorry if that's a bit confusing, I have attached a really basic mock-up of what I'm trying to describe if that is of any help
T