Data Management Tools

Course overview and objectives
To become a more effective business and financial manager there are certain spreadsheet modeling skills and database knowledge that must be mastered.
This course will focus on advanced spreadsheet features and modeling with Excel, database development and data analysis with MS Access.
The course will use a “learn by doing” modular approach with real life business examples.
At the end of the course, students should be able to:
• Use advanced conditional formatting and data validation
• Use advanced functions and formula including array formulas
• Master Goal Seek, Solver, Tables, Scenarios and Pivot Tables
• Create and Edit Macros
• Create Models that are easy to navigate and use them to assist your decision-making process
• Understand the database relational model
• Use MS Access to create databases, queries and forms
 

Course Content
MS Excel
Advanced functions and formula, Advanced Conditional Formatting and Validation, Goal Seek, Solver, Scenarios, Tables (a Monte Carlo simulation example), Pivot Tables, Macros
MS Access
Data Modeling, The Entity Relationship Model, Entities, Attributes and Keys, Relationships, Entity Relationship Diagrams (ERD), Database organization, The Relational Model, Components of the Relational Model, Normalization, Implementation in MS Access, Creation of databases, tables, Queries and forms, The SQL language.

Grading
The final grade will be calculated using the following weights:
• Computer Excel exam: 60%
• Group database assignment: 40%
Both components are mandatory, if you miss one of them, you will fail the course.

Bibliography
Online Excel files with tutorials and exercises at:
http://itlearning.clsbe.lisboa.ucp.pt/dmt
Carlos Rondão – online database topics
Carlos Rondão - It´s Some Kind of Magic
David M. Kroenke – Database Processing Fundamentals, Design & Implementation. Prentice Hall International Editions
Dave Ensor, Ian Stevenson 1997 – ORACLE Design. O’Reilly
http://www.oreilly.com/catalog/oracledes/