Data Modeling with Microsoft Excel E-Book

Bernard Obeng Boateng

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 262

Veröffentlichungsjahr: 2023

Beschreibung

Microsoft Excel's BI solutions have evolved, offering users more flexibility and control over analyzing data directly in Excel. Features like PivotTables, Data Model, Power Query, and Power Pivot empower Excel users to efficiently get, transform, model, aggregate, and visualize data.
Data Modeling with Microsoft Excel offers a practical way to demystify the use and application of these tools using real-world examples and simple illustrations.
This book will introduce you to the world of data modeling in Excel, as well as definitions and best practices in data structuring for both normalized and denormalized data. The next set of chapters will take you through the useful features of Data Model and Power Pivot, helping you get to grips with the types of schemas (snowflake and star) and create relationships within multiple tables. You’ll also understand how to create powerful and flexible measures using DAX and Cube functions.
By the end of this book, you’ll be able to apply the acquired knowledge in real-world scenarios and build an interactive dashboard that will help you make important decisions.

Leseprobe

Data Modeling with Microsoft Excel

Model and analyze data using Power Pivot, DAX, and Cube functions

Bernard Obeng Boateng

BIRMINGHAM—MUMBAI

Data Modeling with Microsoft Excel

Copyright © 2023 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Group Product Manager: Kaustubh Manglurkar

Publishing Product Manager: Apeksha Shetty

Book Project Manager: Farheen Fathima

Senior Editor: Sushma Reddy

Technical Editor: Devanshi Ayare

Copy Editor: Safis Editing

Proofreader: Safis Editing

Indexer: Manju Arasan

Production Designer: Prafulla Nikalje

DevRel Marketing Coordinator: Nivedita Singh

First published: November 2023

Production reference: 1221123

Published by Packt Publishing Ltd.

Grosvenor House

11 St Paul’s Square

Birmingham

B3 1RB, UK.

ISBN 978-1-80324-028-2

www.packtpub.com

I dedicate this book to the Almighty God, who is the light of all knowledge, for guiding me to this milestone. To my wife, Georgina Konadu Boateng, and children, Kristodea, Kristoadom, and Owuraku, for their sacrifices and support. To the Finex Skills Hub team in Ghana for their immense support throughout this journey.

Foreword

This Data Modeling with Microsoft Excel you have in your hands or are reading via a digital device is going to change you for good. Bernard Obeng Boateng has taught dashboard creation and data modeling to thousands of people at conferences and classrooms over the last decade. I have seen him deliver data modeling sessions at Global Excel Summit London, Excel Virtually Global, Australia, Excel Office Hours Nigeria, and at other conferences around the world. For all the eight years I have known Bernard, he has been building reports that involve a deep level of data modeling for companies.

This book is structured to be engaging and practical, with a focus on making you instantly ready for the real-world application of the knowledge. Bernard uses a conversational style and lots of illustrations to ensure you don’t have to read any sentence twice. You will find this book both enjoyable to read and exciting to practice the data modeling concepts it presents.

Bernard starts by helping you understand what data modeling is and why it is very important in your analytical work within Excel. He then takes you on an exciting journey through the paradise that is hidden within Excel and called Power Query. After getting you well-grounded in the use of Power Query, he walks you through how to serve up interactive computations through Power Pivot and Cube functions. Finally, he teaches you how to build beautiful, interactive, and robust dashboards that will be a joy for managers and decision-makers to use.

I congratulate you on taking the first step on this exciting journey to becoming a very proficient, creative, and value-delivering Excel reports builder. You will find the knowledge gained useful across all analytical tools and change your way of thinking in relation to data wrangling. Bernard has poured his more than a decade of experience and his refined teaching style into this book.

I would advise that you don’t just turn the pages but that you simultaneously practice what you learn so that the teaching becomes ingrained in you.

Michael Olafusi Microsoft Excel MVP & Founder of MHS Analytics Inc.

Contributors

About the author

Bernard Obeng Boateng is a 3x Microsoft Excel MVP, Microsoft Certified Trainer and Excel Expert with over 10 years working experience in Banking, Insurance, and Business Development. He has a first degree in BSc. Admin from the University of Ghana Business School and is certified in Business Analytics from the World’s leading Business School, Wharton. He also holds the Advanced Financial Modeler (AFM) certification from the Financial Modeling Institute, Canada and the Financial Modeling and Valuation Analyst (FMVA) certification from the Corporate Finance Institute, Canada.

He is lead trainer at Finex Skills Hub, www.finexskillshub.com , a digital skills training hub dedicated to delivering workshop-styled training in Power BI, Financial Modeling and Microsoft Office Suite in Ghana. He is co-founder of Business Evolution Systems & Training Ltd www.best-gh.com , a business development and training consultancy firm in Ghana. Bernard has an active online audience of over 19,000 followers on LinkedIn and has provided training to several corporate firms and individuals in Ghana.

Sample of Bernard’s work portfolio can be found here: www.obboat.com

YouTube: https://www.youtube.com/@FinexSkillsHub

I want to thank the people who have been close to me and supported me, especially my wife Georgina Konadu Boateng and children Kristodea, Kristoadom and Owuraku. I am grateful to the Finex Team: Osbert, Ferdinand, Ernest, Dii-Winga, Priscilla, Daniel and Naa for always providing the support needed.

About the reviewers

Jennifer Viola is a passionate and innovative Data Analytics and Business Intelligence professional with 20+ years of experience working with data across many industries.

She has a Bachelor’s of Business Administration (2006) and a Masters of Investigations (2022). Her experience combined with her education successfully niches between Business and IT and makes her a well versatile addition to any company looking to improve their bottom line using data to achieve their goals and overall success.

She is published author for TDWI’s Journal of Business Intelligence (2016), and she has mentored and been an educator in the industry since 2016. She enjoys helping and seeing others have successful careers in Data Analytics, too.

Tejanshu Salaria has completed his undergrad degree in Electronics and Communication engineering and master’s in information system with major in finance. Business Development, Finance and analytics are the fields which excite him to innovate something and to build a career where he can help people to grow and make an impact on an organization. He has experience in leadership and has served fortune 500 companies for 7+ years now. He has Significant Experience in: Business Intelligence and Strategy, Data Analysis, Data Engineering, Project Management and Financial Modeling.

Victor Momoh is a Petroleum Engineer during the day and an Excel Enthusiast at night. He is passionate about data and numeracy, so his love for Microsoft Excel does not come as a surprise. Victor is a certified Microsoft Excel Expert (2010,2019/365). An international speaker at various global events and one who is passionate about sharing knowledge through LinkedIn, his YouTube channel, ExcelMoments, and a telegram group, Nigerian Excel Users (NEU) which he runs alongside some Excel MVPs.

Victor’s contributions to the Excel community got him the award of Microsoft Excel MVP back in 2022 and in 2023.

I would like to appreciate Bernard for putting out such quality material and for the privilege to be a technical reviewer for this book.

Table of Contents

Preface

Part 1: Overview and Introduction to Data Modeling in Microsoft Excel

1

Getting Started with Data Modeling – Overview and Importance

Understanding the concept of data modeling

Comparing a one-table analysis to multiple-table analysis in Microsoft Excel

Practical use cases for a data model

The accountant

The salesperson

Introduction to Power Pivot, Excel versions, and installation

How do I install Power Pivot?

Exploring the features of Power Pivot

What is DAX?

Best practices with Power Pivot

Summary

Questions for discussion

2

Data Structuring for Data Models – What’s the best way to layout your data?

Data structuring – understanding the three golden rules

Understanding data redundancy

Problems caused by data redundancy

What is data normalization?

Denormalized and normalized data

Understanding table relationships

One-to-many relationship

Many-to-many relationship

One-to-one relationship

Understanding dimension and fact tables

Fact table – sales

Dimension table – product

Dimension table – customer

Dimension table – date

Understanding the role of primary keys and foreign keys

Summary

Key terms in this chapter

Questions for discussion

3

Preparing Your Data for the Data Model – Cleaning and Transforming Your Data Using Power Query

Understanding connections and queries

Connections

Queries

An overview of the Power Query editor

Getting your data type right

Add Column or Transform?

Merging and appending data using Power Query

Summary

4

Data Modeling with Power Pivot – Understanding How to Combine and Analyze Multiple Tables Using the Data Model

Adding queries/tables to your data model

Adding columns to your data model

Creating calendar tables

Creating relationships using primary and foreign keys

Understanding the different types of schemas (snowflake and star)

Creating hierarchies

Hiding fields and tables from client tools

Summary

Part 2: Creating Insightful Calculations from your Data Model using DAX and Cube Functions

5

Creating DAX Calculations from Your Data Model – Introduction to Measures and Calculated Columns

DAX as a calculated column or measure

Creating your first measure – where to go

Common DAX functions (time intelligence, FILTER, CALCULATE, and so on)

Understanding row and filter contexts

Editing your DAX formulas

Summary

6

Creating Cube Functions from Your Data Model – a Flexible Alternative to Calculations in Your Data Model

What are cube functions?

When do we use cube formulas?

Exploring cube functions in Microsoft Excel

CUBEVALUE

Adding slicers to your cube formulas

CUBEMEMBER

CUBESET

CUBESETCOUNT

CUBERANKEDMEMBER

Spilling cube functions with dynamic array formulas

Summary

Part 3: Putting it all together with a Dashboard

7

Communicating Insights from Your Data Model Using Dashboards – Overview and Uses

What are dashboards?

Factors to consider when laying out your dashboard

Purpose and audience

Clarity and simplicity

Hierarchy and organization

Consistency and visual harmony

Interactivity and user controls

Data visualization techniques

Accessibility and documentation

Common dashboard elements

PivotTables

Charts

Text boxes and labels

Images and shapes

Sparklines

Conditional formatting

Making your dashboard interactive

Slicers

Hyperlinks

Summary

8

Visualization Elements for Your Dashboard – Slicers, PivotCharts, Conditional Formatting, and Shapes

Laying out your dashboard using mockups or wireframes

Enter mockups and wireframes

Practice your mockup in Excel

Using shapes as cards in your dashboards

Inserting conditional formatting and sparklines

Sparklines

Conditional formatting

Adding and formatting charts

Inserting slicers for interaction

Summary

9

Choosing the Right Design Themes – Less Is More with Colors

Understanding your users

Know your audience’s preferences

Accessibility is key

Consistency is crucial

How to reduce noise in your presentation

Using colors to tell a story

Associative colors

Using gradient colors for scales

Highlighting key data

Summary

10

Publication and Deployment – Sharing with Report Users

Protecting your workbook

Skill one – restricting access to sensitive parts of your workbook

Collaboration

Skill two – inviting people to collaborate

Publishing a view-only online version via OneDrive/SharePoint

Skill three – using OneDrive/SharePoint as a means to share your final work

Exporting your data model to Power BI

Summary

Index

Why subscribe?

Other Books You May Enjoy

Packt is searching for authors like you

Share Your Thoughts

Download a free PDF copy of this book

Preface

Data Modeling with Microsoft Excel is an essential resource for anyone looking to harness the full potential of Microsoft Excel in data analysis.

This book delves deep into the advanced features of Excel, specifically focusing on Power Pivot, Data Analysis Expressions (DAX), and Cube Functions. These tools are pivotal for anyone working with large datasets and seeking to perform complex data modeling and analysis efficiently.

This book is not just about learning the functionalities; it’s about understanding how to apply these tools in real-world scenarios to derive meaningful insights from your data. Whether you are a business analyst, a data scientist, or someone who regularly works with data in Excel, this book will equip you with the skills to transform the way you interpret and present data, enhancing your analytical capabilities and decision-making processes.

Who this book is for

This book is for Excel users looking for hands-on and effective methods to manage and analyze large volumes of data within Microsoft Excel using Power Pivot. If you are new or already conversant with Excel’s Data Analytics tools, this book will give you insight into practical ways to apply Excel’s Power Pivot, Power Query, DAX and Cube functions to save you time on routine data management tasks.

What this book covers

Chapter 1, Getting Started with Data Modeling, introduces the concept of data modeling, explaining its significance in today’s data-driven world. Readers will learn the basics of data modeling in Excel and understand why it is a crucial skill for effective data analysis.

Chapter 2, Data Structuring for Data Models, focuses on organizing data to optimize its use in Power Pivot. The chapter provides strategies and best practices for structuring data tables to facilitate efficient data analysis and modeling.

Chapter 3, Preparing Your Data for the Data Model, guides readers through the process of cleaning and transforming raw data into a more usable format using Power Query, setting the stage for effective data modeling.

Chapter 4, Data Modeling with Power Pivot, will show you how to use Power Pivot for advanced data modeling, including techniques for connecting tables using relationships.

Chapter 5, Creating DAX Calculations from Your Data Model, introduces DAX (Data Analysis Expressions), teaching you how to create powerful calculations and measures directly within your data models.

Chapter 6, Creating Cube Functions from Your Data Model, explores Cube Functions as an alternative to DAX, offering flexibility in extracting and manipulating data from the data model.

Chapter 7, Communicating Insights from Your Data Model Using Dashboards, emphasizes the importance of dashboards in communicating data insights. It covers how to create effective dashboards that convey the story behind the data clearly and compellingly.

Chapter 8, Visualization Elements for Your Dashboard, will show you various visualization tools such as slicers, pivot charts, conditional formatting, and shapes, and how to use them to enhance the effectiveness of your dashboards.

Chapter 9, Choosing the Right Design Themes, delves into the principles of design in the context of data presentation, focusing on the strategic use of colors and themes to create visually appealing and informative dashboards.

Chapter 10, Publication and Deployment, guides you through the process of publishing and deploying your Excel models and dashboards, ensuring that the insights are accessible and understandable to end-users, online and offline.

To get the most out of this book

An understanding of Excel’s features like Tables, Pivot Tables and some basic aggregating functions will be useful but not a requirement to come along.

Software/hardware covered in the book

Operating system requirements

Excel

Windows, macOS, or Linux

Power BI

Conventions used

There are a number of text conventions used throughout this book.

Code in text: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: “We can connect these two normalized tables using the common column Course ID.”

Bold: Indicates a new term, an important word, or words that you see onscreen. For instance, words in menus or dialog boxes appear in bold. Here is an example: “we can have separate lookup tables that will give us the details for each Customer ID, City, and Product ID.”

Tips or important notes

Appear like this.

Get in touch

Feedback from our readers is always welcome.

General feedback: If you have questions about any aspect of this book, email us at [email protected] and mention the book title in the subject of your message.

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata and fill in the form.

Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.

Packt is searching for authors like you

If you’re interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.

Share Your Thoughts

Once you’ve read Data Modeling with Microsoft Excel, we’d love to hear your thoughts! Please click here to go straight to the Amazon review page for this book and share your feedback.

Your review is important to us and the tech community and will help us make sure we’re delivering excellent quality content.

Download a free PDF copy of this book

Thanks for purchasing this book!

Do you like to read on the go but are unable to carry your print books everywhere? Is your eBook purchase not compatible with the device of your choice?

Don’t worry, now with every Packt book you get a DRM-free PDF version of that book at no cost.

Read anywhere, any place, on any device. Search, copy, and paste code from your favorite technical books directly into your application. 

The perks don’t stop there, you can get exclusive access to discounts, newsletters, and great free content in your inbox daily

Follow these simple steps to get the benefits:

Scan the QR code or visit the link below

https://packt.link/free-ebook/9781803240282

Submit your proof of purchaseThat’s it! We’ll send your free PDF and other benefits to your email directly

Part 1: Overview and Introduction to Data Modeling in Microsoft Excel

This part is structured to guide both beginners and intermediate users through the foundational concepts and advanced applications of data modeling in Excel. Here, we delve into what data modeling is, why it is essential in today’s data-driven environment, and how Excel can be a powerful tool in this realm. You’ll also learn how to clean and transform your data using Power Query, ensuring it is in the best possible shape for modeling.

This section has the following chapters:

Chapter 1, Getting Started with Data ModelingChapter 2, Data Structuring for Power PivotChapter 3, Preparing your Data for the Data ModelChapter 4, Data Modeling with Power Pivot

1

Getting Started with Data Modeling – Overview and Importance

Think of how a business plan lays out the written roadmap for companies to understand and make sense of all the moving parts of their business: the drivers, resources, and processes required to achieve success. This plan often serves as the manual companies consult to understand how all the pieces of the business puzzle fit together.

In the same way, large and complex datasets require a structure or a blueprint that allows data analysts to visualize how different data points can be structured and connected to deliver insights for action or decision making.

This underscores the significance of data modeling in the field of data analytics, and it is precisely where data modeling in Microsoft Excel proves invaluable.

In this first chapter of the book, we will break down the concept of data modeling within and beyond Microsoft Excel. The chapter will cover the advantages of using a data model to manage multiple sources of data. You will go on to understand some practical use cases on how to use the data model to look up and reference related tables and understand the architecture and features of Power Pivot, the engine for data modeling in Microsoft Excel. Throughout the journey, best practices will be highlighted and covered.

At the end of the chapter, you will be in a good position to understand how data modeling can help you connect and manage datasets from multiple resources to deliver insights quickly and efficiently in your data analytics project.

The following topics will be covered in this chapter:

Understanding the concept of data modelingThe importance of a data model in Microsoft ExcelPractical use cases for a data modelIntroduction to Power Pivot in ExcelBest practices with Power Pivot

Understanding the concept of data modeling

Data modeling is the process of structuring and organizing data in a way that it can be easily analyzed and reported. Think of it like arranging books in a library. If you just threw all the books into a room, it would be hard to find what you need. But if you categorize them by genre, author, or publication date, it becomes much easier to locate a specific book.

Similarly, data modeling helps in organizing data so that you can easily derive insights from it.

Just as a business plan serves as a blueprint for a company, a data model acts as a blueprint for creating and visualizing the relationships between different datasets. This activity is known as data modeling.

It serves as the backbone for your visuals and calculations, allowing for more complex data analysis. A data model gives you a visual or conceptual view of how the datasets you are working with connect to produce the results or insights you need. Getting it right can be the difference between well-optimized data analytics and analytics filled with redundant data that offers little insight.

Microsoft offers the following definitions for a data model in Excel and Power BI:

A data model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook.Data modeling is the process of analyzing and defining all the different data types your business collects and produces, as well as the relationships between those bits of data. By using text, symbols, and diagrams, data modeling concepts create visual representations of data as it’s captured, stored, and used in your business. As your business determines how data is used and when the data modeling process becomes an exercise in understanding and clarifying your data requirements.

In Excel, a data model can help you connect to one or many tables and summarize the datawith PivotTables.

Figure 1.1 – Comparing a one-table analysis to multiple-table analysis

Besides Excel, the concept also applies to other database management systems, such as Power BI, Access, Oracle, and so on.

With a data model, analyzing your data becomes easier because you can clearly define each dataset, the role it plays, and how it connects to other datasets to give you the results you need.

Comparing a one-table analysis to multiple-table analysis in Microsoft Excel

Often, we store our data in a range of cells in Microsoft Excel. Converting data stored in a range of cells into a table makes it easier for you to reference the dataset for calculations and further analysis using a PivotTable. This is called Structured Referencing. Standing in the range of cells, you can insert a table in Excel by going to Insert > Table in the ribbon or simply pressing Ctrl + T.

When data is stored in a table, simple aggregations such as SUM, AVERAGE, and COUNT can be performed using the table name and the column. For instance, summing sales from a table named Table1 can be simply done using =SUM(Table1[Sales]).

Data in the table can also be used in a PivotTable. This way, when the source data changes with the addition of more rows or columns, the PivotTables automatically update with the new data in the table when it is refreshed. This avoids the need to update the source reference of cells in the PivotTable.

Most Excel users tend to store all their data in one table for their analysis. This can be referred to as One-Table Analysis. There is nothing wrong with this approach. However, if the data you are working with grows and you have a situation where you need to add other tables to your analysis, it can become complex with just one table and a PivotTable.

Creating a data model in Power Pivot in Excel allows you to have access to multiple tables for your analysis without the need for complex lookup formulas. It improves performance and gives you a clear overview of how the tables relate.

Let’s now explore some of the key advantages of using a data model in Power Pivot.

Here are some reasons to use a data model:

It gives you a broad overview of your datasets or tables. This ensures that all the tables and datasets you require in your model are accurately captured. Take a look at the following example data model for a sales report.

Figure 1.2 – A Diagram view of an example sales report data model

You’ll realize that even though there are several tables used in the creation of the final dashboard, the data model gives a good overview of how each table connects and contributes to delivering the final results.

It is an abstract representation of the real-world situation you are analyzing. With the data model, you are in a good position to generate accurate measures and calculations for the KPIs in your report.The data model helps reduce the occurrence of redundant data. That is, the repetition of the same data at different points in your dataset. This helps improve performance when your data increases.The data model can also be a good blueprint for developing web or frontend applications for your dataset. For example, PowerApps, AppSheet, Caspio, and Squirrel are some of the applications that can benefit from a well-designed data model.

Most of these are low-code tools that use data models as a blueprint to create interactive apps for users. The data model then becomes an indirect way for developers to document the data that will be required to build these apps.

So far, we have covered what a data model is and the reasons you should consider using data models to structure datasets that are broken up into relational components and that need to be connected and properly visualized in order to effect the maximum efficiency and insight that is possible.

In the following section, we will look at some practical use cases of a data model. We will look at the case of an accountant and a salesperson and see how data models can help reduce the efforts and processes required in analyzing data.

Practical use cases for a data model

This section explores practical use cases of data models in various workplace scenarios.

The accountant

Mr. Owusu Yeboah is a chartered accountant. He enters his accounting records in the Journal tab, a table he has created in Microsoft Excel to record the Date, Description, Amount, Debit Account, and Credit Account of all transactions.

Figure 1.3 – Journal showing accounting entries

In another worksheet named COA, he has a table containing his chart of accounts with account codes, sorted to classify the various accounts into assets, liabilities, equity, revenue, and expenses. The other columns in his chart of accounts describe how each account has to be treated to produce a monthly and an annual financial statement.

Figure 1.4 – Sample chart of accounts

For Mr. Owusu Yeboah to determine the ins and outs of each account or create a trial balance, he would need to use a lot of lookup formulas to connect the two tables. Aside from this, when new data is added to the tables, he must manually update all his workings to capture the new entries. Using Excel tables to store data is one way to avoid manually updating calculations when your data changes.

How does a data model help in this situation?

Using a data model, Mr. Owusu can upload and connect the two tables using common columns. These common columns are used to establish a relationship between the tables and make it possible to create a data model. He can then create an extra calendar table to help him create a month-on-month or annual financial statement.

A calendar table in Excel is a special table with a series of sequential dates that helps you keep track of dates and times in your data. It’s great for looking at things such as sales or expenses by day, month, or year. If your data is missing information for certain dates, a calendar table makes it easy to spot those gaps so you can fill them in. This ensures you’re not missing out on important details when making decisions.

In addition to helping Mr. Owusu Yeboah sort and analyze his data over time, a calendar table makes sure that all the date information in his various tables lines up correctly. This helps him avoid mistakes and makes it easier to combine different sets of data. It also lets Excel perform more advanced calculations for him, such as figuring out his total sales for each month or calculating averages over specific time periods.

His data model will look something like the following screenshot:

Figure 1.5 – A screenshot of a data model with accounting data

This will help him easily capture new information in the journal and chart of accounts and create a dynamic financial statement for his users.

The salesperson

Ferdinand Attobra is a sales executive with Finex online electronics shop. Daily, he is required to create a report that captures top-performing products, branches, and customers to his supervisors.

Figure 1.6 – Sales transactions

To create his report, he downloads four datasets from his sales software:

Transactions: This captures all the revenue as well as the cost of sales per transaction. The table also has fields that identify the customer, product, and store information related to each transaction. This is represented by Customer ID, Product ID, and Store ID.

Apart from the Transactions table, there are three other tables he uses to look up the details of each customer, product, or store that appeared in the Transactions table.

Figure 1.7 – Sample lookup tables

Customers: This table has the unique details of all the shop’s customers’ IDs, their names, and their customer segments.Products: This table contains the unique details of the product IDs, their categories, sub-categories, and their names.Location: This table contains the details of each store ID, the city, region, and country.

The challenge Ferdinand faces in creating his report is how he can use the various IDs stored in the Transactions table to look up the customer, product, and store involved in each transaction.

How does a data model help in this situation?

Using a data model, Ferdi can upload and connect the Customers, Products, and Locations tables to the Transactions tables using the Customer ID, Product ID, and City columns respectively. This is where a calendar table, created as supplemental data but very useful, would get connected as well. He will then use this model to generate his daily reports to analyze sales by Product, Geography, Customer, and Date.

The model will look like the following screenshot:

Figure 1.8 – A screenshot of a data model showing sales data

From the two case studies, we can appreciate that using Excel’s data model can help us overcome some of the typical challenges in our routine office work.

Excel’s data model allows you to integrate data from multiple sources in an efficient manner. This is what is called an Entity Relationship Diagram (ERD).

Figure 1.9 – Sample ERD for a sales report in Excel

Apart from this key advantage, the data model