Homework 5: Oracle Crystall Ball MIS 6543 Business Analytics Fall 2013 Arkansas State University Instructor: Dr. Ahmad Syamil Due date: 8:00 PM, Wednesday, December 11, 2013. Name your homework files using your name and location, and then put them on Blackboard>MIS6543> Course Tools>Journals>Digital drop box>Create Journal Entry>Write an appropriate title and message including whether you are an online or face-to-face student> Attach file. Inform me by email after you submit your file. Of course you can submit your homework earlier. If you are an ASU Jonesboro face-to-face student, you must also submit the complete hard copy of your work under my office door (BU 406) by the due time. Do not submit the hard copy of the answer to each problem oneby-one. You can get Oracle Crystal Ball simulation software for chapter 13 from http://www.oracle.com/crystalball/index.html or http://www.oracle.com/technetwork/middleware/crystalball/downloads/index.html ?ssSourceSiteId=ocomen If you never register on the Oracle website, Oracle will ask you to register before downloading Oracle Crystal Ball (CB). Furthermore, you can only use the CB trial version for 2 weeks. Therefore, you should start working on this assignment no sooner than 2 weeks before the day you plan to submit this assignment. Using an older version of CB is also acceptable. Use Google. If you use one version for 2 weeks, you may be able to install another version for another 2 weeks. Consequently, you can use CB for 4 weeks. Although your operating system is 64-bit Windows Operating System, most likely you need CB for 32-bit Microsoft Office because the Office installation is by default the 32-bit version of Office. Only companies that process large files require the 64-bit version of Office. *** As I informed you earlier, you can put more than 1 linear programming (LP) problem in one Excel file. However, you must create 1 Excel file for each CB problem. Furthermore, you can only open 1 Excel CB file at a time. If not, CB creates many graphs from all files that you open. ****

Page 1 of 6

You can also use Monte Carlo simulation and risk analysis software other than Oracle CB from the following link http://en.wikipedia.org/wiki/Comparison_of_risk_analysis_Microsoft_Excel_add-ins Some of the software listed on the link above have no time limit. Furthermore, the closest replacement for Oracle CB is @Risk from Palisade Corporation https://www.palisade.com/risk/ Two problems: Problem 1: Chapter 13 - Computer Simulation with Oracle Crystal Ball for Business Planning. This is an easy problem. Work on this problem first. Taylor Jovi is the CEO of a company that has 5 divisions. She is modelling how much sales will be generated by each division during 2014. Her estimates are summarized in the following table: Divisions
Book publishing Printing Local newspaper Magazines Children books

2013 Sales
$5,432,231 $2,503,213 $1,467,642 $2,546,734 $3,542,532

Estimated Growth Rates in 2014
Triangular dist. Min. = - 5%, most likely= 1%, max = 4% Normal dist., mean = 5 %, standard dev. = 7% Uniform dist., min. = - 10%; max = -2% Lognormal dist. Location (only for the newest CB) = 0%; Mean = 10 %, standard dev. = 10% Beta dist. Min = 2%, max = 4%, alpha= 6, beta = 2.

Create a spreadsheet to simulate the total revenues that could occur in the coming year. Run 500 replications of the model and do the following: a) Construct a 95% confidence interval for the average level of sales she could expect for 2014. Refer to pages 528 to 530. b) What are the chances that total sales in 2014 will be larger than those in 2013? c) What is the probability that the total revenues will be smaller than $15.6 m in 2014? d) What is the probability that the total revenues will be between $15.5 m and $15.8 m in 2014? e) What is the probability that the total revenues will be larger than $16 m in 2014? If a problem consists of several parts such as (a), (b), and (c), you must also mark your answer with (a), (b), and (c). You must include a print screen similar to figure 13.20 on page 540 to support your answer to each part of the homework problems.

Page 2 of 6

Problem 2: Chapter 13 - Computer Simulation with Oracle Crystal Ball for Retirement Planning. Lil Jonas works for Arkansas Pacific Group (APC), a private equity company, which allows him to contribute up to 9 % of his salary and other incomes into tax deferred savings plan. He always maximizes his contribution. APC matches a portion of employee contributions. Depending on APC’s performance, APC contributes at least 25 % but no more than 100 % of the employee’s contributions. Almost every year APC contributes about 70 % (Hint: Use the triangular distribution). He is currently 30 years old and makes $ 60,000. He wants to retire at age 60. He expects that the yearly growth of his salary and other incomes follows a normal distribution with a mean = 3% and standard deviation = 5%. He invests his retirement money in mutual funds. His investments consists of his contribution and his company’s contribution. He expects the annual returns of his investment to vary according to a lognormal distribution with an average of 4 %, a standard deviation of 6 %, and location (only for the newest CB) = 0%. Use 500 replications. Assume that the yearly return of his investment money is calculated at the end of each year. Hints for parts (a), (b), and (c) below: When you use Cristal Ball, do not make one Excel file with 3 different worksheet. Create three different Excel files using copy and paste. a) If he contributes 9 % of his income to the plan, how much total investment including all returns can he expect to have at age 60? Construct a 95% confidence interval. b) Suppose he made a 9 % contribution to his plan for 8 years, from age 30 to 37, and then stop contributing. (b.1) How much of his own money he would have invested and (b.2.) How much total investment including all returns can he expect to have at age 60? Construct a 95% confidence interval. c) Now suppose he contributes nothing for the first 8 years of the plan and then contributes 9% for 23 years from age 38 to age 60. (c.1) How much of his own money he would have invested and (c.2) How much total investment including all returns he can expect to have at age 60? Construct a 95% confidence interval. . d) What did you learn from his example? Notes: 1. You can get your homework and assignment files from: 1. Your @smail.astate.edu email registered at Blackboard. 2. Blackboard 8 http://blackboard.astate.edu/. 3. Your classmate. 2. Most of the problems require Microsoft Excel. It is your responsibility to be familiar with the Microsoft Excel version you are using (2000, 2003, 2007, or
Page 3 of 6

2010). The ASU College of Business computer labs use Excel 2010. Please buy or borrow any Excel book if necessary. You can get and read Excel ebooks on-line from the ASU library. ASU students can get Excel, Symantec EndPoint Anti Virus, and other software free of charge from http://www2.astate.edu/a/finance-admin/its/ 3. Creating homework files properly including MS Excel files is a big part of this class. You must create your homework file from scratch. If your file does not work properly before the due date, please do not send your file to someone (including to me) and ask someone to fix your file. I can give you some hints but I do not fix your file. If I fix your file, it means that I am the one who finishes your homework. You, and not the instructor, have the responsibility to finish this assignment. 4. Start this assignment as early as possible. If you wait until the last minute, you will not be able to finish this assignment on time. 5. Drop your homework files in Blackboard Digital Drop Box. Name your file using: FirstName_LastName_Location_Face_to_Face_or_Online_HW#_Problem#_ MIS6543_Fall_2013. Example: John_Public__Jonesboro_F2F_HW5_Problem1_MIS6543_Fall_2013. 6. If you are in a hurry, you can e-mail your virus-free homework files to me. My computer will automatically delete your files if they are not virus free. You can drop your files in the Blackboard Drop Box the next day. 7. Type the following information on the first worksheet (cover sheet) of your Excel file: • • • • • • • Course name, i.e., MIS 6543 Business Analytics. Term, i.e., Fall 2013. Homework or assignment number, e.g., Homework 5. Your name ASU campus location and status (face-to-face or online) The name of the instructor, i.e., my name. Type and sign or initial the following:

“I did not copy any other person’s homework. I did not share my work with anybody else. I created this homework from scratch. I did not revise or copy and paste any other files. Nobody else helped me create my homework files. If I did any dishonest activities, I will get an “F” in this course and may be expelled from ASU.” Name: _______________________ Date: ____________
Page 4 of 6

8. Before submitting your file, please create a footer and header on each worksheet. If you use Microsoft Excel 2007 for Vista, click “View,” and then “Header and Footer,” If you use Microsoft Excel 2003 for Windows XP or lower, click “View,” then “Header and Footer,” and finally “Custom Header.” If you use Microsoft Excel 2000 or lower, you will see seven small buttons/icons. If you use Microsoft Excel 2003/XP, you will see ten small buttons/icons. Left section: type your name (s). Right section: Click “date” and “time” icons. Click the appropriate icon to put the following footer: Left section: Click “file name” icon. 9. Please type the title (problem #, page #, and so on) on top of each Excel worksheet/file. For example: Problem 1: Chapter 13 - Computer Simulation with Oracle Crystal Ball for Business Planning. 10. Submit 1 file for each problem. Homework must be neat, and easy to read. Don't make someone hunt around your pages to find your solutions. Write your solutions so that other people can read and understand your solutions. If a problem consists of (a), (b), and (c), you must also mark your answer with (a), (b), and (c). I grade not only the content but also the organization of your homework. Format your answers and tables nicely. Please also use this class to learn Excel and to learn how to organize your work properly. 11. If it is possible, print each problem on one page. If not, at least do not split a table/graph, i.e., do not print a table/graph on two pages. Microsoft Excel 2003/XP: To print a graph on one page, use the following procedure: a. Click the graph you want to print. b. Click “File”, then click “Print Preview” to make sure that everything is fine. c. Click “Close” if you would like to edit your graph. Otherwise, click “Print.” You have several ways to print a table on one page. You can use the following procedure: a. Highlight the cells you want to print. b. Click “File”, then click “Print,” and then click “Selection.” c. Click “Preview” to make sure that everything is fine. d. Click “Close” if you would like to edit your table. Otherwise, click “Print.” If the table is big, you can also use the following procedure to print table/cells on one page:
Page 5 of 6

a. Highlight the cells you want to print. b. Click “File” then “Print Area” then “Set Print Area.” c. Click “File” then “Preview” then “Setup.” d. Click “Page” tab. e. Select “Portrait” or “Landscape.” f. Under “Scaling,” please check "Fit to 1 page(s) wide by 1 tall." Click OK. g. Click “Close” if you would like to edit your table. Otherwise, click “Print.” Please always use “Print preview” before printing your Microsoft Excel/Word document to make sure that your printout will be OK. If you use Microsoft Excel 2007/Vista, click the quick access button on the upper left of your computer screen > Print > Print Preview > and then follow the instruction above.

Page 6 of 6