Excel work

Department of Accounting & Information Systems

College of Business & Economics

IS 312, Information Systems for Business Building a DSS in Excel, Part II

Page 1 of 3

This document created and maintained by David W. Miller. PhD. ©2005-2014 This page provided for exclusive use by students in IS 312 at California State University, Northridge

Read all instructions carefully!

The following assignment is a continuation of the initial DSS Tool Building assignment. It is intended to be a brief introduction to how Microsoft Excel may be used as a Decision Support System for future projections sensitivity analysis ("what if…?" analysis). You will expand your DSS model to include five year projections. You are to follow the instructions and complete and submit the assignment as individuals.

You are to use the DSS tool (database and model) that you created in the first DSS assignment. Keep all of the base year (2013) assumptions (i.e. gross sales of $2.5 million, etc.) and their relationships (the model base).

Your assignment is to do the following:

Though you will be re-creating the Database and Model from the first DSS assignment, you are to use the DSS_II.xls spreadsheet file that I am providing to you. While this requires extra effort to re-create the Database and Model, the formatting that is contained in the provided spreadsheet will make the Part II DSS tool much easier to build in the long run. At this time, you should also correct any errors identified in the Database or Model of the first DSS project—repeating those errors will likely result in points being lost in the scoring of this, the DSS II, project. The instructions for the first DSS project are still posted on the class Web site.

Add projection formulas for each of the Database values. All projections will be in the form of fixed percentages of compound growth (or decline) for the projection period. Note that values entered in column C, Annual Change %, are used in the projection formulas to compound that percent rate over the projection period (i.e., over 5 years, compounded annually). Therefore, each year’s value will be the previous year’s value plus the percentage of the previous year’s value. Note that a negative percent value entered into the Annual Change % column will result in a decrease (reduction) in the projections.

All of the values in the Model are calculated from the values in the Database. Therefore, projection formulas are not needed for any of the projection model (2014 – 2018) columns. Instead, simply copy and paste the formulas from the 2013 Base Year column to each of the projection model (2014 – 2018) columns (columns D, E, F, G, and H, respectively).

The final element to complete is the row showing the effect or magnitude of the annually compounded changes on the Profit After Tax for each projected year. Formulas need to be entered into cells D35 through H35 that will calculate the change in the Profit After Tax relative to the 2013 Base Year value.

When completed (with no values entered into the Annual Change % column but the 2013 Base Year values set) your spreadsheet should resemble the example in Figure 1. The balance of the spreadsheet should change whenever a projection variable changes (i.e. without any other intervention on your part).

Run each of the scenarios listed below to determine the organization's sensitivity to certain growth factors. All growth (and decline) factors are assumed to be compound annual percentages, unless stated otherwise in the scenario. Each scenario assumes all items not mentioned in the scenario are the base

IS 312: Building a DSS In Excel, Part II Course Project – Spring 2014

Page 2 of 3

year values. Be sure to return to the base assumptions before attempting each question. Your spreadsheet, if built correctly, will return the correct answers to the below questions.

Figure 1. Completed DSS II Tool with No Annual Change

(NOTE: Where I ask for descriptive answers, give short and to-the-point answers, not longer than one sentence.)

1. Assuming no other changes, with a compound annual growth in gross sales of five percent, what will be the profit after tax in:

a. year 1 (2014)? b. year 2 (2015)? c. year 3 (2016)? d. year 4 (2017)? e. year 5 (2018)?

2. Returning to base assumptions, you are considering new plant and equipment that will reduce variable overhead 10 percent a year but increase fixed overhead 15 percent a year.

a. What will be the effect of this investment on net income five years from now (year five – 2018 - profit after tax)?

b. Should you do the project? c. Why should you do or not do the project?

IS 312: Building a DSS In Excel, Part II Course Project – Spring 2014

Page 3 of 3

3. Management is currently in collective bargaining negotiations considering possible annual percentile wage increases. You are asked to report what the effect of certain increases will be on net income five years from now (year 2018) for each of the following annual compound percentage increases: (note: I am asking for the effect which means 'how much will net income go up or down five years from now compared to base year assumptions,' not 'what will the net income be five years from now').

a. 5 percent b. 10 percent c. 15 percent

4. You have discovered there to be a two to one relationship between increases in gross sales and MM&G expenses (for every one percent increase in sales, there is a two percent increase in MM&G). If this were true, what would the net income be five years from now (year 2018) if there were an annual percentage increase in gross sales of:

a. 2 percent? b. 5 percent? c. Is it worthwhile to try to increase sales? d. Why?

5. You have a scheme to reduce returns and allowances by 6 percent per year; but, implementing the scheme means that MM&G will increase by one percent per year.

a. What will be the change (effect) in net income in year five if you implement the scheme? b. Is it a good scheme? c. Why?

You are to turn in the following: Answer sheet: • A one-page, word-processed, document on which you have answered each of the above questions. • The sheet will have your name (in First then Last format) along with the class and section in the

upper-right corner and the answers will be double-spaced. • The hardcopy printout of the answer sheet is to be handed in, in class, at the beginning of class on

Monday, May 5. DSS spreadsheet file: • Rename the file using your last name followed by your first name as the filename.

o For instance, the filename I would create would be MillerDavid.xls (note that there is no space, comma, etc. in the file name).

o Note too, that if you do not have the folder option turned on to display the file extensions of known file types (that is the default setting so it is likely that this option is not on for your computer) then the .xls file extension will not be displayed. Do not add the .xls if you do not see it when you look at the file name in Windows Explorer.

• Your completed DSS tool (the completed spreadsheet) with base assumptions (i.e., no values in the Annual Change % column) is to be submitted as an attachment to an e-mail message. o Be sure to enter the following as the subject with the proper course number for your class:

 IS 312 DSS II File 12682, if you are in the Mon/Wed 11:00am class  IS 312 DSS II File 12691, if you are in the Mon/Wed 12:30pm class

• The file is to be emailed by classtime Monday, May 5.