Click here or scroll down if you would like to go straight to the Company A screenshot thumbnails.

Coy A has developed an innovative product for which demand is steadily increasing among property owners and managers across northern Australia and throughout the Pacific. In fact, demand – fuelled by word of mouth, favourable media coverage and traffic to the company website - is increasing at such a rate that the proprietors have been forced to engage several contract manufacturers to rapidly increase supply. Given the geographic spread of existing and potential customers, difficult logistical issues have arisen. While pointing strongly to a profitable long-term future, the rapid increase in demand is causing significant cashflow pressures for the business.

Coy A approached Wingarra BMI to discuss the matter. The owner was particularly concerned to understand the cashflow implications of each of a number of possible future pathways. Together, a number of strategies were mapped out, including the following:

• Raising significant equity to fund the construction of a manufacturing plant
• Continuing under a contract manufacturing model, but licensing operators in different territories
• Franchising different territories, both domestically and in a number of highly prospective foreign territories (with manufacturing operations either being controlled at the Coy A level or outsourced by the respective franchisees).

Wingarra BMI undertook preliminary modelling which pointed to the franchising option (with centralised control of manufacturing operations) as being best suited to Coy A’s long-term plans, including international expansion pathways. Based on this, very detailed modelling was then undertaken to optimise the franchise business model. This involved the design and construction of a sophisticated, tri-level (international, national and regional) model which allows for full testing of all technical and financial variables which were identified as being likely to impact upon the business across the model horizon of five years.

The resulting integrated model produces separate P&L, Balance Sheet, Cashflow Statements and ratio analysis outputs for each region, and also at the national and international level. Separate valuation modules, equity allocation and return on investment modules are also incorporated at each level so the owner has a basis for commencing contract negotiations with potential regional and national franchisees based on likely financial performance on a region-by-region basis.

Among the specific variables integrated within this model for testing are the following:

Head company (international/franchisor) variables:

• Country definition, including delineation of franchise territories – 12 territories allowed
• Customer categories – 10 allowed

Regional franchisee variables (required to calculate revenue-based royalty fees) – each variable set separately for each customer category and each franchise region:

• Client sign-up profiles – by customer category by month by year – Likely, Best and Worst scenarios
• Ave no of assets per site – producing assets and other assets
• %age of assets covered under safety program - producing assets and other assets
• Asset installation fee
• Asset yearly fee
• No of months after client sign-up assets come on line
• Sales collection profile – Month of sale (Ms), Ms+1, Ms+2
• Product acquisition schedule – Month assets installed (Mt), Mt-1
• No of products installed per two-worker team per day
• No of workers scheduled
• Rate per worker per day
• Ave no of work days per month
• Franchise acquisition fees (paid to national franchisee) – payable in three tranches
• Revenue-based franchise fee – payable to group IP company
• Revenue-based franchise fee – payable to head company (international/franchisor)
• Revenue-based franchise fee – payable to national franchisee – for safety program
• Revenue-based franchise fee – payable to national franchisee – other

National franchisee variables:

• Direct operations territory definition
• Customer categories – 10 allowed
• Client sign-up profiles – by customer category by month by year – Likely, Best and Worst scenarios
• Revenue and direct cost variables – same as for regional franchisees
• Franchise acquisition fees (paid to head company) (international/franchisor) – payable in three tranches
• Revenue-based franchise fee – payable to group IP company
• Revenue-based franchise fee – payable to head company (international/franchisor)
• Product acquisition fees – by national franchisee to head company
• Product acquisition fees – by regional franchisees to national franchisee
• Asset depreciation rate (DV)

Further head company variables:

• Asset manufacture timing code – Month of supply (Ms), Ms-1, Ms-2, Ms-3
• Asset manufacture costs – material - unit measure, price/unit, quantity required
• Asset manufacture costs – labour – price/hr, hours required
• Bulk discount rate on material purchases
• Net product distribution costs (per unit)
• Revenue-based franchise fee – payable to group IP company

 

Click on icon or label - larger screenshot will open in new page.

Note: model run on dummy data for screenshots.

Coy A inputs 1	Coy A inputs 2
Coy A inputs 3	Coy A inputs 4
Coy A special outputs 1	Coy A special outputs 2

 

Like us to prepare a proposal for a financial modelling assignment for your company or project? Please go to our Request for proposal page for details.