Lecture 4

1 / 48

# Lecture 4 - PowerPoint PPT Presentation

Lecture 4. Advanced Topics. OVERVIEW. Importing Data Data Analysis Add-In Solver Macros. IMPORTING DATA. You may receive data that is not in Excel that needs to be opened in Excel For example a text file, that is smaller and more amenable to email

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Lecture 4' - perrin

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Lecture 4

OVERVIEW
• Importing Data
• Solver
• Macros
IMPORTING DATA
• You may receive data that is not in Excel that needs to be opened in Excel
• For example a text file, that is smaller and more amenable to email
• Load this file into Excel, maintain the main columns
Importing Data From Text File
• Save and Close the file
• Open the file again from the folder
• Notice the changes we made in the text file
• The file is still a text fileeven though we have modifying it in Excel
Importing Data From Text File
• From the Excel window select Save As Excel Workbook
• Some of Excel’s tools are not loaded automatically but must be activated by the user
• For example the Statistical tools called “Data Analysis”
Data Analysis
• Use Data Analysis to calculate Descriptive Statistics of the “Speed” column
SOLVER
• Solver enables users to set up operations research problems e.g. integer linear programming, non linear programming etc
• Operations research problems typically involve systems of equations that involve inequalities
• Therefore, for non-inequalities problems we can use it as alternative, to say matrices, to solve systems of equations.
• Solver must be activated from Add-Ins prior to use.
Optimization Using Solver
• Consider the following investment decision problem for a financial institution
• Three new financial products yield returns of 6.5%, 7%, 9% respectively
• Total investment amount in these products altogether cannot exceed \$500K
• Due to risk, product 3 investment cannot exceed twice product 2 amount and product 2 investment cannot exceed three times product 1
• What is the optimal investment plan to maximize return on investment?
Optimization Using Solver
• Note that the more objectives and constraints, the more complex the problem
MACROS
• Macros are used to record and save the keystrokes used to perform a task
• By so doing the user can then call the macro to run the same tasks on another dataset
• This is especially advantageous for repetitive type tasks, e.g. a weekly or daily traffic update report
• Before we can use macros we must activate the Developer tab through Excel Options
Activate Developer Tab
• Format the traffic data, but save all work in a macro
Record Macro
• Keystrokes:
• Highlight top row
• Bold
• All Borders
• Fill color (green)
• Select: Date, Time, Vehicle
• Format
• AutoFit Column Width
• Scroll back to left
• Highlight first 30 rows of speed column
• Fill with orange color
• Developer
• Stop Recording