Clcik here or scroll down if you would like to go straight to the Company E screenshot thumbnails.
Coy E has developed a highly unusual consumer product for which it has been able to carve out a significant special-purpose market in a number of countries. The company wanted to investigate a number of possible commercialisation pathways. Pre-eminent among these were options to establish company-owned stores or to market via a franchisee network.
The company wanted to determine whether higher long-term profits could justify the greater equity dilution required under a capital raising program sufficient to support the much higher cash requirements of the company-owned route. In essence, this became a question of the comparative ROI for the founders of a high capital/higher profit company-owned store option as opposed to a low capital/lower profit franchise option.
Coy E approached Wingarra BMI to design and construct a financial model and prepare an information memorandum for presentation to a number of potential investors. Wingarra BMI designed and constructed a model which allows for the testing of multiple variable sets simultaneously in order to assess the feasibility of these alternative commercialisation pathways. It also allows pre-qualified investors to work through alternative scenarios with the proprietors.
Among the specific variables the model allows to be tested, both independently and interdependently, are the following:
Store rollout parameters:
- Month of store openings (1 to 60) – Likely, Best and Worst scenarios – 20 stores
- Company-owned V franchised code – selected via 1 or 2 toggle for each store
Market parameters:
- Estimated no of target customers in store area – by store
- Assumed market penetration %age by year - initial sales – by store by year
- %age of first year sales by month (later years assumed constant rate throughout year) – M1 to M12 – by store
Cash V credit sale parameters:
- Cash sale customers - %age (balance = credit) – by store
- Cash sale prices – by store by year
- Credit sale prices – by store by year
Drop-off after initial sale parameters:
- %age of cash customers exiting after initial sale – by store
- %age of credit customers exiting after initial sale – by store
Ongoing sale parameters:
- %age of ongoing cash customers electing for multiple yearly services (assumed every six months) – by store
- %age of ongoing credit customers electing for multiple yearly services (assumed every six months) – by store
- Ongoing service sale prices – cash – by store by year
- Ongoing service sale prices – credit – by store by year
Product sale parameters:
- Product sales only (PSS) - ave quantity purchased (mls) – cash – by store
- PSS - ave product sale prices (per ml) – by store by year
- Product sales only (PSA) - ave quantity purchased (mls) – credit – by store
- PSA - ave product sale prices (per ml) – by store by year
Franchisee parameters:
- Franchise acquisition fee – by store
- Franchise renewal fee - %age of franchise acquisition fee payable every third year – by store
- Revenue share to franchisee under franchise option – by store
Customer service staff requirement parameters:
- Ave no of days store open per month – all stores
- Ave work hours per day – all stores
- Ave service times (mins) – all stores
- Ave training time and class size – credit customers only – all stores
Direct cost parameters:
- Service staff salaries – by store by year
- Receptionist salaries – by store by year
- Service machine acquisition costs – by store by year
- Training materials – by store by year
- Product order timing – relative to month of sale – all stores
- Product costs (per ml) – by store by year
- Monthly store operating costs – rent, telecoms, marketing, etc – by store by year
- Store fit-out costs – non-deductible/part-depreciable – by store
- Store fit-out costs – franchisee share – by store
- Store fit-out costs - %age of fit-out costs which are depreciable – by store
- Store fit-out costs - depreciation rate (DV)
- Store fit-out costs - timing code (relative to month of store opening – by store
Click on icon or label - larger screenshot will open in new page.
Note: model run on dummy data for screenshots.
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.
|
