Hypothetical Purchasing Project Simulations
Budget: $10 – $30 USD
In this simulation, a number of hypothetical projects are generated per year for 5 years and presented for purchasing.
Every year, the projects generated will be sorted based on different project attributes and purchased with the initial capital budget of $650,000. The number of projects available for purchase will increase by 5 every year, with 25 projects available in the first year. The cash flow of each purchased project is added to the capital remaining at the end of that year; if $600,000 worth of projects are purchased in the first year, then the next year's budget will be $50,000 plus one year's worth of cash flow from each purchased projects. The cash flow of each purchased project will be added to the budget of every year of that projects lifetime. At the end of the purchasing cycle (5 years), the net wealth accumulated from the purchasing decisions made is compared across different project sorting methods. Net wealth is the total amount of income realized after the end of the final purchasing year (this is already calculated for you —check the code to see how this is calculated). The simulation should run for 30 cycles.
The majority of the simulation program is written for you, but there are several steps that you will need to take (and changes you will need to make to the code to complete this assignment:
1. Familiarize yourself with the given code. A flowchart describing the logic of this program has been given, and the code has been documented using in-code comments ('comments in VBA look like this). Make sure that you have a good understanding of what the program is already doing and what you will have to add. ~
Calculate attributes for the projects that are generated. The generation of the projects themselves is already given, however all attributes for these projects are 0. All of these attributes will have to be calculated or randomly generated:
a. First cost will be calculated as a uniformly-distributed random number (including decimals) between the input min and max first cost. This can be done in VA with the following code:
firstCostmin + (firstCostMax - firstCostin) * Rnd
This code will produce a value between the first cost min and max. The VBA function "Rnd" returns a uniformly-distributed random decimal between 0 and 1.
* IRR (internal rate of return) will be calculated as a uniformly-distributed random number between the input min and max IRR. Use the same method that you used for first cost.
* Lifetime will be calculated as a uniformly-distributed random number between the input min and max lifetime. Use the same method as first cost and IRR.
* Cash flow will be computed using the first cost, IRR, and lifetime of the project. The equation, referred to as (A/P, i,
n), is given in the book. You may also use Excel's built-in PMT function to calculate this value:
worksheetfunction. Pmt (IRR, lifetime, -firstcost)
Note that the first cost should be negative when used in this function!
* Payback is the number of years it will take for the project to recover its first cost. This is calculated as the first cost divided by the annual cash flow.
* NP (net present value) will be calculated using the generated project attributes. The equation for NPV is given in the textbook, but you may also use Excel's built-in PV function: worksheetfunction. PV(discountRate, lifetime, - cashFlow) - firstcost Note that the cash flow should be negative when used in this function!
1.
2. Add VBA code to sort the generated projects by payback, NPV, and IRR. Use Excel's "record macro" function to generate VA code to perform a sort on the desired project attribute. After this code is generated, insert it into the indicated spot in your program and make the necessary adjustments so that projects are sorted within their years only. You do not want all projects across all years to be sorted! This sort will have to be repeated for all three sorting methods.
3. Add VBA code to perform purchasing decisions for NPV and IRR sorts. The purchasing decision logic has been given for the payback sort. Repeat this code and make the necessary adjustments to perform decisions for the NPV sort and IRR sort.
4. Add VBA code to calculate the average and standard deviation for each sorting method's net wealth. You may do this any way you wish, including using the "record macro" function in excel. Keep in mind that the number of cycles being performed is variable.
Here are a couple of notes for this assignment:
* In order to easily access VA in your Excel files, you should enable the "Developer" tab in Excel: go to File → Options -> Customize Ribbon, and check the box next to "Developer" in the "Main Tabs" list.
* Your Excel file must be saved as a "Macro Enabled" (xlsm) workbook. The template provided is already formatted this way, but make sure not to change this when saving your workbook.
* As you work through this assignment, be sure to save your work regularly and test your code often! I recommend decreasing the number of years, cycles, and projects in your input (e.g., 2 years, 2 starting projects, and 3 cycles) for testing-this will save a lot of running time. You are free to make any changes to the code that you would like. If you are familiar with programming, I'm sure you will be able to identify some areas of the code that could be re-written to be more efficient or easier for you to work with. However, please do not change the layout of any of the sheets.
* Do not create a sub-module named "Reset" anywhere in your program! This creates a conflict with CrystalBall and is mildly irritating to work around. You should not need to create any new sub-modules, but you are welcome to do so if you find it necessary. Learning a new coding language is difficult. You are free to look up VBA syntax (e.g., how do I increment a value in VBA?), but do not use any pre-written code aside from what I have provided for you. Your work should be original. Keep in mind that tools such as ChatGPT are not likely to provide code that will accurately address the problems that you are being asked to solve in this assignment and may cause more work for you.
Every year, the projects generated will be sorted based on different project attributes and purchased with the initial capital budget of $650,000. The number of projects available for purchase will increase by 5 every year, with 25 projects available in the first year. The cash flow of each purchased project is added to the capital remaining at the end of that year; if $600,000 worth of projects are purchased in the first year, then the next year's budget will be $50,000 plus one year's worth of cash flow from each purchased projects. The cash flow of each purchased project will be added to the budget of every year of that projects lifetime. At the end of the purchasing cycle (5 years), the net wealth accumulated from the purchasing decisions made is compared across different project sorting methods. Net wealth is the total amount of income realized after the end of the final purchasing year (this is already calculated for you —check the code to see how this is calculated). The simulation should run for 30 cycles.
The majority of the simulation program is written for you, but there are several steps that you will need to take (and changes you will need to make to the code to complete this assignment:
1. Familiarize yourself with the given code. A flowchart describing the logic of this program has been given, and the code has been documented using in-code comments ('comments in VBA look like this). Make sure that you have a good understanding of what the program is already doing and what you will have to add. ~
Calculate attributes for the projects that are generated. The generation of the projects themselves is already given, however all attributes for these projects are 0. All of these attributes will have to be calculated or randomly generated:
a. First cost will be calculated as a uniformly-distributed random number (including decimals) between the input min and max first cost. This can be done in VA with the following code:
firstCostmin + (firstCostMax - firstCostin) * Rnd
This code will produce a value between the first cost min and max. The VBA function "Rnd" returns a uniformly-distributed random decimal between 0 and 1.
* IRR (internal rate of return) will be calculated as a uniformly-distributed random number between the input min and max IRR. Use the same method that you used for first cost.
* Lifetime will be calculated as a uniformly-distributed random number between the input min and max lifetime. Use the same method as first cost and IRR.
* Cash flow will be computed using the first cost, IRR, and lifetime of the project. The equation, referred to as (A/P, i,
n), is given in the book. You may also use Excel's built-in PMT function to calculate this value:
worksheetfunction. Pmt (IRR, lifetime, -firstcost)
Note that the first cost should be negative when used in this function!
* Payback is the number of years it will take for the project to recover its first cost. This is calculated as the first cost divided by the annual cash flow.
* NP (net present value) will be calculated using the generated project attributes. The equation for NPV is given in the textbook, but you may also use Excel's built-in PV function: worksheetfunction. PV(discountRate, lifetime, - cashFlow) - firstcost Note that the cash flow should be negative when used in this function!
1.
2. Add VBA code to sort the generated projects by payback, NPV, and IRR. Use Excel's "record macro" function to generate VA code to perform a sort on the desired project attribute. After this code is generated, insert it into the indicated spot in your program and make the necessary adjustments so that projects are sorted within their years only. You do not want all projects across all years to be sorted! This sort will have to be repeated for all three sorting methods.
3. Add VBA code to perform purchasing decisions for NPV and IRR sorts. The purchasing decision logic has been given for the payback sort. Repeat this code and make the necessary adjustments to perform decisions for the NPV sort and IRR sort.
4. Add VBA code to calculate the average and standard deviation for each sorting method's net wealth. You may do this any way you wish, including using the "record macro" function in excel. Keep in mind that the number of cycles being performed is variable.
Here are a couple of notes for this assignment:
* In order to easily access VA in your Excel files, you should enable the "Developer" tab in Excel: go to File → Options -> Customize Ribbon, and check the box next to "Developer" in the "Main Tabs" list.
* Your Excel file must be saved as a "Macro Enabled" (xlsm) workbook. The template provided is already formatted this way, but make sure not to change this when saving your workbook.
* As you work through this assignment, be sure to save your work regularly and test your code often! I recommend decreasing the number of years, cycles, and projects in your input (e.g., 2 years, 2 starting projects, and 3 cycles) for testing-this will save a lot of running time. You are free to make any changes to the code that you would like. If you are familiar with programming, I'm sure you will be able to identify some areas of the code that could be re-written to be more efficient or easier for you to work with. However, please do not change the layout of any of the sheets.
* Do not create a sub-module named "Reset" anywhere in your program! This creates a conflict with CrystalBall and is mildly irritating to work around. You should not need to create any new sub-modules, but you are welcome to do so if you find it necessary. Learning a new coding language is difficult. You are free to look up VBA syntax (e.g., how do I increment a value in VBA?), but do not use any pre-written code aside from what I have provided for you. Your work should be original. Keep in mind that tools such as ChatGPT are not likely to provide code that will accurately address the problems that you are being asked to solve in this assignment and may cause more work for you.