Zoho Analytics and Power BI for Dear Inventory
Connect Dear Systems to Zoho Analytics, Microsoft Power BI and other data warehouse tools for advanced reporting
This is a Zoho Dashboard using Dear data. It is also possible to combine Xero data, sales history from your old system, 3PL and freight data, Shopify, Google Analtyics, your internal systems and even spreadsheet data
- GrowthPath's Analytics Connector provides Dear master data tables, Dear transactional tables, and a set of special tables which combine data for advanced reporting.
- The Connector is based on GrowthPath's Dear cache and copes with high data loads. This technology also allows frequent data refreshes.
- It supports multiple Dear instances in multiple base currencies, consolidating to base currency using average monthly exrates
- Your data is housed by the GrowthPath Application Server, a dedicated client server (no co-tenanting of your data).
- Self-hosting: We deploy to AWS via Digital Ocean, but you can also host the GrowthPath Application Server in your own AWS, Azure or Google cloud hosting.
- Built-in data integrity checks and server monitoring of the connector and cache layers.
- Use advanced data transformation layers to clean up historical data
- Combine Dear data with Xero, QBO, Google Analytics, Shopify analytics, or with data from your freight software (we already have Shippit data feeds).
- Can serve as a form of backup for Dear Inventory data (with significant limitations, but at least equal to any other solution)
You can read the detailed product manual to understand the full capabilities.
Review the table schema from a test server
View the table schema: GrowthPath Dear Analytics Table Schema
Close-up of part of the data model
The Connector was originally designed for sending data to Zoho Analytics. It now makes the same tables available in a client-dedicated cloud database where it can be used by Power BI or any other analytics tools that can access Postgresql data. Postgresql feed for Power BI is in Beta.
The Zoho Analytics connector is mature, and capable of high volume sites. GrowthPath has built a robust API connector with excellent retry capability (this is open-sourced). The Connector also has automatic integrity checking. It uses rolling update techniques and can refresh even on an hourly schedule.
The GrowthPath connector can work with multiple Dear instances in different base currencies, converting results to a common reporting currency.
The Dear API load is minimised by the use of our advanced Dear cache, part of the GrowthPath Application Server. The GrowthPath Application Server can also share its Dear API load over multiple Dear API connections, to get much higher rate limits, if required. GrowthPath has clients doing hundreds of orders per day.
Currently the Analytics Connector prepares more than 25 tables. They are not just a copy of Dear's master data and transactional data, but they include tables which combine data for insights which go beyond Dear's reporting. For instance, on overview per order of what's been shipped, invoiced, is in fulfilment, credited and returned.
Data transformation and clean up: The Connector allows for a client-specific transformation layer, which allows historical data to be corrected and adjusted on the fly. For instance, you may describe an important color as Gray in one Dear system, and Grey in another: we can standardise that before the data goes into the Analytics tool.
Larger extract of the data model
Zoho Analytics for advanced reporting
Zoho Analytics is a fairly advanced data warehouse and reporting tool, 100% cloud-based. You can make powerful reports, charts and dashboards. In one place, you can combine Xero data, Dear transnational data, legacy data, Google analytics data, Shopify data, your own data feeds .... in an easy to use, browser-based tool, and it's not very expensive. If you have not used advanced reporting, it's a great place to start.
'Advanced reporting' means
- Reporting which focuses on the high level and allows a user to filter and drill down to immediately highlight interesting details
- A tool where a user can construct new reports to meet one-off requirements (example: preparing to meet a buyer)
- Use of visualisation
- A focus on business-drivers
- A tool that helps a skilled user explore relationships in transactional data to discover important trends
- A tool which combines data from different sources, including legacy data, POS data, website analytics, CRM insights.
The reporting tools in Dear meet some of these requirements, but they have little visualisation, are not very flexible, are limited to data in Dear and a user-licence is required per user.
You can get a feel for Zoho's capabilities with this 6 minute video
Using the GrowthPath Analytics Connector requires an annual subscription to the GrowthPath Application Server. This varies depending on the size of the server required. Typically, it is $1200 AUD.
You also need to provide a Zoho Analytics subscription (https://www.zoho.com/analytics/pricing.html). If you want five users, you can budget less than $100 AUD a month, or about 70 USD. Zoho is good value.
The connector is currently sold as a one-time fee. $4000 AUD for the connector alone. If you want a customised ETL layer (to clean up historical Dear data), load and integration of data from legacy systems or consulting around choosing KPIs and configuring dashboards and reports, GrowthPath will provide a fixed-price services project. Typically, clients pay AUD $4000 for onboarding.
Currently, we provide the Postgresql connector, for PowerBI and other analytics tools, at the same charge as the Zoho connector.
If you want multiple Dear instances, there is a one-time charge of $1000 AUD extra.
Power BI and other analytics tools
Currently in soft launch. A small number of slots are available for clients interested in an early/preview version.
Microsoft's Power BI Desktop is a reporting tool running in Windows. It doesn't have its own store of data, although it stores extracts of data based on reports created.
To use fresh data, it needs a connection to a database acting as a data warehouse. You can connect it to the dedicated analytics database provided by the GrowthPath connector, which runs on Postgresql, a supported database.
The tables have relationships defined so PowerBI can help the report creator link data from the transactional tables to the master data tables.
If you have another analytics or datawarehouse tool, you can extract the data from the same database. Even into Excel.
This avoids having to write a custom integration into Dear's API. The Growthpath connection uses an advanced and robust connection to Dear, which is much more advanced and tested than a one-off integration made by an analytics vendor.
The other big advantage is that the GrowthPath connector offers a solution to multiple Dear databases in different currencies, we have advanced synthesised tables for reporting which goes beyond Dear, we have automatic data integrity checking and we have a data transformation layer.
This is in Beta testing, which means we are selling the service but to a limited number of customers. If you are interested, please contact GrowthPath.
This series is about SME finance teams that are capable of engaging with the business. It is an adapted version of articles from tim-richardson.net
Author: Tim Richardson.
In this article I will cover capabilities, flexibility, some tips on development ideas that scale to a small team and building the team: recruitment, and moving people out of the team. Obviously this is a very ambitious topic to cover in one article. Leadership styles and organisational culture are never the same, yet they have a huge impact on this topic.
Cash vs Profit: is Cash still King?
The best decision-making techniques emphasise cash flow, not profit, and the theory of company valuation is based on cash flows. What is the difference between cash and profit, and why is the profit technique so widely used for reporting?
Most businesses use the default settings from their accounting system which usually causes the gross margin to be reported too high. This causes profit leaks due to mistakenly under-quoting, over-spending on sales promotions and mistakes about understanding the true profit of different customers. This is one of the easiest mistakes to repair. A few simple tweaks is all it takes to fix the problem by moving to a correct margin.
This article discusses correctly identifying variable costs, and how to get contribution margin from your accounting system, be it MYOB, Saasu, Xero or QuickBooks.
Part of GrowthPath's Profit Engineering Essential Skills series
Perpetual vs Periodic inventory systems
The Perpetual Inventory System is superior to the Periodic Inventory System: it provides gross margin of each invoice line and margins for each customer. You'll make better pricing, discounting and marketing decisions.
The best way to use information to boost growth is to implement business drivers, a business measurement approach which goes way beyond traditional accounting.
Part of GrowthPath's Profit Engineering Essential Skills series
Business Start Up Financials
Some notes on starting a business in Australia
Who is this for?
This book is for people looking to start a small business who want to understand basic business financials and who want to make a useful but simple financial plan covering the first two years. It focuses on practical realities, such as what you need from the business to replace the job you are giving up.
This book is designed to
- give you some important financial skills for running a small business
- to produce a financial plan for the first two years of your new business.
Starting a business is much more than making a financial plan. This book touches on some of these additional topics, but it doesn't go into much detail about making the full business plan.
This book is sold as a bundle with a spreadsheet. The spreadsheet is a practical, simple financial plan. Business financial plans can be high-level and long-term, but this one is short-term and detailed. It's designed to help you focus on the first two years of your small business. It's not designed for dot com entrepreneurs looking to raise $500m. It's more for people looking move into self-employment, perhaps with the idea of recruiting a few employees, or perhaps just to replace the income from a job.
This book doesn't cover all aspects of starting a business. It won't help you do marketing research, for example. A complete business is a clear story explaining the market opportunity and the points of difference between your business and competitors, integrated with a set of numbers. Numbers on their own are just an hour at a keyboard.
Also, the book doesn't cover in detail important steps such as choosing the right legal business structure, registering your business or getting the correct insurance. In this chapter, I make a short introduction to these points.
This book is sold in conjunction with a spreadsheet model, licensed to one person. This book therefore covers essential topics about business finance for small businesses, while at the same time developing a practical set of business plan financials.
Choosing a business structure
The business structure is the legal way your business operates. The decision has important tax and legal consequences. I advise you to discuss this with a tax accountant. This section is designed to give you high-level knowledge of the choices you face.
You can read more about business structures at the Australian Tax Office website. The most common source of professional advice is from a public accountant (a tax accountant).
The term “business” is a general term, applying to sole traders, companies, trusts and partnerships. “Company” is a specific way of running a business.
Most small businesses in Australia are sole traders. Despite the name, a sole trader may have employees. A sole trader is carrying on a business as an individual. This has two consequence: contracts, and tax.
When a sole trader enters into a legal agreement, such as a contract with a customer, an employee, a landlord or a bank, the relationship is the with natural person: with you. Usually, if an enforcement action is taken out against a sole trader, such as for a debt not paid, all the assets of the sole trader are in play, regardless of whether they are private or business.
A sole trader is tracked by the ATO using the individual's Tax File Number. If a sole trader has income as an employee and from a business, both income streams are reported on the same tax return, and both contribute to the tax bracket of the individual. Running a business entitles the sole trader to more tax deductions than an employee, but apart from that, the ATO doesn't see much difference. So a sole trader earning high income from a business will pay high rates of marginal tax.
Sole trader businesses are fast, cheap and easy to start. This is a big advantage. There is even a limited circumstance when you don't need to register a business name or get an ABN, but in nearly all cases it will be necessary to register a name. This costs less than $100 and it's an easy step to do online.
The tax and legal consequences of operating as a sole trader cause some dissatisfaction. The most common alternative is to run the business through a company or a trust.
A company has its own legal identity. For example, a company is able to sign a lease to rent premises. The company is not a living being and can not pick up a pen to make a signature: the company is operated by one or more directors, who are natural people. But the directors are not personally a party to the contract, even if they sign the contract: they are binding the company to the contract. Most of the time, a dispute between a company and some other party can not involve the directors personally. This legal protection is an advantage of a company structure. However, the legal protection is designed to protect directors in normal business life. It won't protect directors who break the law or breach certain duties, such as continuing to allow their company to build up debts when it has no chance of paying for them.
The company is seen as a distinct entity by the ATO. It has its own Tax File Number. Companies even have their own tax law: Company Tax. Some differences are that companies do not have a tax free threshold, and profit is taxed at a flat 30%.
Companies have flexible ownership. It's possible to start a business being the 100% shareholder, and then down the track sell 10% to another party in exchange for cash or something else of value. A sole trader can't do this.
Forming a company is more difficult than establishing a sole trader business, but it is still quite easy and usually takes only a few days. It's possible to do it online at ASIC for less than $500, but most people go through an accountant. Anything you pay over $500 is for the accountant's time. Apart from the higher one-off costs, companies require annual fees, annual reporting and for a business owner, it's an additional tax return on top of their individual tax return. Everyone's circumstances differ, but it's not uncommon for people to start as sole traders and move to companies when the business has grown. The biggest mistake is leaving this change too late. Note that when operating as a company, the owner will usually be regarded as an employee of the company. The company is then responsible for superannuation, PAYG tax, fringe benefits (if a car is provided) and WorkSafe.
A trust is also common among privately held businesses. A trust has many of the advantages of companies, but with more flexibility about how company profits are distributed, which translates to tax advantages in some circumstances. A trust requires a trustee, and a company is used for this mostly. So all the costs of a company exist, plus the costs of setting up a trust. These trusts are usually “Discretionary Trusts”, also known as family trusts.
Partnerships need to be mentioned. The traditional partnership is an ancient legal structure. Making someone your partner is a very profound public statement that you trust them to the utmost extent, because you are bound by your partner's business actions as if you have made them yourself. In some circumstances, this powerful statement of trust is useful: if you were an established lawyer or accountant a few centuries ago who wanted to expand business by bringing in someone new to your clients, making the new person your partner gave your clients confidence to deal with the new person, since to most intents and purposes they were legally dealing with you.
But this can backfire spectacularly if the person is not worthy of your trust. It's not too hard to find people who have been in partnerships. It is much harder to find someone who did it twice. Be very cautious before going down this route. Instead, consider a company with distributed shareholdings (50% each, for example).
Once again, this section is designed to give you a high level understanding. The specifics of business name registration are not covered in this book.
A sole trader will need to register the business name. The only exception is if you are trading under your un-adorned natural name. If you are starting a hair dressing business and you trade as Tim Richardson, that's ok. If you trade as Trim Cuts or even Tim Richardson Hairdressing, then you need to register.
The catch is that before you register, you need an ABN. You get an ABN from the ATO website. Google for “getting an ABN”.
Only real businesses can get an ABN. The ATO website has a questionnaire to see if you qualify, and then will take you through the process of getting one. Basically, you need to be starting a real business very soon, and a real business is one run with the intent of making profit. This is in contrast to a hobby business, which is not entitled to get an ABN. ABNs are restricted because they are the gateway to the GST system, and only real businesses are allowed to be in the GST system.
Individuals are normally allowed to have only one ABN. If you had one in the past which was deactivated due to lack of business activity, it is very likely that you will not get a new ABN instantly. Instead, an ATO case officer will investigate and probably call you to discuss your options, such as reactivating the old ABN or giving you a new one. You face a delay of up to a month.
Once you've got an ABN, go the ASIC site to register your business name.
This process has some automated checks to make sure your business name is reasonably unique.
The uniqueness check of your business name is not about giving you intellectual property protection, or to protect someone else's IP. Rather, the confusion check is to make sure that people you do business with are not at risk of confusion about who they are doing business with. To get protection for your business name, you need to look at trademarks. See the IP Australia website for more information.
Once you have an ABN and you've successfully paid to register your business name, you are read to begin business as a sole trader … unless you need specific business permits and licences. Each state has a website called BLIS, The Business Licence Information Service, and they have small business advisors (such as Business Victoria). These are two good places to learn more about the permits and licences you will need.
Registering a business: company
Most people will use an accountant to set up a company structure. It's possible to do it yourself via the ASIC website. When you establish a company, you will nominate the initial directors and shareholders, whether you will provide a constitution or use the defaults and a few other aspects. They are all important. It is of course possible to add more shareholders and directors after the company is formed but it is much more convenient to get it right at the start.
Even though I recommend using a third party to establish a company, it is a very routine process. You will almost certainly be provided with a constitution for your company, but it is a standard template. Setting up a typical company is one to two hours of work for the expert accountant. Most accountants pay third party processors to do most of the work. You need to pay for that expertise, but don't overpay. There is a surprisingly wide price range for this routine activity. You're starting a business and it's time to get wise: seek a few different quotes. Consider pure online options.
Establishing a trust
The typical discretionary trust setup involves the formation of a trust and the appointment of a company as the trustee. So it involves creating two different legal constructions. For an accountant in public practice, it's a routine matter, but it is more complex that setting up a pure company and will cost more money. Get quotes in advance, and shop around.
Another vital aspect of starting a business is arranging the correct insurance. You typically need insurance to protect your business from the damage of harming other parties. Harm can be physically, which is protected by public and product liability insurance, or financial loss, protected by professional indemnity insurance.
You can buy insurance over the phone from an insurance company. The smarter approach is to use an insurance broker. Business insurance is complex. Insurance brokers have a duty of care to get you the correct insurance. If you buy insurance directly, no one takes this risk of poorly chosen insurnace, except you.
Having insurance is not only to protect you. It protects your clients. They have increased confidence is using your advice and your products, because they know they are protected by your policy.
An aside: What about Franchising?
Franchising is very popular in Australia.
A franchisee runs an independent business, but gets a time-limited licence to use a brand owned by someone else. The franchise provides some basic training, some marketing support and the franchise sells you the products you need (usually they can’t force you to buy from them due to Australian law, but most often it is the only practical approach). You can take over an existing franchise, or buy into a new site. You pay up front either way, and then you pay ongoing fees (usually about 7% to 9% of sales).
Most franchise systems take away many basic aspects of running a business: you get a brand, raw materials, marketing material and possibly even software to run the business. The one key skill you still need to master is managing employees.
Some franchise downsides are
what you can do is very restricted,
your business is dependent on the franchise and you are not in the position of power
less opportunity to learn.
Franchising is very regulated, but despite that there is still wide variation is how it works and how good a franchise is.
This book does not cover franchises. The major issue is the value of the franchise: what you pay to get started. It is often cheaper to enter a “greenfields” site (a new franchise) but this is riskier because there is no proven track record of sales.
Before buying a franchise, be very comfortable with the information shared by the franchise to help you evaluate it. Some franchises are very helpful and informative, and some less so. Franchising is very highly regulated now … because it has ended badly for many people. There are business brokers who specialise in franchises: they are a great source of insight. Most beginners are probably best advised to buy an existing franchise.
The business plan: First Look
Purposes of a business plan
You may need a business plan mainly to meet a one-off requirements of someone else. Some examples are:
* To get government approval for a licence or permit
* To borrow money from a bank
* To get a lease on a building
These are one-time uses. You need a plan to create confidence in your business idea, but your own requirements for a business plan are secondary. In these cases, you feel you are producing a plan to satisfy a third-party. Once you have achieved what you need, you will set the plan aside. It's difficult to be very positive and enthusiastic about a plan made for this reason.
The real reason businesses should plans is to reach objectives and to be successful.
There are different way of measuring businesses success. In this book, we measure it financially. We say that the outcome of business is to create something of value: a business that can be sold.
From this point of view, the business plan culminates in a financial model: a projection of what cash the business is going to generate. The plan explains the assumptions behind the model: it is the story of how the business will work.
The financial model is broken down into periods of time: months, in our case. This means that progress can be measured regularly. A good plan will highlight what is causing deviations.
This book talks mostly about making the financial model, which in our case is a spreadsheet.
Everyone knows that it is very easy to fill a spreadsheet with numbers. So it is very easy to make business plans which have no credibility and no use. The narrative part of the business plan is crucial.
Achieving Credible Business Plan Financials
A successful business makes money by sustainably taking advantage of a market opportunity. A “market opportunity” means a need to be filled. “Taking advantage” means “making money”. “Sustainably” means you can keep making money because you have something special to offer which is hard to copy.
The business plan needs to prove what the market opportunity is and how big it is. This is the role of market research.
The next thing a good business plan must establish is how the business will differ from competitors in serving the market opportunity. This is the “taking advantage” part. A good business plan discusses what capabilities, products and investments are needed, and how the business can put term in place.
Finally, a good plan needs to discuss how the business will stay ahead of competitors and sustain the business.
With these in place, the plan makes a good foundation for a credible financial model.
Points 1,2 and 3 are not easy. Points 1 and 3 are highly specific to each business. Good market research is time consuming. It really means surveying prospective customers and doing a lot of legwork on data. We usually see short cut approaches: “The market size for sport shoes in Australia is $2.2 bln a year. We aim for only 1% market share, so we predict sales of $22 mln.” These business plans are binned by prospective investors. It's a bad plan, because it doesn't identify clearly what the opportunity is. It also begs the question: if it's so easy to get 1% market share, why isn't everyone doing it? This is top-down market research. In a business plan, it's usually of little use.
Instead, convincing market research is bottom up. It starts with real potential customers and how they respond to your product.
Point 2 is where you show that you know what you're talking about.
Point 3 is where you show that you can turn the opportunity and your expertise into a sustainable business, rather than just a one-time deal.
Bank and investors translate business ideas into cash-flows. You should view a business plan as a life-support mechanism for a set of financial projections. Keep this in mind, and your business plan will become powerfully credible.
You can only spend a dollar once. Learn how decision-making techniques let small and medium sized businesses make the right business investment choices.
US consultant Kaihan Krippendorff says "small businesses should scrap strategic planning" and we agree.
Three places to find growth opportunities
1: within your current customer base.
2: optimize pricing and target new customers.
3: acquisitions .
Why does GrowthPath recommend the Opportunity-based approach to growth?
Most traditional approaches to business planning and strategy assume that you can predict the future and influence the environment. In fact, the real growth opportunity for flexible SMEs is taking advantage of surprises.