Let us worry about your assignment instead!

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

SOLVED
CategoryProgramming
SubjectExcel (VBA)
DifficultyCollege
StatusSolved
More InfoExcel Vba Help
39781

Short Assignment Requirements

This is a simple stock simulation project having two small questions that need to be done on a separate work book.You may refer to the project description file for further details.Please read the black schole doc for the formula for black shcole option pricing formula.

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.

 

Customer Feedback

"Thanks for explanations after the assignment was already completed... Emily is such a nice tutor! "

Order #13073

Find Us On

soc fb soc insta


Paypal supported