Reference no: EM131358847
Assignment: Capital Budgeting Simulation
This is the first part of a semester long project that will use VBA to investigate several capital budgeting methods. Assignments will be given throughout the semester culminating in a project presentation the final class meeting. Assignments are initially done on an individual basis. As students demonstrate an understanding of VBA and the project concepts, teams will be formed to complete the project.
Initial Assignment: This assignment will be due at 12:00 Noon on Thursday, Jan 21 via email. The assignment will involve generating projects with various characteristics, ranking the projects using several methods, buying projects using available money, and continuing this process for five capital budgeting periods (years). At the end of the five years the wealth accumulated by the various ranking methods will be determined and compared. Specifically this assignment will require:
1. Generate 25 projects with uniformly distributed first costs ranging from 40,000 to 280,000; uniformly distributed lives from 2 - 10 years; and uniformly distributed rates of return (IRR) from 5 - 40%. With this information the yearly cash flow (which is the same each year) can be computed. As an example assume a project has been generated with a first cost of 75,325, a life of 6 years, and an IRR of 27.2%. The yearly cash flow can be computed by multiplying the first cost by: ((1+i)^n)i/((1+i^n)-1). This equation called the (A/P,i,n) is in the book. This can be done in Excel using the PMT function. For these numbers this gives a yearly cash flow of 26,820. This same process will then be used to generate 24 additional projects with varying first costs, lives, and yearly cash flows.
2. Compute the payback for each project and rank the projects generated in step one using payback, which is the length of time need to return the initial cost. This is equal to the first cost divided by the annual cash flow. The smaller the better.
3. From the ranked list, use an initial capital budget of $600,000 to buy as many projects as possible. Only whole projects can be purchased. Any unused cash will rollover to year two. Keep track of the projects purchased and continue to year two. For year two generate 30 projects, rank them, and purchase projects using the money that projects purchased in year one have provided through their yearly cash flows. Note that any project purchased will return the yearly cash flow every year through its life. For year three generate 35 projects, rank them and purchase using the same approach. Continue this process in year four with 40 projects and year five with 45 projects.
4. For the five year period several projects will have been purchased during each of the five years. Some of these projects with short lives are no longer returning any cash, and some of the projects with longer lives will still be returning cash. To compare the capital budgeting ranking methods we want to see how much wealth has been accumulated by the company during this period. The measure we will use will be to at the end of year five add up the money the projects will still return from the end of year five to the end of the projects' lives to get one value of the net wealth at the end of year five. For example a project bought in year 3 with a 7 year life will have 5 years of cash flows left. If each yearly cash flow is say 45,000, the value of the remaining life of this project would be 5 * 45,000 = 225,000. This process would need to be repeated for all projects that have cash flows remaining starting at the end of year five. This value will be used as the net wealth. This completes one cycle of the simulation for one ranking method. This will need to be completed for 30 cycles for this ranking method. Compute the average and standard deviation for the 30 cycles.
5. Complete this process (steps 1-4) for two additional ranking methods: Net present value (NPV) using i = 15% and Internal rate of return (IRR). The calculation of NPV can be found in the textbook or online. The IRR has already been computed for each project. Complete 30 cycles for each of these methods.
This assignment needs to be done in Excel using VBA. A VBA macro button needs to be placed on the initial sheet clearly visible when in the home (A1) location. This macro button will execute the simulation with no other input required from the user. The output should show the results for each of the ranking methods for each of the 30 cycles, and the average and standard deviation for each method.
This assignment is to be done individually. Every student will be required to submit their Excel file via email by the due date.
|
Examine each part of your argument for error affecting truth
: Examine each part of your argument for errors affecting truth. (To be sure your examination is not perfunctory, play devil's advocate and challenge the argument, asking pointed questions about it, taking nothing for granted.)
|
|
Choose an ethical issue raised by the mattel
: Choose an ethical issue raised by the Mattel case study (e.g., you might choose to analyze Mattel's treatment of employees). Apply the Utility Test: http://ethicsops.com/UtilityTest.php
|
|
Draw the charts and plot the values of the sample means
: Twelve samples, each containing five parts, were taken from a process that produces steel rods at Emmanuel Kodzi's factory. Draw the charts and plot the values of the sample means and ranges
|
|
Find the input probability distribution
: Show that if n such channels are cascaded, the resulting channel will be equivalent to a Z channel with ε1 = εn.
|
|
Compute the payback for each project and rank the project
: IEE 6060- Compute the payback for each project and rank projects generated in step one using payback, which is the length of time need to return the initial cost. This is equal to the first cost divided by the annual cash flow.
|
|
Requirements drove the system
: Have you had any work or personal experience in which an IT system has transformed the way an organization has carried out work? What requirements drove the system to be developed and what requirements are likely to drive it to change in the futur..
|
|
What are the powers retained by the state
: The entire textbook on California discusses the similarities and differences between the US national government and the government of the State of California. Which of the major topics (chapters) has the most similarity between the US national (f..
|
|
Polc functions of management
: In this Assignment you will apply the concepts you read about in Chapter 1 relative to both defining organizational behavior and the impact of organizational behavior on business outcomes. As you could see in our Discussions, many of the POLC func..
|
|
Ethics and social responsibility practices
: Develop a 1,050-word examination of the ethics and social responsibility practices within the organization. Include the following in your examination:
|