Advanced Excel Modeling - Data Synchronization

Post date: Jul 14, 2018 5:40:23 PM

For the professional modeler Excel's advanced features improve transparency and efficiency while reducing complexity and risk. They also provide greater flexibility, scalability and connectivity. These benefits grow in value as models become more complex and connected to other models, data sources and people.

In the previous section we discussed model transparency. In this section we will explore how to avoid a common error: Data Synch Errors.

Lesson Objectives

At the end of this lesson you will be able to:

    • Define Data Sync Error

    • Explain how data sync errors happen

    • Explain how to prevent data sync errors

This is under construction.

Click this link for the course's start

What is Data Synchronization?

Data synchronization is the process of establishing consistency among data from a source to a target data storage and vice versa and the continuous harmonization of the data over time. It is fundamental to a wide variety of applications. https://en.wikipedia.org/wiki/Data_synchronization

In the spreadsheet context this means making sure every instance of a value shown more than once in a model remains identical. Data Synch Errors occur when two or more data elements must be identical but aren't. This can make a fun and challenging game but in models data synch errors are often difficult to detect and can cause erroneous results. Fortunately, data synch errors are easily prevented.

Repeating values is common in models. Models often show things like a model start date in several places. In Payton's simple project the label USD is repeated several times. If this model were ever used for a different currency then we would have to make sure to change every instance of USD to, say GBP (British Pound). If we aren't careful, we could miss one of the labels causing confusion. The problem is worse when we use literals in repeated formulas. When formula literals need changing, the potential for missing a formula is much greater and much harder to notice and discover when things go wrong.

How to Eliminate Data Sync Errors

Preventing data sync errors is not only easy but also greatly simplifies model maintenance. We already took care of one potential data sync error by replacing literals with references in a Constants sub-section of Inputs. Using constant references instead of literals eliminates the potential of miss-keying a literal in a formula which can be very hard to find. We can use the same principal to eliminate the potential data sync errors in labels like USD by replacing USD with a reference instead of keying it in everywhere a monetary unit of measure is needed by adding a Labels sub-section to our Inputs section.

The inputs section contains values with dependents (formulas that reference them) but no precedents. A type of input is Labels.

Text providing context, units of measure, or format strings to document what a value represents and in which units (if applicable).

Labels Sub-Section

We can create a section for labels where we can enter them once and reference them wherever else they are needed so we have only one place to maintain them and eliminate data sync errors resulting from mistyping labels.

With the Lables sub section in place our model can accommodate various currencies by changing one label which propagates everywhere needed.

Resulting Structure

Now that we have added a Constants sub-section for transparency and a Labels sub-section to eliminate label data sync errors, here is what Payton's Simple Project looks like now

Example

Payton's project requirements call for monthly payments but rates are expressed as annual percentage rates. So to convert an annual interest rate to a monthly interest rate we must divide by the number of months in a year or MPY.

Lab

    1. Add the Labels sub-section to you Inputs section

    2. Adjust your model to replace repeated literal labels with references to the Labels sub-section

Assignments

    1. Complete the Lab

    2. Complete the Quiz

Quiz

    1. A Data Synch error is:

      1. When we forget to refresh links to outside data sources

      2. When our model needs to be recalculated

      3. When values that are supposed to be identical are not

      4. None of the above

      5. All of the above

    2. Data Synch errors can occur:

      1. In formulas when literals are not replaced by Constants references

      2. In repeated labels that are not replaced by Labels references

      3. Anywhere where data is supposed to be identical but is not

      4. None of the above

      5. All of the above

Summary

Store data in one place. Where ever else that data is needed, use a reference to where it is stored. We should store literals, whether from user entry, modeler entry, or data importing, in one cell (or name) and reference that cell (or name) everywhere else that value is needed