Mr. Gundersen has a specialty in developing models and solutions.
He has developed scores of models for businesses across a spectrum of subject areas (including, but not limited to): Consulting, Food Manufacturing, Human Resource Consulting, Supply Chain, Industrial Manufacturing, Transportation, Property Management. His solutions have also helped traders to be more profitable in the stock market and aided individuals in building wealth.
FUNCTIONAL SOLUTIONS
How to overcome the problems of Excel in a large forecasting model?
Background. A major snack brand had challenges bringing together their corporate P&L and rolling forecast. The firm used Excel worksheets to assemble a board book of actuals and ongoing forward looking projections. Having recently been acquired, they reported numbers in two formats, one for the parent company and the other in an internal format used by the operating units. The numbers were assembled through a web of spreadsheets and database connections that were assembled at different times making a true, accurate snapshot nearly impossible. Leadership complained about difficulties with traceability that led to downstream trust problems between the finance team and key stakeholders.
Solution. Led efforts to develop a 3-way financial model for a major snack brand that consolidated data from dozens of workbooks, scores of worksheet tabs into a single model. The end result included: Power BI dashboards, an Excel-based interface for capturing forecast data, the ability to have multiple reporting structures, and the ability to capture data directly from Excel into a centralized model. Customer profitability increased significantly during a period of flat revenue due to better planning and forecasting capabilities. Greater trust in the numbers through time led to a better relationship between the finance team and its stakeholders.
Technology.
Relational Database | SQL Server
Multi-Dimensional Database | Olation
Dashboard | Power BI
End User Tools | Microsoft Excel
Add-ins | Power Excel
How much will we bill based upon the project forecast?
Background. A major health insurance carrier had 100+ active initiatives in progress at any given time and needed a way to forecast their consulting spend. The carrier and consultancy had negotiated that it would be more economic to bill-ahead to avoid incurring interest charges on unpaid invoices. Billing in this manner required having a project forecasting model that could accurately forecast based upon changes in start dates, resource levels, and end dates.
Solution. Developed a large project forecasting model that drove billings of $2 million/month in revenue. The carrier was billed directly based upon forecasted project activity from an Excel model. Multiple forecast versions were captured and reviewed with internal and external stakeholders before generating billings. “All eyes” were on this model across all stakeholders – the level of review was unprecedented. The model aided in improving project discipline across the organization was the central point of control for all billings.
Technology.
Relational Database | Microsoft Access
End User Tools | Microsoft Excel
How can we understand the current state of the business at strategic and operational levels?
Background. An International HR Consulting firm was crafting its 3-year strategic plan. The organization had growth plans and needed to reshape its workforce to align to the plan. Part of the process required a worldwide review for each line of business of key metrics – profitability, revenue, unit volumes, and compensation. A datamart was created to provide a single-source of truth for metrics being used in the analysis. Data was sourced from financial, regional sales systems, and the compensation system.
Solution. Several models were developed to provide metrics in support of the strategic review.
Sales Model – the model tracked programs sold to clients by level, function and type across all sales and service agents, across geographies, with daily, monthly, quarterly, and annual rollups.
Commission Model – the model tracked billable events originating at sales offices with splits to service offices that drove commission payouts. Clients, staff members, activity types, status codes, and other factors were used in the calculus that ultimately drove commission payouts. The model also exposed areas for potential fraud. Subsequent checks and balances were put in place to limit fraudulent activity on a go forward basis.
Compensation Model – the model captured elements of compensation across all career levels, lines of business, and geographies. Metrics included: base compensation, incentive comp, opportunity at 100% of goal, max opportunity, profitability, and more. The model had levers for each region, zone, and corporate function that drove compensation across all levels (from office level workers to the CEO).
Data from these models were critical inputs into the formation of the 3-year strategic plan.
Technology.
Relational Database | SQL Server
End User Tools | Microsoft Excel
How can we improve profitability in 90 days?
Background. A distributor with 80+ locations was faced with a dilemma – their accounting system provided a view into spending with suppliers, but there was no consolidated breakdown into overall purchases by product category. Furthermore, each of the locations had individual order entry databases that were NOT consolidated. As a result, the procurement team lacked a consolidated view of purchases: a) by supplier and b) by product category. This led to suppliers dividing-and-conquering the distributor from a pricing standpoint causing the distributor to pay more than they needed to for key products.
Solution. A special initiative was formed to consolidate purchases made across all locations into a consolidated supplier model. Much of the work in this initiative involved aligning metadata coming from the 80+ locations so that numbers could be rolled up by supplier and product category. Using Pareto principles, it took about 90 days to focus on the 20% of the suppliers generating 80% of the spend. Analysis on the 20% led to negotiations with key suppliers. Within six months of the start of this initiative, working capital was improved $1 million+due to improved rebate opportunities generated through negotiations.
Technology.
Relational Database | SQL Server
End User Tools | Microsoft Excel
What is the best model for compensating partners that balances corporate and partner interests?
Background. When designing a partner compensation model, a balance needs to be struck between the corporation’s interest in profitability and the partner’s interest in building a revenue stream. The corporation has a long term interest in driving new revenue. Partner’s will often sell the first deal, do relatively little thereafter to generate new business, and expect to continue receiving a high discount percentage on exiting business. Hence, the firm wanted a model that would auto adjust discount percentages based upon rolling sales volumes so that new business is always encouraged over existing business.
Solution. Developed a model that incorporated the above principles using Excel. The model was used to contrast the current program and new program in a way that spotlighted: (1) the company made more money under the new program, (2) partners were happier because they could build a revenue stream even though they would make slightly less, (3) driving new business was clearly incented over farming existing business.
Technology.
End User Tools | Microsoft Excel
US STOCK MARKET
Hide
How can I make money on stocks that I already own?
Background. The Wheel Strategy is an approach for trading options where the stock owner obtains “rent” (called premium) on stocks they already own for a specified period of time. This process can be repeated over and over again to greatly increase income generated from a portfolio. Trading The Wheel through most broker accounts requires that users review lengthy option chains to make decisions and enter multi-leg trades manually which can often lead to input errors which are costly. Furthermore, most brokers do not provide a means for tracking trade profitability across trading lifecycle. This leads to traders have to develop their own system for recording and analyzing historical results.
Solution. Developed a system that allows users to easily maintain watch lists, review the best puts and calls, trigger trades, and track trades across their entire lifecycle. Scorecards were built to monitor existing positions that provide the trader with actionable intelligence. Analytics were created to monitor current holdings, exits, and to provide a historical debrief on each trade. Finally, trading processes are automated with a real-time connection to TD Ameritrade so that trades are made seamlessly, without the need for double entry. The impact… income oriented traders make significantly more income versus simply collecting dividends.
Technology.
Relational Database | SQL Server
End User Tools | Microsoft Excel
Development Tools | Visual Studio .NET | Excel VBA
APIs | TDAmeritrade | Telechart 2000
Does the use of trading patterns provide an advantage of the dartboard approach?
Background. Trading patterns are used to identify when a particular condition occurs in the market. Once that the condition occurs the pattern is captured. Thereafter, back tests can be run to determine the optimal entry and exit conditions. In short, we wanted to consider some of the following questions: 1) Does one trading pattern offer a strategic advantage over another? 2) Across different trading patterns, what drivers are the most important to long term success? 3) Can it be shown that results are statistically significant?
Solution. Developed a 10-year simulation and analytics that identified high performing trading patterns that consistently produced 20%+ annualized returns. The simulation selected a market basket of stocks each night after determining which stocks met pattern criteria. Based on the market basket, stocks were randomly traded. A sensitivity analysis was on various entry and exit conditions. The simulation helped researchers to draw three conclusions: 1) certain patterns provided a strategic advantage over the dartboard, 2) certain patterns were more effective in downtrends by taking advantage of minor uptrends, and 3) the results were statistically significant indicating that select patterns could reliably be traded in the real world.
Technology.
Relational Database | SQL Server
End User Tools | Microsoft Excel
Development Tools | Visual Studio .NET | Excel VBA
How to trade this profitable strategy when it is tedious and record keeping is a pain?
Background. An Iron Fly is a four-leg options trade involving the purchase and sale of four different contracts as part of a strategy to benefit from stocks or futures prices that move within a defined range. Many day-traders execute this strategy; however, it is challenging to implement manually due to: changing market conditions that impact trade parameters, tedious processes to enter trades into brokerage accounts, the need to maintain detailed records for post trade analytics.
Solution. The solution starts by pulling the option chain for a target security, eliminating noise, and presenting only the most relevant metrics to the user (i.e., delta, bid/ask ratio, buy sell ratio, etc.). It also includes a breakeven analysis and calculation of max/min loss. The user can generate multiple scenarios at different points during the day based upon market conditions. The solution includes automated trading capabilities for trade entry and exit in order to take advantage of market movement. Finally, the solution logs all trades at a detail level to ensure that downstream analytics can be performed on all historical data.
Technology.
End User Tools | Microsoft Excel
Development Tools | Visual Studio .NET | Excel VBA
APIs | TDAmeritrade | Telechart 2000
How can I streamline daily processes and get the metrics I need to drive an accurate forecast?
Background. A client came to Coolera with a workbook that contained several years’ worth of option trading data for different trading strategies across multiple accounts. All of the data had been entered and calculated manually. They wanted a better way to capture data and calculate resulting metrics. They wanted to answer questions like: Which strategies were most profitable? How to accurately forecast based upon historical results? What was the net profit after commissions?
Solution. A workbook was created that first addressed data capture and metric calculation in a way that streamlined the amount of data entered and performed calculations automatically. Excel pivot tables and slicers were used to provide a visual and simple approach for reviewing end of day data and long-term history. For more complex metrics, portions of the data were saved to a relational database. From the database, queries used to calculate metrics which would otherwise be complex model in Excel. The project provided the client with the daily data needed to run its trading operation and built a base that could be used for future forecasts.
Technology.
End User Tools | Microsoft Excel
Development Tools | Excel VBA
PERSONAL FINANCE
Hide
Do I have enough money to retire? When can I take Social Security?
Background. Answering the question, “Do I have enough to retire?” is a complicated question. There are many variables that impact the answer. My age? Spouse age? What are our sources of funds before and during retirement? How much do we have in taxable and tax-deferred accounts? Will I semi-retire? At what age? What will my tax rate be? When to take Social Security? What is the IRS RMD age for my age bracket? What are my expenses? How will they change with inflation? How will my activity level (i.e., go-go, slow-go, and no-go) impact my expenses? What periodic one-time expenses should I plan for? All said, it’s complicated.
Solution. Coolera developed a retirement planning model that accounts for sources of cash, expense forecasting, spending throttles based upon retiree activity (Go-Go, Slow-Go, No-Go), RMDs, inflation, expected return on investment, and more. The model accounts for the most important factors that impact cashflow, shows withdrawals, account balances, and allows the user to change variables that impact outcomes. One of the most important aspects of this model is its ability to run an analysis that helps the user determine when to take Social Security.
Technology.
End User Tools | Microsoft Excel
How can I run the numbers for my upcoming home purchase?
Background. Purchasing a home is the biggest investment that most make but seldom do they have a simple tool to quickly run the numbers. Also, when there are multiple properties involved, how do you easily compare your options? The prospective buyer has to account for income, cash on hand, down payments, frontend/backend ratios, mortgage amortization, and more when assessing, “Can I fiscally handle taking on this asset and its associated debt?”. Additionally, “What is the change in monthly cashflow before and after the move?
Solution. Coolera developed a Home Purchase calculator to make it easy for buyers to run the numbers and compare numbers associated with each alternative. After capturing key information from the user, the model calculates cash reserves, mortgage payment (PITI), frontend ratio, backend ratio, cash flow before/after purchase, and the after-tax cash flow on a yearly basis. The model also provides an amortization table so that buyers can cross check the payment schedule with that from the mortgage company.
Technology.
End User Tools | Microsoft Excel
What is the impact of starting to invest later rather than right now?
Background. Watch Suze Orman Live and she will spout out numbers related to the impact of waiting to invest. Most people do not realize the humongous impact of waiting to invest vs. starting right now. Nor do they realize how much more of their income they would need to contribute to end up in the same place as someone who started early and contributed much less.
Solution. Coolera developed a savings comparison model to help people lock on the idea of why it is so important to start investing e-a-r-l-y. The model takes a few inputs like name, current age, starting/ending investment ages, monthly contributions, and expected rate of return. The outcome is that users can see how someone who contributed so much less can have so much more than their peer by starting to invest earlier in life. We should all take advantage of the miracle of compounding.
Technology.
End User Tools | Microsoft Excel
How can I easily understand the impact of growth rates and time invested?
Background. Do a Google search on time value of money and you often find pages with terms like PV, FV, AC, R, and N. Most users simply want to enter a few numbers and see a grid with age interest rate in the rows and age in the columns so that they can see across a range of rates and ages how much their contributions will grow.
Solution. Coolera has built a one screen calculator that makes it easy for users to see the miracle of compound interest in action. The calculator also provides users with an easy-to-understand display of the numbers so that they can see how monies compound over time without needing to understand PV, FV, AC, R, and N.
Technology.
End User Tools | Microsoft Excel