 Utilizing BI tools, such as Power BI, can add several levels to a company’s internal reporting and data insights. Interactive and dynamic dashboards can be composed relative to organizational departments such as supply chain, finance and accounting, operations, and marketing; but it can also be tailored to the level of the audience, from the managers “in-the-weeds” to the executives. However, in order to get the most out of the visualizations and analysis for the intended user, dynamics, scalability, minimal maintenance and integrity in calculations, a good deal of planning should be implemented to optimize your time in actual development.
This article will flow through some thoughts on the layout and planning of a dashboard, inclusive of dynamics and functional components such as a Drill Through on four-levels of products, a Bookmark, and several Measure calculations (afterall, if I was going to piece this together I also wanted to test some ideas). If you don’t use Power BI, that is okay, many of the planning and data organizational steps will apply to any BI or model development.
0 Comments
 Statistical Methods : This is a quick example of an ARIMA model on a QoQ Revenues dataset that I chose because it is particularly ugly. While QoQ revenues is not typically the period over which forecasting would be done for revenues from operations, it was a readily available set. Statistical ARIMA forecasting is particularly useful when there aren't easily available and associated variables to use as predictors like you might use in multivariate regression models.
We are going to look at time-series data for forecasting. Time-series simply refers to data that has been collected at regular intervals over a time period. Because it is collected regularly, this data has both its value and the time it was collected as characteristics. Examples are closing stock prices (end of day - daily), monthly revenue (monthly), and hourly electricity usage (hourly). Using time-series data requires an understanding of Stationarity. The most important thing to understand about forecasts is that they rely on historical data and the composition of that data. This fact is an influential assumption to any forecast since you are utilizing the historical relationships that produced your time-series values and then pushing these same relationships into the future to produce forecasted values. You can certainly adjust these relationships, but then you may have to decompose your values by identifying those relationships before producing forecasts … not always an easy task. This is why nearly all forecasts begin with the assumption that any influence that creates past values will continue in the same fashion into the future.  Let's just get right to the point. The vast majority of your "analysts" are not anything more than process maintainers. This is the reason you can't get any actual models constructed from scratch or are stuck with models nicknamed "the beast" comprising sheets and sheets of formulas that are no longer used, but no one understands how to clean it out. This is also the reason all your budgets come from Three Statement financials and all your variance "analysis" is little more than the difference between budget and actuals. As we move forward this will only get worse given the usage of AI as the crutch for lack of aptitude, knowledge and general ability. As I predicted, there will be a reliance on professional certifications and proctored exams proving the passage and competence of knowledge for hiring in the very very near future. Data Analytics
Analysis is the ability to reason and make conclusions from events and data. This analysis can be descriptive, the most basic of data analysis which is the best most companies can get out of their "analysts". Descriptive analysis is simply telling you what a set of data indicates about a given topic. In the case of a business, the data is most often financial. You may get a set of P&Ls or Balance Sheets, from which the basics are told to you of rising or tightening margins, increased expenses, or changing asset and liability positions. You have sat through these meetings covering financial accounting ratios going up or down. Well ... You could read this yourself. If you know a ratio is supposed to go up to be considered positive and its not (or vice versa) then something needs to be done.
 Company: Machine-tool manufacturer of highly specialized, custom lasers machines and coating machines. Executive Summary
 Executive Summary
 Before I get into the example I would like to comment. Companies often have issue with field services and planning due to the mobility aspect, but if there is a segmentation, the planning becomes clear. Think of each vehicle as a brick and mortar location … that moves.
First, set up the planning as if it was a location. There are the lease payments, equipment investment, inventory investment (parts), maintenance, and all the admin that would accompany an actual store such as general liability insurance and licensing and permitting. You will eventually forecast revenues and have your AR and AP that is directly attributable to the field service, but don't think of it as mobile yet. Think of it more like a service station that has a certain number of available hours for the day for appointments, only these appointments will have more idle time between them due to the drive time. If you are working out a plan for field services for the first time, or considering changes, you should probably work with different sizes of vehicles. The reason is that you must understand the investment in the equipment and inventory load out that you would like to have given a certain size of vehicle. As you begin with a large, ideal vehicle, you can then choose another smaller, more economical vehicle and perform the same the loadout. Of course, the smaller vehicle will force a selection of equipment and inventory that you believe is absolutely essential given less capacity for the equipment. This planning will also have you questioning what types of service will be most common since this will determine equipment and inventory carry and have you working out how other services could be completed if they are beyond the current equipment and inventory. These are all good questions because they get you to consider data collection and future analytics.  This field services case study is that of a transportation NPV (net present value) model for bus purchases. There are initially two options, that of a purchasing of six, 32-Passenger buses or four, 52-Passenger buses, but I have included the use of solver to maximize the NPV if we were to select a mix of the two types using the same budget constraints. I always try to include additional analytics into the options, but I will walk through the analysis and the details. The buses will be used on a full day schedule of 480 miles, a 16 hour day, of which is included a 4 hour rush hour segment that the buses will operate at full capacity. Below are the specifications collected for each of the buses.  The available information suggests that the budget is at least $720,000 given that we are able to purchase six 32-Passenger buses at $120,000 each. This will provide the budget constraint for the maximization of NPV when running the solver for mixed purchases.
We will use a straight line depreciation over 8 years with the respective salvage values just for modeling. The wages per hour are assumed to be the same for a full-time driver and a part-time driver and the operational expenses are per bus per annum.  An alternative pricing and planning strategy is desired given the failing of volume forecasts for new product introductions and the shortfalls experience in budget plan to actual profitability.
Introduction of new products into the market requires collective efforts on the part of the marketing, operations/engineering, and finance teams. There are three primary pieces, Price to Demand elasticity and subsequent demand curve, the direct costs and traceable fixed costs through the process, and the required return (ROI) for the product to be a success. As with any model and data garbage in is garbage out. Diligent data planning for collection and analysis of the customer base by the marketing team is required, consistently and thoroughly to arrive at an accurate demand curve for a pricing model. Since most pricing for a CPG market is represented by a monopolistic competitive environment (economic term) of numerous products all slightly differentiated, a deep understanding of what differentiated characteristics are of value and what that value is worth is needed. In general, any new product will often incorporate these value driving characteristics and assist in the determination of the demand curve. In this case, there were two initial scenarios of pricing and marketing campaign planning:  TL;DR You are missing out on your candidates because your are allowing those with no understanding of how to identify skills to evaluate and make decisions on a skilled individual. This means you waste time interviewing those identified as a potential candidate due to their “time” and “keywords” relationship without any consideration to the quality of that time. How does that make sense? You need real skills, and identifiable understanding of the required skills. This is what you aren’t getting. It’s obvious in the repeat postings and time for a reevaluation and overhaul of the system you use.  Snip From a Tweet from TheJobChick X Feed I am not sure how many people try to track events key to their industry, there are just so many things going on in life. But, I, have my regular routine of tracking events … at least to the degree possible. Every morning I run through various financial websites and news sites checking for stories that may be impactful to the economics behind a business. Of course, currently, we have wars, precious metals, inflation, fraud, datacenters, layoffs (often underreported) and AI. I probably missed more than a few since these are just a broad scope, but one other topic that I regularly check is the listings for positions within finance and accounting.
Sure, finance and accounting interests me, its my wheelhouse, but it is also a sign of the health of a company and the job market. Some of the first companies to layoff in rough times are in financial services or in the finance and accounting department, definitely in the FP&A (financial planning and analysis) focus since it is usually beyond the scope of reporting compliance and therefore trimmed down. This isn’t to say sales and programming (retail/logistics/technologies) hasn’t been hacked to bits, but I can’t watch every area. What I have seen, however, over the past 4-5 years of tracking position postings has been more than interesting, something I believe few upper management are even aware of … TL;DR : Clustering as a method for forecasting could be effective if the automation was present to cluster as customers were entered. It could prove to be very accurate for cash flow forecasting, but also planning for future resources, at least given the data sample used I am a big fan of using tools that are generally associated with ML (machine learning) to conduct analysis on data sets, especially for FP&A work. Many people don't realize that the programming and development that goes into ML data fitting, filtering and analysis is just mathematical theories and equations that can also be used on a scripting basis. I prefer to do some pre-analysis on data to get an understanding of what the data is saying about a given subject matter so I make use of the data capabilities of platforms such as R and Python in addition to Excel. Even if you are not a developer, I am not, you can still find many useful tools that enable you to glean quick insights into data that would be difficult to do within Excel or a BI tool directly. I refer to this as the pre-prep phase before getting into model construction, costing, or forecasting and is the actual data analytics that should go into financial analytics else you could be missing out.
The great thing about data analytics and data science is that you actually don't need to know anything about the data that you are given and you can still begin to draw conclusions. I have been given data-frames with no labels other than row1, ... row10,000 and column1, ..., column30 and relationships can still be found; clusters, dominating dimensions, and even regressions predictors if one wants to run through several arrangements of least squares calculations. This was just a test, but I like to point out that data speaks if you understand data regardless of industry, a huge misconception in hiring practices these days as this skillset is desperately lacking in the corporate finance and accounting functional department. But that aside, let's see how data talks. |