Do you keep hearing the term "Business Intelligence" (BI) at conferences, on websites and social media, and from Microsoft? Do you think it's something you need or want, but you don't know where to start? If your answer to any of these questions is "yes" or you want to improve your current BI methodology, then this book is for you. We are going to share with you some real-world solutions for GP users, build solutions, and then edit them to fit your specific needs.
Baseball, Jason Bourne, and James Bond—what do these three things have to do with Microsoft Dynamics GP or any accounting or enterprise resource planning (ERP) system?
Intelligence! I know you are probably saying, "What?" So let me explain.
Since the formation of the National League (United States professional baseball) in 1876, statistics for all major league players and teams have been kept. These statistics have been used to determine how well a player is performing and how well a team is performing. In 1977, author Bill James began writing baseball statistics books, developing these statistics into a methodology he called sabermetrics. He defined sabermetrics as "the search for objective knowledge about baseball."
For more information on books by Bill James published by ACTA Sports (www.actasports.com), see his website at www.baseballinfosolutions.com.
In 2002, Oakland A's Billy Beane and Paul DePodesta took the A's to the playoffs (despite the team having the third lowest salary in the league, and therefore, no star players) using sabermetrics. Until that point, baseball stats were used only to analyze a player's performance and not to determine whom to sign as players. This seems like a "duh!" moment now, but in 2002, it was radical. The success of the Oakland As has inspired many teams to hire full-time sabermetrics analysts. This fascinating story is captured in a wonderfully written book by Michael Lewis titled Moneyball, The Art of Winning an Unfair Game. The book was turned into a full-length feature film in 2011. If you haven't seen it, watch it. If you have seen it, watch it again with the perspective of someone who is working to achieve BI.
For more information about Moneyball, The Art of Winning an Unfair Game, W.W. Norton & Company, Inc., visit http://books.wwnorton.com/books/Moneyball/
So, obtaining accurate and timely baseball statistics makes way for the ability to hire players who can perform in the areas in which the team needs improved performance. This information allows more wins and possibly the ultimate baseball goal: winning the World Series.
James Bourne is a fictional character created by author Robert Ludlum who works (or worked) for the Central Intelligence Agency (CIA). First, I would like to address the intelligence of Mr. Bourne, and then I'll address the intelligence of the CIA.
In the first Bourne Trilogy book, The Bourne Identity, Mr. Bourne is fighting for his life, while fighting amnesia at the same time. Although he cannot remember who he is, he does recall how to assess situations. For some, this may be a natural trait, but for most, including Mr. Bourne, it was a learned trait. This quick assessment of the situation (for example, who may be watching me? Who may be looking for me? Who may have a gun? Who may be dangerous? and so on) is a powerful example of using timely intelligence. For Mr. Bourne, "real-time data analysis" is critical because he has no past information on which to rely.
Because of movies and characters such as Jason Bourne, we often think of the CIA as an organization that does nothing but spying. The CIA has three roles: collecting information (intelligence), analyzing the information they collect, and then performing (covert) operations based on this information if the President of the United States directs them to do so. Note that information, or intelligence, is a major part of what the CIA does and is.
For more information about the Bourne Trilogy books published by Bantam Books, visit www.randomhouse.com/book/104383/the-bourne-identity-jason-bourne-book-1-by-robert-ludlum.
Ian Fleming's character, James Bond, has infiltrated the lives of almost every movie goer and TV movie watcher at some point or another. We all know that Mr. Bond is a member of Britain's MI6 (Military Intelligence, Section 6). MI6 (the Secret Intelligence Service or SIS) is responsible for the United Kingdom's national security and economic well-being.
Now, I am not bringing this up simply because Mr. Bond is in an intelligence agency; I am bringing it up because it is intelligence that keeps Mr. Bond alive (along with his skills in weaponry, piloting, swimming, driving, charming women, and hand-to-hand combat; well, you get the point). Think about it: in the first part of each story, Mr. Bond meets Q to obtain "spy gadgets" that will assist him with his mission. At some point in the story, Q's gadgets always end up getting Mr. Bond out of trouble and/or saving his life, regardless of whether it is a belt jet pack, a Lotus Esprit that has submarine capabilities, or even a digital watch that doubles as a phone. Q always knew exactly what each situation would require by understanding the fundamentals surrounding Mr. Bond's mission.
Ian Fleming worked for Britain's Naval Intelligence Division during WWII, overseeing many operations, including Operation Golden Eye. He used the knowledge obtained during his service as background for his James Bond novels. So, Fleming used his intelligence to set the groundwork.
Basically, obtaining accurate and timely information helps Q provide James Bond with just the right gadget for the operation. It also allows MI6 to maintain the national security and economic welfare of the United Kingdom.
For more information about the James Bond books published by Vintage Books, visit www.ianfleming.com.
So now that we've spent a bit of time discussing baseball, Jason Bourne and James Bond, let me explain why these are great examples of business intelligence for your accounting and/or ERP system.
Baseball sabermetrics uses a player's or team's performance based on everything they have done. This is similar to our financial statements; for example, how did we do last month compared to the budget, the previous month, or even the year before?
Jason Bourne was an expert at reviewing his surroundings and protecting himself and his assets. Paying attention to what is happening in real time is critical to business success as well. This means entering information into your system every day so that data can be reviewed and captured. What good does it do for you to find out that you overspent 45 days ago?
Q always had James Bond prepared by analyzing what should be necessary for an operation. This is similar to how businesses and organizations budget and forecast. Being prepared for the future has a direct relation to studying what is occurring and what has occurred. If we do not forecast or budget, how do we know whether we are improving as per criteria such as the economy and industry trends?
Chapter 1, What Is BI and What Are BI Tools for Microsoft Dynamics GP?, explains how BI should be viewed to help users determine what they need to see. In this chapter, we tackle the challenge of deciding what should be viewed by thinking about "problems/goals" and not "reports." I also briefly describe the various BI tools available with Microsoft Dynamics GP.
Chapter 2, Business Intelligence for the General Ledger, is all about using General Ledger data as our source of information. This chapter covers examples and guidance on building a balance sheet dashboard (changes in cash, AR, AP, inventory, and profit/loss) and the ratio of AP to AR.
Chapter 3, Business Intelligence for Bank Reconciliation, is more about managing cash flow than monitoring a bank balance. This chapter contains examples and guidance on:
Building bank balances with last reconciliation dates
Where the money goes
Chapter 4, Business Intelligence for Payables Management, explains how proper use of the payables management module combined with good BI can improve cash flow. This chapter covers examples and guidance on building unapplied credit/payments, invoices on hold and vendor holds, and shareable aged trial balance.
Chapter 5, Business Intelligence for Receivables Management, helps you know what is owed to you. Making a sale is great, but collecting on it is even more important. This chapter will contains examples and guidance on building a shareable aged trial balance and customers' sales in the current year compared to the last year.
Chapter 6, Business Intelligence for Sales Order Processing, covers the heart and soul of all businesses and organizations—revenue (or income). This chapter covers examples and guidance on building the following: a dashboard of sales by customer, state, item class, and salesperson; a dashboard of open orders by customer, state, item class, and salesperson; and items sold by the manufacturer.
Chapter 7, More Business Intelligence on Sales Order Processing, demonstrates order management, making fulfilment easier and improving customer service. This chapter contains examples and guidance on building the following: SOP documents on process holds, items shipped within the last 30 days with tracking numbers, and a returned items report.
Chapter 8, Business Intelligence for Inventory Control, is designed to help you manage what you have in your inventory in an attempt to keep your inventory stock at a minimum, while maintaining what you need. This chapter covers examples and guidance on building a rolling 12-month turnover report, and items on which you most frequently adjust quantities and transfers.
Chapter 9, More Business Intelligence for Inventory Control, is all about items that are returned. This chapter contains examples and guidance on building the top 10 most invoiced and returned items, items with quantities that are not available for sale, and items that have not been sold within the last year.
Chapter 10, Business Intelligence for Purchase Order Processing, includes both distribution purchase orders and non-inventory/distribution purchase orders. This chapter covers examples and guidance on building purchase orders that await approval and open purchase orders older than 45 days.
Chapter 11, Miscellaneous Business Intelligence, covers additional content, including information based on employees, projects, and fixed assets. This chapter contains examples and guidance on building commission details and an employee list with personal details by location.
This book is designed for any GP user who loves working with Excel. You just need to be comfortable in Excel to complete the nontechnical steps. If you are not experienced in SQL Server, no worries! We have clearly mentioned when you need to obtain assistance and when you can take off on your own. We will be using the following applications, so please make sure you have them all on your system before you begin:
Microsoft Dynamics GP 2013 with the Fabrikam sample company deployed
Office 365 subscription (many of the examples described in this book can be completed with Excel 2013 Professional Plus)
Microsoft SQL Server 2008 R2 or 2012
Microsoft SQL Server Reporting Services (SSRS) deployed, with the free SQL Report Builder 3.0 installed
This book is for you, all GP users! It is designed to make your jobs easier. How? Through the use of BI provided by your Microsoft Dynamics GP, aiding you in making informed decisions. This book will share real examples of fellow GP users who fulfilled a requirement or desire with BI. The issue, the solution, and how the BI is used will be shared with you. Step-by-step guidance will be given for each report so that you can recreate it (or a variation of it) yourself. The very few areas that are more technical will be pointed out so that you can enlist help if you like. The bulk of this book's guidance requires no experience, with the assumption that you know how to save an Excel file!
In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "it will prompt you to override the previous Office Data Connection file or *.odc file."
A block of code is set as follows:
CREATE VIEW view_BI_RM_TRX AS SELECT CT.CUSTNMBR AS [Customer ID], CM.CUSTNAME AS Customer, CM.HOLD AS [Customer Hold], CM.CUSTCLAS AS [Customer Class ID], CM.CNTCPRSN AS Contact,
New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "Open a blank workbook in Excel, from the menu bar."
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of.
To send us general feedback, simply e-mail <[email protected]>, and mention the book's title in the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files from your account at http://www.packtpub.com for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
We also provide you with a PDF file that has color images of the screenshots/diagrams used in this book. The color images will help you better understand the changes in the output. You can download this file from: https://www.packtpub.com/sites/default/files/downloads/7241EN_ImageBundle.pdf.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title.
To view the previously submitted errata, go to https://www.packtpub.com/books/content/support and enter the name of the book in the search field. The required information will appear under the Errata section.
Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors and our ability to bring you valuable content.
If you have a problem with any aspect of this book, you can contact us at <[email protected]>, and we will do our best to address the problem.