TUTORIAL 1
DATA TABLE, GOAL-SEEK, AND
SETTING UP SOLVER
1
MODEL
You are planning to go on vacation in Europe (see Excel) and setting up a
budget.
Decisions: daily hotel costs, daily food & entertainment
Parameters: travel (flights are paid), daily transportation (averages
posted online), trip duration, f/x rates Decisions:
daily
spendings
Output: My
travel budget
Cost of flights
Other
parameters by Not
2
country importan
t for this
tutorial.
DATA TABLE
Use a data table to determine your budget if the following situations occur:
1. The f/x rate for Euro to HKD varies between 9.0 to 10.0 (in 0.1
increments), and the f/x rate for GBP (British pounds) to HKD varies
between 10.0 to 11.0 (in 0.1 increments).
2. The nightly rate for hotels in Switzerland varies between 320 SFR and
370 SFR (in 5 SFR increments).
3. The f/x rate for Euro to HKD varies between 9.0 to 10.0 (in 0.1
increments), and your daily budget for food in France varies between 60
EUR to 90 EUR (in 5 Euro increments).
Instructions for data tables are provided in lecture slides.
3
GOAL SEEK
Goal Seek – Given a desired output (goal), how can we achieve the goal?
Excel modifies ONE input to achieve this goal.
Type of questions:
1. I only have a budget of 30,000 HKD. I am looking for cheaper hotels in
Switzerland. How much can I spend per night?
2. I can refund my ticket from England to Hong Kong. How much should a
new ticket cost (in GBP) if my total budget is 34,000 HKD?
Reset your model before you analyze a new set of decisions! (Or press
“Cancel”)
Output cell
Goal 4
Decision/data
parameter to change
SETTING UP SOLVER (& ANALYSIS TOOLPAK)
FOR FUTURE LECTURES
Windows
1. File ⇒ Options ⇒ Add-Ins ⇒ Go.
2. Select the “Solver” and “Analysis ToolPak” boxes. Click OK.
5
SETTING UP SOLVER (& ANALYSIS TOOLPAK)
FOR FUTURE LECTURES
Windows: Go to Data You will see “Solver” and “Data Analysis”
at the end.
6
SETTING UP SOLVER (& ANALYSIS TOOLPAK)
FOR FUTURE LECTURES
Mac:
1. Open Excel and make it active in the current window.
2. Go to the toolbar on top of your window ⇒ Tool ⇒ Excel Add-ins.
3. Select the “Solver” and “Analysis ToolPak” boxes. Click OK.
7
SETTING UP SOLVER (& ANALYSIS TOOLPAK)
FOR FUTURE LECTURES
Mac: Open Excel -> Go to the built-in toolbar -> Data -> You will
see “Solver” & “Data Analysis”