- Details
- Parent Category: Programming Assignments' Solutions

# We Helped With This Excel (VBA) Programming Assignment: Have A Similar One?

Category | Programming |
---|---|

Subject | Excel (VBA) |

Difficulty | College |

Status | Solved |

More Info | Excel Vba Help |

## Short Assignment Requirements

## Assignment Description

__Scenario Manager__

ª The *Scenario* *Manager* allows you to vary up to 32
input cells for various values, or scenarios, and observe the results of
several output cells.

ª The *Scenario Manager* will create a *Scenario Report* which shows the resulting output values for each scenario of input values.

ª Preparation requires an initial list of inputs or outputs. Appropriate values and formulas should be filled in these cells.

ª Then go to *Data Ribbon > Data Tools > What-If Analysis
> Scenarios Manager* to view the *Scenario Manager.*

ª Add a new scenario.

ª Cell references should be to the list of inputs created in the spreadsheet preparation.

ª Next, specify the values these inputs should take for the scenario we are creating.

ª Click *Summary* to create the *Scenario Report*.

ª The *Scenario Summary* dialog box asks us to select the
outputs we want to observe for the various scenarios of inputs.

ª We have a list of inputs for a company’s sales.

ª We are interested in the company’s after tax profits for each of the five years as well as their total NPV.

ª We want to consider three different scenarios for year 1 sales, sales growth, and year 1 price.

ª Create each scenario

ª After all three scenarios have been created, create the Summary Report.

ª The Scenario Report is complete

__Data Tables__

ª *Data Tables* are used to determine how some outputs vary in
response to changes in input.

ª *Data Tables* use the spreadsheet to refer to cells which
may contain formulas or functions for some output and input of some problem.

ª There are two types of *Data Tables*:

ª *one-way data tables: *determine how changing one input will
change any number of outputs

ª *two-way data tables: *determine how changing two inputs
would change a single output

ª A list of inputs and outputs should be created first.

ª Next, you will create a list of the various input values you want to experiment with.

ª If you are creating a *one-way data table*, you would put
these values in a single column.

ª If you are creating a *two-way data table*, you would create
one column and one row of varying input values for the two inputs of interest.

ª You must then enter the output formulas you want the *Data
Table* to calculate for observation.

ª For *one-way data tables*, these output cells would be in
the columns adjacent to the input column.

ª For *two-way data tables*, this output cell would be placed
in the upper corner of the data table.

ª Select *Data* > *Table* from the Excel menu.

ª

ª If we are creating a *one-way data table*, the column input
cell will be the only reference we give.

ª If we are creating a *two-way data table*, we will reference
both a row and column input since we are varying two inputs.

ª We are given a list of inputs and outputs for ticket sales.

ª The *Total Profit* is calculated by finding the unit profit
(price minus cost per ticket) and multiplying this value by the number of
salespersons and the average number of tickets sold per person.

ª The first data table we want to create will show the different
profit values as we vary the price per ticket. This will be a *one-way data
table*.

ª Now suppose we are curious to see how the combination of price
per ticket and number of salespersons affects our total profit; this will now
be a *two-way data table*.

__Simulation__

ª *Simulation* is a modeling tool which is used to
imitate a real-world process in order to understand system behavior.

ª The true behavior of a system is estimated using distributions.

Random numbers from these distributions can be generated to evaluate multiple strategies and predict future performance

Defining Simulation

ª *Simulation* is a software-generated display of a
process which one wants to observe.

ª Simulation is a useful tool for analyzing how a set of actions functions under different parameters or settings.

ª It benefits from trial and error without the cost of materials, labor and time that would be necessary to observe the same results on the original process.

ª Simulation differs from optimization in that it does not aim to find the best result for some given inputs.

ª Rather, it seeks to find the best inputs given a desired result by running several trials of a particular scenario.

Working with Distributions

ª Simulation is useful because it can handle variability of parameters in a model.

ª That is, we cannot know many settings in a process with full certainty; there is usually some range of numbers into which values fall but we can not know exact numbers.

Thus, we use *distributions* and *random* *numbers*

Generating Random Numbers

ª Some Excel functions can be used to
generate various input data values for several scenarios, or *runs* of a
simulation.

ª Once we know the distribution of a certain parameter, we can generate random numbers within this distribution create the simulation.

ª The RAND and RANDBETWEEN functions are used to generate random numbers in Excel.

ª The RAND function does not have any parameters; it returns a randomly chosen fractional number between 0 and 1.

ª *=RAND()*

ª You can manipulate this RAND value if you want to generate values outside the interval between 0 and 1.

ª *=RAND()*(n-1) + 1*

Example:

ª To generate heights, widths, and depths to calculate some probable packaging volumes, we create random numbers between 1 and 10.

ª =RAND()*9 + 1

ª To generate a random integer, use the INT function with the RAND function.

ª The INT function rounds a number down to the nearest integer.

ª *=INT(number)*

ª *=INT(range_name)*

ª *=INT(cell_referenced)*

ª To generate random integers, we would apply the INT function to the RAND function by typing:

ª *=INT(RAND())*

ª *=INT(RAND()*(n-1) + 1)*

ª The RANDBETWEEN function takes two parameters, which are the lower and upper limits of a range.

ª *=RANDBETWEEN(lower_limit,
upper_limit)*

ª To create random numbers between 1 and 10, you could type

*=RANDBETWEEN(1,10)*

ª To generate a random number from a
particular distribution, we can use Excel’s *inverse distribution* functions.

ª The general distribution Excel function is

ª *=DIST(x_value,
distribution_parameters, cumulative_value)*

ª The general inverse distribution Excel function is

ª *=DISTINV(probability,* *distribution_parameters)*

ª We will use the RAND function as our value for the probability parameter to generate some number between 0 and 1.

ª For example, to generate random
numbers from the *Normal* distribution, we would follow the format:

ª *=NORMINV(RAND(), mean, std dev)*

ª This inverse distribution function can be similarly used for other distributions:

ª BETAINV

ª BINOMINV

ª LOGINV

ª We generate a set of numbers from the Normal distribution with mean 50 and standard deviation 15.

ª *=NORMINV(RAND(), 50, 15)*

ª ** **

ª We use the RAND function in Excel to generate random numbers.

ª The general distribution functions in Excel have the following format

*=NORMDIST(x_value,
mean, std_dev, cumulative_value)*

ª The *x-value* is the number for
which we are calculating the distribution.

ª The *cumulative value *is *TRUE* if we use the cumulative distribution function (cdf), or *FALSE *if we use
the probability mass function (pmf).

ª To generate a random number within a given distribution, we must use the inverse distribution functions.

ª The format of these inverse distribution functions is the following

ª *=NORMINV(probability, mean, std_dev)*

ª The *probability* parameter is a
number between 0 and 1 associated with the given distribution.

ª We will use the RAND function as our value for this parameter to generate some numbers between 0 and 1.

ª For example, to generate ten random
numbers in the *Normal* distribution, we would follow the format.

*=NORMINV(RAND(),
mean, std dev)*

__Application: News Vendor Problem__

__ __

ª A bookstore must determine how many calendars for the upcoming year to order in September.

ª It costs $2.30 to order each calendar, and they sell each one for $4.70. After January 1, 2010, any unsold calendars are returned to the supplier for a salvage value of $0.75 each.

ª The best guess is that the number of calendars demanded is governed by the following probabilities:

ª Demand: 150, 200, 250

ª Probability: 0.3, 0.3, 0.4

How many calendars should the company

ª The spreadsheet is prepared using formulas to determine unknown values.

** **

** **

** **

** **

** **

** **

** **

** **

ª The NORMINV function is used with the RAND function to generate a random number from the Normal distribution.

ª **Runs can be made to compare profit
values for several random input values.**

** **

** **

__Application: Simulation for Option Pricing__

ª A European __call __option on a
stock allows the owner of the option to __buy__ a share of the stock for a
certain price (the strike or exercise price) on a certain date in the future
called the exercise or expiration date.

ª A __put__ option is the same
except it allows the owner to __sell__ a share on the expiration date.

ª Buy/Sell options? => Remember with silly mnemonics

ª British Columbia => BC => Buy/Call

ª Small/Petit Clothing Labels => S/P => Sell/Put

Data Table Simulation for Option Pricing:

ª The owner of a call option hopes that
the market price of the stock *increases* above the exercise price.

ª The option can then be exercised and the owner can earn the difference by buying the stock at the exercise price and immediately selling it at the market price.

ª Opposite case for put options. The
owner of a put option hopes that the market price of the stock *decreases *below
the exercise price.

ª The option can then be exercised and the owner can earn the difference by selling the stock at higher exercise price and immediately buying it back at the cheaper market price.

ª What is a fair price for an Option?

Black-Scholes (1973). Cox-Ross-Rubenstein (1979).

ª The price of an option must be the *expected *(i.e. the average) *discounted value *(continuously at the risk-free
rate)* of the cash flows from an option on a stock having the same variance
as the stock on which the option is written and growing at the risk-free rate of interest*.

ª Note: The price of the option does not depend on the growth rate of the stock that it is written on.

Value of Cash Flows for Option Pricing:

ª Let F = Future stock price and X = Exercise price.

ª __Call Option__

Value of Cash Flow = Max { F-X, 0 }

ª __Put Option__

Value of Cash Flow = Max { X-F, 0 }

__Simulating Future Stock Prices__

ª The stock price Pt at any time t in
the future is related (via Ito’s lemma) to the current price P0 by

Pt = P0 * exp[( μ - .5σ^2)*t + σ * Z * √t]

ª μ ~ mean percentage growth rate of the
stock

σ ~ standard deviation of
the growth rate

μ and σ are expressed as decimals (i.e. 6%
=> 0.06)

Z ~ N(0,1) i.e. standard normal random variable

- all units are measured with respect to a common time unit (i.e a year)

ª For option pricing, the growth rate, μ, is the risk-free rate

ª When simulating future stock prices, the growth rate, μ, is the stock’s growth rate

__Continuous Discount Rate__

ª Any future Cash Flows must be discounted at the continuous discount rate.

ª The formula for the continuous
discount rate is:

Discount Rate = exp[-( r)*t]

ª r ~ risk-free rate of interest

t ~ time in the future

__Application: AnTech Call Option Pricing__

ª Consider the Antech stock. A share of AnTech currently sells in the market for $42.

ª The stock price has an annual
standard deviation of 20%

( σ year = 0.2) and has
tended to increase at a rate of 15% per year ( μ year = 0.15).

ª The risk-free rate is 10% per year.

ª An option is available for this stock with an expiration date of 6 months (0.5 years) and an exercise price of $40.

ª What is a fair price to pay if this is a European Call Option?

ª 0) In the "Formulas" ribbon, "Calculation" section, under the "Calculation Options" select "Automatic Except for Data Tables"

ª

ª 1) In cell B27 put the "reference" to the output from the model that you want to record in your table. That is, put the formulat "=B15" into this cell.

ª 2) Select the entire data table range - A27:B4027

ª 3) In the "Data" ribbon, "Data Tools" section, under "What-If Analysis" select "Data Table“

ª 4) Ignore the "Row Input Cell" section, and click into the "Column Input Cell" section.

ª 5) Select any blank cell on your worksheet that you will NOT use in your model. (i.e. A25)

ª 6) Click "OK"

7) Either press the "F9" key, or go to "Formulas" ribbon, "Calculation" section, select "Calculate Now

ª Your Data Table should now have filled in correctly and the average value of the discounted cash flows is the option price.

ª It should be about $4.76

__Simulation with VBA__

ª There is no particular “simulation code” in VBA.

ª We will instead be defining a set of functions we have already learned, and some new ones, which we can use when programming a simulation application.

__VBA Rand Num and Dist__

ª To generate random numbers in VBA, we
will use the *Rnd* function.

ª This function can be manipulated to generate a random number between a lower bound and upper bound using the following formula.

*(upperbound - lowerbound) * Rnd + lowerbound*

ª You can use this equation with the *Int* function to ensure this random number is an integer.

ª To generate a random number in a particular distribution in VBA, we will usually have to use the distribution formulas from Excel.

ª To do this, we can use the same
techniques along with the *Formula* or *FormulaR1C1* methods in VBA.

ª We can also use some of the *Worksheet
Functions* available with the Application object to use some of Excel’s
inverse distribution functions.

ª Since the *Log* function is also
a defined VBA function, we can use it with the *Rnd* function to generate
random numbers from the Exponential distribution.

ª To do this we just take the logarithm of the random number.

*Log(Rnd())*

ª To specify a particular Exponential distribution from which the random number is being generated, we simply multiply the mean by the previous function.

*Mean * Log(Rnd())*

ª For example, the below code generates 10 random numbers from an Exponential distribution with a user-specified mean.

* Dim mean As Double*

* mean = InputBox(“Please enter mean of your
Exponential *

* distribution.”)*

* For i = 1 to 10*

* Range(“A1”).Offset(i,0).Value = mean *
Log(Rnd())*

* Next i*

__Making Runs and Collecting Data__

ª As seen in the code above, we can use *For, Next* loops to generate multiple data values.

ª We may ask the user to specify how much data they want to use for the simulation.

ª We may also perform some calculations with the random data we generate.

ª To simulate a system, we first prompt
the user for the number of *runs or iterations* they want to perform.

ª A *run *or an* iteration* is a single execution of a series of actions which model the system.

ª We then need to determine if we will
be storing this data and the results of the specified calculations in *arrays* or in a *spreadsheet.*

ª When multiple calculations of data are needed, which do not require Excel functions, arrays can be easier structures to work with.

ª They are easier in the sense that range names do not need to be defined and extra worksheet space does not need to be used.

ª If however, some other Excel functions, such as distribution functions, will need to be used with the generated data, it may be better to store this data in a worksheet.

ª For example, say there is a system which takes as input a number from an Exponential distribution with lambda 5, and outputs the square of that number.

*Dim runs As Integer, Input() As Double,
Output() As Double*

* runs = InputBox(“Please enter the number of runs
for this *

* simulation”)*

* ReDim Input(runs)*

* ReDim Output(runs)*

* For i = 1 to runs*

* Input(i) = 5 * Log(Rnd())*

* Output(i) = (Input(i)) ^ 2*

* Range(“A1”).Offset(i,0).Value =
Output(i)*

* Next i*

ª The input and/or output of a simulation can then be stored in a spreadsheet for the user to see.

ª For some simulation models, it will be unnecessary to store or show the user the input values.

ª Analysis of the output is usually of most importance to the user.

__ __

## Assignment Description

** ****Project **

** **

**Note: Make 2 Separate Workbooks For Each Part**

**For Part 2 : Please refer the Black Scholes Option
Pricing Doc where simulation and Option Pricing Formula is described.**

** **

**Part 1**

The website *http://www.x-rates.com/d/CAD/table.html** *displays the current exchange rates
between the Canadian Dollar and several other currencies. Set up an appropriate
userform that possesses a button that will import this data from the website
and place it onto a worksheet. In addition, place a second button on the
userform that will allow the user to refresh the query at any time (**Hint #1**:
assuming that the imported data begins in cell A3 – the VBA code to refresh the
query would be Range("A3").QueryTable.Refresh BackgroundQuery:=False. **Hint #2**: only unload the userform when the cancel button is selected).
Each time the query is refreshed, calculate which country has the “strongest”
currency (defined for this question as the currency which 1 CAD buys the least
of) and place this result into a textbox on the userform (**Hint #3**: You
might want to look at how to use the Match function in Excel and then
incorporate this function into your VBA code using Application.WorksheetFunction
– up to you, but it requires only a single line of code).

**Part 2**

Consider the (Tech Stock Option Pricing
file Attached) Antech stock option pricing file attached. Suppose now that a
share of AnTech currently sells in the market for $34. The stock has an annual
standard deviation of 17% (year = 0.17).
The stock price has tended to increase at a rate of 12% per year (year = 0.12). The risk-free rate is 5% per
year. An option is available for this stock with an expiration date of 6 months
and an exercise price of $32.50. Rather than using data tables we would want
to now price the option via simulation entirely in VBA code. Develop an
application that consists of a userform containing the above, key data
displayed in text boxes as the default values. However, the user will be
allowed to change any of these values should they so wish. Also include a
textbox which permits the user to input a value for the number of iterations of
the simulation – be sure to allow for the input of “large” integer values (i.e.
100,000 or even 1,000,000 runs). When the user presses the OK button, have your
application calculate the option values in a simulation analysis for both a Put
and a Call on this stock using the data values selected. Once calculated, have
these two option prices displayed on the userform. Do not unload the userform,
since the user may wish to enter new values onto the form and resimulate the
pricing routine. Only unload the userform on the selection of the Cancel
button. **Hint**: For any probability values required, you can make use of
the Application.WorksheetFunction object together with appropriate Excel
functions. Note that in VBA, the Rnd or Rnd() function generates random numbers
between 0 and 1 – this is different from the Rand() function in Excel.