Information Systems for Business Building a DSS in Excel

The following assignment is intended to be a brief introduction to how a spreadsheet application such as Microsoft Excel may be used as a Decision Support System for sensitivity analysis ("What if?" analysis). You are to follow the instructions to complete and submit the assignment as individuals or couples. You will have to complete the DSS tool. The Income Statement for 2014 is shown below, as are some descriptions of the relationships between data items contained in the Income Statement. You are to use this data and the descriptions to complete the Model of the DSS from the relationships described.

Income Statement

Year Ending December 31, 2014

Income

Gross Sales	$3,500,000
Returns and Allowances	$140,000
Net Sales Expenditure		$3,360,000
Labor	$490,000
Materials	$770,000
Fixed Overhead	$70,000
Variable Overhead	$315,000
Cost of Goods Profit and Taxes		$1,645,000
Gross Profit	$1,715,000
M, M and G	$604,800
Profit Before Tax		$1,110,200
Federal Tax	$299,754
State Tax	$88,816

Profit After Tax $721,630

Net Income $721,630

A company is producing and selling several products for gross sales (income) of 3.50 million dolars(in 2014). Returns and allowances are 4 percent of gross sales, reducing the income to net sales. Marketing, management, and general expenses (MM&G) are figured to be 18% of the net sales. Cost of goods sold is the sum of labor (14% of sales), materials (22% of sales) and overhead. Overhead is figured to be variable overhead (25% of the combined cost of labor and materials) plus fixed overhead ($70,000). Gross profit is the difference between net sales and cost of goods sold. To figure the profit before tax, MM&G expenses need to be subtracted from the gross profit. Finally, there is the 27% Federal taxes and 8% state taxes that need to be considered, but these are only assessed if there is indeed a profit; if the profit before tax is zero, the tax is zero (therefore, you will need to use the IF function for tax calculations).

Your tasks:

Create the DSS tool in Excel starting with the 152_MIS440Ass1_dssSheet.xlsx spreadsheet I have provided. Note that the worksheet has three areas, the Database, Model, and Representation. These correspond to the three major components of a DSS. The Database shall contain only numbers while the Model will contain the formulas that define the association between the Database values and provides the output values used in analysis. The Representation acts as an interface between the DSS and user, placing the data and results in a format that can be easily interpreted by the user. It is important that you retain appropriate separation between the components.

Enter the data values identified in the problem statements above. You will alter the Database values to perform your sensitivity analysis. That is to say, when you change the value in the Database section, these changes should be reflected in the Model and the Results column of the Representation. There shall be only numeric values in the Database; that is, no computations shall be performed in the Database. [Note: Fraction= percentage (part of) ]

Build in the formulas as necessary to create the Model. You need to identify and build into the Model each association between variables described in the problem statements above. There shall only be formulas in the Model; that is, there shall be no constants in any of the formulas.

Within the Representation, each cell of the Results column that corresponds to a Base Year column value, shall draw its displayed value from the Database and Model as appropriate; that is, there should be no constants in the Results column. In the Change column, a simple calculation will need to be performed to display the difference between corresponding values of the Base Year and Results columns.

The Database values are also known as the independent variables; that is, they may be changed independent from any of the other values. The values displayed in the Model as a result of the computations are dependent variables; that is, whatever value is displayed is dependent upon the Database value(s) and their association to one another. When you change a value in the Database section, the change will be reflected in the dependent variables of the Model.

The Results of the Representation will change without any other intervention on your part whenever an independent variable changes. All monetary values shall be displayed to the nearest dollar, and other numbers in the database, subsequently used in calculations, should be displayed to the nearest whole number (the various fractions should be displayed as percent).

That data values and relationships described in the above problem statements form the base year assumptions that reflect actual data relationships used to construct the DSS tool. Once the DSS tool has been completed, you are ready to perform the sensitivity (What if?) analysis. The analysis is performed by answering the fourteen questions below. For each question, you are to change the assumptions, then derive the answers to the questions. Your spreadsheet, if built correctly, will return the correct answers to these questions. All of the questions are independent of one another. Therefore, you need to be sure to return to the base assumptions before attempting the next question. You may want to use the Excel Goal Seek tool for scenarios that look for a specific dependent variable value to be reached. Help with using Goal Seek is available for both Excel 2003 and Excel 2007 or you can refer tutorial of Goal Seek in Excel from following website:

http://www.homeandlearn.co.uk/excel2007/excel2007s7p4.html

Please only provide the dollar value of your response (i.e., do not phrase your response as a sentence). Also note that appropriate responses to questions may come from either the Results column or the Change column of the Representation depending on how the question is framed. Be sure your answer is the value from the appropriate column.

1. What will be the profit after tax if gross sales decline to $ 2.0 million?

2. What would be the change in Federal Taxes if the tax rate changes to 32 percent?

3. Assume that the Federal tax rate is 40% and gross sales total $4,000,000, what would the change be in net income?

4. As in question 3, assume that the Federal tax rate is 40%; but this time, assume that gross sales are only $3,000,000. What will be the change in net income (from the base year, not question 3)?

5. What is the change in net income if the materials cost fraction could be reduced to 20% per year?

6. What would be the change in net income if fixed overhead is doubled?

7. If State taxes triple, what would be the after tax profit?

8. What would the profit (or loss) be if sales dropped to $500,000, and Fixed Overhead increased to $100,000?

9. Assume that the Return Fraction increases to 7%. What would be the Profit after Tax?

10. Assume that every fraction increases by 5% (5 percentage points), and Fixed Overhead is $100,000. What will be the profit after tax?

11. What would be the change in net income if gross sales were $250,000?

Goal-Seeking Questions:

12. What amount would gross sales have to be in order for the company to show zero ($0) profit (the break-even sales point)?

13. What gross sales are needed to generate an after tax profit of $1,000,000?

14. By what amount would gross sales have to fall in order for the company to show a loss of $10,000 $30,000 (specify the value)?

Submission Instructions:

You have to submit:

1. An excel worksheet contains SHEETS (1-14) on which you have answered each of the above questions. For your answers, all I need is a highlighted dollar amounts.

2. Your completed DSS tool (the completed spreadsheet), set to the base assumptions in sheet named as DSS. Rename your spreadsheet to : MIS440Ass1_[yourID]_[your parners ID]

3. This file has to be submitted via Schoology as an attachment (Assignment).

4. Make sure that you have inserted your name into the appropriate cell of the spreadsheet. The due date for this assignment is posted in Schoology>Assignment page.

REPRESENTATION Income Statement 7 DATABASE Base Year Change Results Income 9 Gross Sales 10 Return Fraction 11 Labor Cost Fraction 12 Materials Cost Fraction 3 Fixed Overhead 4 Overhead Fraction 15 MMandG Fraction 16 Federal Tax Fraction 17 State Tax Fraction 18 9 MODEL 20 21 Returns and Allowances 22 Cost of Goods 23 Total) Overhead 24 Variable Overhead 25 MMandG 26 Federal Tax 27 State Tax 28 Labor Cost 29 Materials Cost 30 31 Gross Sales Returns and Allowances Net Sales Expenditures Labor Materials Fixed Overhead Variable Overhead Cost of Goods Profit and Taxes Gross Profit M, M andG Profit Before Tax Federal Tax State tax Profit After Tax Net Income REPRESENTATION Income Statement 7 DATABASE Base Year Change Results Income 9 Gross Sales 10 Return Fraction 11 Labor Cost Fraction 12 Materials Cost Fraction 3 Fixed Overhead 4 Overhead Fraction 15 MMandG Fraction 16 Federal Tax Fraction 17 State Tax Fraction 18 9 MODEL 20 21 Returns and Allowances 22 Cost of Goods 23 Total) Overhead 24 Variable Overhead 25 MMandG 26 Federal Tax 27 State Tax 28 Labor Cost 29 Materials Cost 30 31 Gross Sales Returns and Allowances Net Sales Expenditures Labor Materials Fixed Overhead Variable Overhead Cost of Goods Profit and Taxes Gross Profit M, M andG Profit Before Tax Federal Tax State tax Profit After Tax Net Income