Return to flip book view

Financial Reporting, Accounting, Planning, & Analysis with ExcelCharley KydUltra Excel Productivity for Business UsersIntroducing...Click to begin

Financial Reporting, Accounting, Planning, & Analysis with ExcelCharley KydUltra Excel Productivity for Business UsersIntroducing...

Your EDP Institute's ToolboxYour membership in the EDP Institute will include five critical Excel resources for accounting and financial professionals. If you want to accelerate your career, have a meaningful job, and enjoy your Excel work, flip the page to see the first resource...IFYOUMANAGEEXCELUSERS...The Institute will teach your Excel users how to skyrocket the value they deliver to you and your company.They'll gain the time and knowledge to add context to your reports—like periods with economic or weather events. They could include inflation rates, regional data, benchmark data, industry trends, competitor performance, etc. They'll have the time to look for ways to improve your company's growth, EBITDA margin, cash flow, etc.They'll have the time to complete more of the tasks and processes on your to-do list, like mitigating financial risk, researching outliers, developing scenario analyses, etc.And they'll have the time to work on their Professional Portfolios, which will improve your own Team Portfolio.

The Excel Data Plumber's Journal The main resource in your EDP toolbox will be the monthly EDP Journal,with its many supplementary workbooks. Step by step, it will cover...Excel flowbook-automation methods, so you can create new Excel work that you can update with just one command—period after period. New Microsoft tools for Excel, as they become available and useful.Excel reporting methods—including charts, dashboards, and Excel infographics—batch automation with VBA, rolling financials &forecasts, outlier reports, large datasets, interactive reporting andanalysis with slicers, flipbook report distribution, and much more.Excel analytical methods, including Monte Carlo forecasting, multiple regression, interactive data smoothing and transformation, data normalization, Pareto Analysis, seasonality analysis, etc. Excel formula methods, from SUM to dynamic arrays to LAMBDA.Help with Your Professional Portfolio, which you'll use to PROVEboth your professional and Excel expertise to anyone!

Unlike all other Excel training, the Journal will cover layers of relevant content, month after month—as suggested in a McKinsey article:THEEXCELDATAPLUMBER'SJOURNAL:The Journal'sUnique Training StrategyI agree! That's why the Journal focuses so heavily on the needs of accounting and finance professionals—and their managers.Meeting those needs isn't just about learning the Excel program; it's about learning to think. You need to understand Excel's capabilities, your data, and your managers' needs—deeply enough that you can reduce your Excel work to simple steps that you can implement easily.The Journal's initial layer is designed to make you as productive as possible with your current Excel scutwork, as quickly as possible. Then, layer by layer, you'll add new knowledge and skills to that foundation—so you can remember and apply what you've learned.

If you only learn about saws, and hammers, and chisels, and pliers, you'll have real problems building an ultra-productive factory. And if you only learn about functions, and commands, and PivotTables, and VBA, you'll have real problems building an ultra-productive report.In both cases, if you want to create outstanding results—rather than merely becoming a tool expert—you need to take a different path.To create ultra-productive and reliable Excel results—results you can create quickly then update and error test with one command—you must learn how to create real solutions in Excel, from multiple data sources. And also, by learning about external data sources that are relevant to your company, you'll be able to...Add new context to your reports—to enhance your readers' insight. Uncover patterns in your business environment—to help explain your current results and improve your future strategies, and...Include real data in your Excel portfolio—to help prove both your Excel skills and professional insights.In the EDP Institute, you'll work with data from at least the three sources shown here. Let's take a look at them...THEEXCELDATAPLUMBER'SJOURNAL:Learnfrom Multiple Data Sources

Learn Power Query with Economic DataFrom FREDThe Federal Reserve Economic Database, FRED, is the world's largest source of free U.S. and International economic data. It currently has more than 800,000 data series from more than 100 sources.From an Excel perspective, it's easy to set up Power Query to import FRED data. Each series uses the same file format, so we can use one set of Power Query queries to return any number of data series with one command.This is why your early Power Query training will use FRED data.From a business perspective, FRED can add valuable context to your Excel work. It has data about the U.S., its regions, states, counties, and each state's largest cities. It also has data about many other countries.FRED has data about inflation, commodity prices, employment, recessions, transportation, real estate, imports and exports, bank lending practices, interest rates, economic surveys, and a lot more.Your internal data tells you WHAT happened. External data like FRED data can help to tell you WHY it happened, and what might happen NEXT.

Learn to Use PQ with an API, By Working With Stock Market Data from SharadarSharadar aggregates investment-quality stock market data, which Nasdaq distributes. Both financial and market data is included—at a surprisingly low price. And a large amount of it is available for free.From an Excel perspective, you'll learn how to use Power Query with Nasdaq's API to return data from 14 data tables, each with a different structure. That's why most of your later Power Query training will rely on Sharadar data.FRED data introduces you to Power Query. And Sharadar data will take your PQ skills to the next level by adding APIs to your bag of tricks.From a business perspective, Sharadar normalizes their financial data. It's as if all public companies use the same 53 GL accounts. And you easily can map your GL Accounts to Sharadar accounts. That means you'll have the ability to compare your or your clients' financials to those of any public company, or any industry, or any financial condition.In addition to GL Accounts, Sharadar includes dozens of financial ratios, daily market caps, high-low-close values, price earnings ratios, trading volumes, beta, dividend yield, and so on—about common stocks, ADRs, derivatives, funds, and other equity securities.In short, with this data, you can get daily updates about how your customers, competitors, industry, portfolio, and so on are performing in the market—using any analytical methods you want in Excel!

Are Your Investments, Your Company, orYour Clients in Danger of Bankruptcy?Sharadar's data includes more than 3200 public companies that have gone bankrupt in the past 25 years. As a financial person or investor, here are some questions you now can research with that data:What financial and market measures are the best predictors of failure in the months before bankruptcy?Which industries are most susceptible to bankruptcies? Do different sectors have different indicators of likely bankruptcy?How well does the extent of insider trading predict bankruptcies?When companies have been on the edge of bankruptcy, and then turned themselves around, what financial changes did they make?In January 2024, Bing's ChatGPT listed these five companies as bankruptcy candidates: GameStop (GME), AMC Entertainment Holdings(AMC), Hertz Global Holdings (HTZGQ), Sears Holdings (SHLDQ), and J.C. Penney Company (JCPNQ). It based its decision on the Altman Z Score and the total amount of liabilities. Does your own company pass that financial test? Do your investments? Could you create a more precise and reliable screening method? Excel Data Plumbers gain the skills and data to answer questions like that. And learning those skills is much more valuable to you and your company than spending most of your time cranking out Excel scutwork.

Learn to Use PowerPivot, CUBE Functions,& DAX with Large Datasets in ExcelThere's one more thing about Sharadar that I need to tell you about...They give us a LOT of data!If you want to report or analyze individual stocks, that will be easy to do in your worksheets. No problem.But suppose you want to analyze GROUPS of companies—like the S&P 500, or sectors like Technology and Healthcare, or market capitalization categories like Mega-Cap and Small-Cap, or Large-Cap bankruptcompanies in the Industrial sector. The data to support any of those aggregations easily could exceed 20 million numbers!Worksheets can't handle all that data, but Excel's Power Pivot feature can—by using Power Pivot, DAX, Excel CUBE functions, and a PivotTable trick you've never seen before. In different ways these will allow you to analyze aggregations of large data in worksheets. So, down the road, when we start to add large datasets to your bag of tricks, we'll also get into those features of Excel.But let's be clear...My goal will NOT be to teach you to become an expert in these tools. My goal will be to teach you what you need to know, to do what you typically need to do with large datasets. And we'll use Sharadar's financial and market data to teach you those things.

Learn Power Query andan API to TrackNear-Real-Time"Currency" DataMetalpriceAPI offers near-real-time data about "currencies," a term they apply to precious and other metals, and also to actual currencies.Their data is free if you use it in low volumes. And it's almost free until you start to use a lot of it.From an Excel perspective, you'll learn how to use Power Query with an API that's quite different from the Nasdaq API. That experience should make the next API you use with PQ much easier to set up for yourself.From a business perspective, MetalpriceAPI offers the bid, ask, and settled prices for the precious metals, gold, silver, platinum, and palladium. And they offer prices for the industrial metals, aluminum, copper, and zinc.Additionally, they offer U.S. exchange rates for 151 currencies. On a personal note, one of the exchange rates they offer is for the Cayman Islands Dollar, the KYD—which happens to be my last name.Also, from an investment perspective, MetalpriceAPI not only offers the price of gold, they offer the economic category of the measure that's most strongly correlated with the price of gold—the broad U.S. Dollar exchange rate, which has a high negative correlation with gold. But if you want to compare the price of gold to the dollar's broad exchange rate, you'll need to get that exchange rate from FRED. Or else, you could create your own broad rate from those 151 rates!

The Excel Data Plumber's MastermindWhen you join the EDP Institute, you'll gain access to the Excel Data Plumbers' Mastermind, which is an unlisted LinkedIn group.With the EDP Mastermind, you'll join a worldwide support network of Excel Data Plumbers—where you'll ask and answer questions about Excel, finance, analytical methods, companies, data sources, APIs, etc.You'll upload and download files, brainstorm project ideas, share ways to add value to your company or career, and collaborate with other Plumbers by industry, profession, geographic region, and so on. You'll also be able to get feedback from other Plumbers about your posts, articles, reports, and Professional Portfolio—and get ideas about ways to improve them.Some mastermind groups cost tens of thousands of dollars per year. But the EDP Mastermind is part of your EDP Institute membership.IFYOUMANAGEEXCELUSERS...This is your Mastermind, as well. But would a Manager's Mastermind or a Leader's Mastermind also be useful? If so, click the Message Me bubbles at the right side of your screen. Then please tell me how you could use it.

You'll also be able to ask job-finding questions in the Mastermind.In his book, 7 Rules of Power, Professor Jeffrey Pfeffer discusses research into job referrals. It found that a network of casual acquaintances is the best source for referrals. That is, weak ties are better than strong ties, where strong ties are the ones you have with your close friends and family, as in the triangle-shaped figure above.Weak ties are like most of the ties you’ll develop through the Mastermind. Here's why they usually work better for job-finding:You’ll get higher-quality ideas by interacting with professionals in many different regions, industries,and related professions.You’ll have more weak ties than strong ties.Your weak ties will tap into many more diverse business and social networks than your strong ties will—as illustrated here.You'll likely increase your LinkedIn recommendations. If your Excel help and examples in the EDP Mastermind impress people, they'll likely add recommendations to your LinkedIn Profile. And having a wide variety of business professionals to recommend your work could tip the job-finding scales in your favor.THEEXCELDATAPLUMBER'SMASTERMIND:Job-FindingQuestions for Professionals

An April 2022 McKinsey survey found the top five reasons people gave for quitting their previous job. Here they are, and how EDP can help:Reason 1: Lack of career development and advancement. But your skilled Plumbers will have the time to complete high-value projects for you, which are MUCH more valuable to their careers and your company than manually cranking out Excel scutwork—month after month! Reason 2: Inadequate total compensation package. But as the next page illustrates, you can afford to pay your Excel Plumbers more because they'll be MUCH more productive. Reason 3: Uncaring and uninspiring leaders. But to inspire your Excel users, pay for their membership in the Institute. And help them to use what they learn—with ideas like Brown Bag Wednesdays, Brainwork Days, and Concierge Plumbing. Also coach them, of course.Reason 4: Lack of meaningful work. But when your Excel users replace scutwork time with brainwork time, they can do MUCH more meaningful work for you, your company, and their careers.Reason 5: Unsustainable work performance expectations. But it's MUCHeasier to meet reporting deadlines—and avoid burnout—when your Excel users can produce and error-check each report and analysis with one command—and then get home in time for dinner.IFYOU'REAMANAGER:The EDP Institute Can Help YouTo Keep Your Excel Users Onboard

The Financial Benefit Of the InstituteIf you're a manager, the two highlighted numbers in the table below show the financial benefit of flipping your Excel users' brainwork time from 15% to 85%, as Accenture suggests you can. By doing so, you could gain about 5.5 full-time-equivalent employees (FTEs) PER EXCEL USER, PER MONTH—or the equivalent of about $52,000 per month.And if you're an Excel user, the highlighted numbers show the potential value you could give your employer—per month—when you switch from strategy-free Excel reporting methods to the EDP Strategy. But that's not all. Line B shows that each Plumber can free up more than five times more hours to do brainwork. But—as McKinsey suggests—each new hour can be up to NINE TIMES more productive than in the past. So your new Excel work can be significantly more valuable to your company and your career than this table suggests! Does it sound too good to be true? Even one-tenth of that performance improvement can bring a significant financial benefit to your company.

Bonus 1:Your Talent Showcase: The LinkedIn Business Insights FactoryFirst, when you publish an article for your portfolio anywhere online, you'll want as many managers as possible to see it.So the Business Insights Factory is a listed LinkedIn group—with write-access only for Institute members. For managers around the world, it's set up to promote your articles about using Excel with public business data. That is, it's a talent showcase designed to help you network relentlessly. The more brainwork you can do with public data, the more business insights you can generate. And the Factory will be your opportunity to showcase your brainwork to managers around the world. And when managers visit articles written by other Plumbers, they'll be just a click away from the Factory, which will link to all other Plumbers' posted content, including your content.Second, the Factory will be a place where you can improve your own knowledge about business topics that involve data in Excel. Whether it's about finance, or economics, or statistics, or taxes, or modeling, or whatever, your fellow Plumbers can teach you career-enhancing topics that you or your own managers need to know.So the Factory will teach both you and your managers how to advance your professional knowledge—not just your Excel knowledge.

Bonus 2:Related TutorialsAt times, you'll need to learn about non-Excel resources. So you'll get my related tutorials with the Journal. Their topics will include:The Federal Reserve Economic Database (FRED) for U.S. and International economic data. You'll often use this data as context for your company's reports and as real data for your Portfolio.Sharadar data, with financial statements for more than 10,000 public companies. And many of them have decades of financial history.LinkedIn, for working with your Profile, the EDP Mastermind, and the Manager's Insights talent showcase.ChatGPT, the Artificial Intelligence (AI) program, because it can fill in gaps in your Excel knowledge and also amplify your Excel creativity.The flipbook that I'm using for this presentation, because it's an outstanding tool for distributing Excel reports and analyses. I'll update these PDFs frequently, as you ask more questions, or you share your own discoveries in the Mastermind. And I'll likely add more tutorials as technology changes.

AccentureBain & CompanyBoston Consulting GroupCEODeloitte GartnerEYHarvard Business ReviewMcKinsey & CompanyMIT Sloan Management Reviewstrategy + business (PwC)Wall Street JournalWithin a few months after you join the Institute, you'll be able to begin the work of creating flowbook versions of your current Excel reports. And after that's done, you'll be looking for more projects to do. Your boss will have some ideas, of course. And if you become a Concierge Plumber, you might check with your VIP. But you should have your own ideas, as well. That's why I'll regularly send you ideas and insights about Excel projects, curated from my past projects, books in my library, 50 years of notes in my filing cabinets, ChatGPT ideas, and a long list of sources that I subscribe to and scan for relevant projects—sources like these:IFYOU'RE AMANAGER...Do you remember that the main reason many employees are quitting is that there aren’t enough opportunities for skills development or career advancement? With your support, yourPlumbers never should have that problem!In short, if you're curious enough about your data, and you look for good ideas, you always should find insightful contributions to make.Bonus 3:Excel Project Ideas & Insights

Bonus 4:Semi-CustomHelp & TrainingIf you subscribe now, you'll be a founding member of the Institute. So to make up for the small LinkedIn groups at first, I'm giving founding members like you two special bonuses. Bonus 4 will be semi-custom Excel help and training.If you send me anonymized copies of your major reporting headaches, along with a short description of how you currently generate the report, I'll be sure to cover reports like yours as quickly as I can in your training. (That is, because you'll be a founding member, your questions and examples will help to influence the direction your training takes.)And in a training supplement, I'll explain how to send me the information I need and how to remove all company data from the example workbooks before you send them to me.I'll sort your examples into groups of similar reports, and then explain how to set up reports from one group—like a P&L—so you can change its fiscal year in one step, and update it periodically with one command. And then, in other issues, I'll explain how to set up other Excel work from your examplesTake advantage of this bonus so you can start to reduce your scutwork as quickly as possible, and before my work to support this bonus takes up so much time that I must discontinue it.Also, by the way, if a manager wants specific reports even more quickly, check out the last page of this presentation with an additional option.

When you join the EDP Institute today, here's what you'll get today...You'll get Issue 1 of the monthly Excel Data Plumber's Journal, which will teach you how to use Excel's Power Query feature to import data from CSV files, Excel Tables, and web tables. It will be delivered as a flipbook and as a PDF file.You'll get the Financial Reporting Supplement, which teaches you how to sign up for a free subscription to Sharadar data on Nasdaq, and how to get started with using their API. It also includes a workbook with Power Query queries for all 14 stock market files.You'll get access to the EDP Mastermind, where you can ask and answer questions, and start to network with other Excel Data Plumbers. You'll get the ChatGPT tutorial, with examples of how to use it to get step-by-step instructions for doing just about anything in Excel.And you'll get the Semi-Custom Help.pdf document, which explains how to take advantage of Bonus 4.Flip to the next page to learn more...Here's Everything You'll Get Today

If You're an Excel User...You should expect to at least double your Excel productivity, andprobably much more, which will allow you to produce significantlymore value for your employer, which should boost your careerconsiderably.You'll catch up and keep up on both Excel and professional skills thatshould delight your current and future employers.You'll gain more power to choose a better work-life balance whilealso having more fun at work and growing your professional networkmore quickly.If You Manage Excel Users...Your VIPs should be much happier because you'll give them manyvaluable reasons to be delighted with you and your Excel users. Theyparticularly should like their Concierge Plumbers.Your Excel users should be much happier, more productive, and lesslikely to quit—because you'll boost their career, add projects to theirresume, slash their scutwork, and introduce them to your VIPs.You'll save thousands of dollars per month by reducing the need forhiring new Excel users, for most other Excel training, and for mostother analytics work that now is done by programmers—rather thanby your domain experts in finance and accounting.Flip to the next page to join the Excel Data Plumber's Institute...What's the Value of Membership?

How to Become a PlumberIFYOU'RE AMANAGER...You'll get far more insight from Excel users who have the time, energy, and encouragement to exercise their curiosity about yourcompany's data. You need Excel Data Plumbers! Whether you're an Excel novice or guru...If you're burned out from your Excel scutwork...If you're not doing meaningful work...If you're not adding new accomplishments to your resume.If your career is stalled out, or you don't feel valued at work...If you're not delighting your managers...If your pay isn't growing faster than inflation...If you don't have enough time for your personal life...If you're not growing your business network... ...then you really need to join the Excel Data Plumber's Institute!As I said earlier, I'm giving founding members like you two special bonuses. Bonus 4 gives you semi-custom help with your reports. And Bonus 5 is a 53% reduction in your membership fee. Your fee is $397 $187 per monthand you can cancel at any time. Click this button to join NOW!Join Now

Your Career Opportunity...As the table on the next page illustrates, your company could enroll 51 Excel users in the EDP Institute for about the same cost as hiring one new employee. And as this table shows, when you increase your brainwork time from 15% to 85%, you'll increase your productive time by at least five average Excel users. And that brainwork time will be MUCH more productive.I don't know you, your boss, or your company's financial condition, so there are no guarantees about your career. But when you can prove that you can do the work of even two average Excel users—and probably many more of them—you logically should increase your pay by a lot. And you should have the power to make career choices you can't make now. Over the next few years, stagflation could bring the most challenging time you've had in your career. You'll need all the help you can get. Join NOW to prepare yourself for the challenges and opportunities ahead. Membership is $397 $187 per month and you can cancel at any time. Click this button to join NOW!Join Now

Who would be the most qualified to increase the quantity and quality of your department's reports, analyses, and other management insight? One new hire, creating improvisational reports? Or as many as 51 of your current users transformed into ultra-productive Excel Data Plumbers? The cost is about the same—as the table illustrates. And transforming even a few of your Excel users into Excel Data Plumbers could give you more results and higher-quality results than a new-hire ever could. Your existing Excel users already know your company's challenges, your data, and your people. But to deliver the results you need, they also must know how to use their knowledge ultra-productively with Excel.To manage the challenges ahead, you'll need to transform your Excel users into Excel Data Plumbers, starting now. Sign them up today!Membership per Excel user is $397 $187 per month and you can cancel at any time. To sign up your Excel users, send them the link to the previous page and ask them to sign up for the training, using the billing method you specify.If You ManageExcel Users...

Too many management reports are a time-suck. They take a long time to digest. They fail to provide the insights that managers need. And they're difficult to change.Do you have problems like that? I can design flowbooks that give you the reports you want, in the format you want, in the frequency you want. And then I can help your Excel Data Plumbers to create and maintain them. I also can help your Plumbers to become your own Business Insights Factory, so they have the time and the training to find and report new insights in your data.If you need custom reports before your Plumbers have reached the first level of their training, I can prepare them for you. To learn more, introduce yourself and your organization to me by email, using the address and subject shown here. Tell me briefly about the reports you need, and the version of Excel you use. Then I'll get back to you immediately.I'm here to help.Charley KydIFYOU'REAMANAGER...Do You Need Strategic Help with Your Management Reporting?