Client

Our client is one of Australia’s largest retail food brands with over 140 locations across Australia and New Zealand.

Brief

The brief was to develop a corporate planning model that was modular, scalable and capable of modelling individual retail outlets and have that consolidated into a whole-of-company view.

The client was keen to harness Modano’s modular capability to develop an Excel-based historical and forecast model that would enable the client to:

• model different types of retail outlets (which were based on ownership structure);
• add new outlets with a single click;
• accurately capture the implications of changes in the ownership structure; and
• value the sum of underlying outlets, as well as the consolidated company.

Solution

The build took two months to complete and resulted in two models being developed:

• Stores Model; and
• Corporate Planning Model.

The Stores Model was based around the following main modules:

• Master – Central database comprising of information relating to individual stores / retail outlets (e.g. store type, remaining useful economic life, historicals etc);
• Stores Group – comprised of a group of stores, each containing their own detailed operational assumptions and forecasts;
• Financial – EBIT Statement for each store; and
• Reporting – Dashboard showing several key metrics for each store.

The Corporate Planning Model was based around the following main modules:

• Import – imported relevant outputs from Stores Model;
• Financial – integrated three-ways financials showing consolidation; and
• Reporting – summary dashboard and ratios summary.

Overall, the models allowed the client to forecast and budget for the Group and support various Board and Executive Management Processes such as capital management and risk management.

Modano Benefits

When combined with the Modano software, the model design facilitated the following automated model development processes:

• monthly roll-forward;
• adding new operational groups; and
• adding new stores (and removing) as required.