4: Utilizing Best Practices for Preparing and Analyzing Data
4.1 Design complex Excel models
4: Utilizing Best Practices for Preparing and Analyzing Data
4.1 Design complex Excel models - Video Tutorials & Practice Problems
Video duration:
1m
Play a video:
Video transcript
<v Illustrator>When implementing complex Excel models</v> that may need to be advised periodically it is extremely important that we design our solutions for the desired longterm sustainability. Without a well thought out design, the final product might suffer an efficiency, reliability, sustainability, or worse delivering on the key objectives. In such context, a combination of the following standards, practices and considerations may be desirable. A consistent structure for our models, which may include standard worksheets, optimal data structures, and appropriate intermediary steps to simplify complex calculations. Use of naming conventions for worksheets, names, tables, and other data structures. Style definitions, and consistent number formats for similar cells. Custom templates for cells containing specific data types that can be used as a model index or the individual cells from such templates can be copied onto other cells within the model for consistency purposes. And finally, color schemes may be used with styles, templates and data objects like Excel tables and dynamic arrays, to distinguish between source, reference and process data. Please note, you may want to consider other standards and practices depending upon your experience, client requirements and emphasis on re-usability. However, for the purposes of this fundamental scores, we will limit our scope to the topics listed here. With that, let's review these items in further detail.