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. 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 Returns and Allowances Net Sales $3,500,000 $140,000 $3,360,000 Expenditure Labor Materials Fixed Overhead Variable Overhead Cost of Goods $490,000 $770,000 $70,000 $315,000 - $1,645,000 Profit and Taxes Gross Profit M, M and G Profit Before Tax Federal Tax State Tax Profit After Tax Net Income $1,715,000 - $604.800 $1,110,200 -$299,754 - $88.816 $721,630 $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 Sheet.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 $ 20 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 (50) 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)? DATABASE Base Year Change Results REPRESENTATION Income Statement Income Gross Sales Returns and Allowances Net Sales Gross Sales Return Fraction Labor Cost Fraction Materials Cost Fraction Fixed Overhead Overhead Fraction MMandG Fraction Federal Tax Fraction State Tax Fraction Expenditures Labor Materials Fixed Overhead Variable Overhead Cost of Goods MODEL Profit and Taxes Gross Profit M. M and G Returns and Allowances Cost of Goods (Total) Overhead Variable Overhead MMandG Federal Tax State Tax Labor Cost Materials Cost Profit Before Tax Federal Tax State tax Profit After Tax Net Income Net Sales Gross Profit Profit Before Tax Profit After Tax 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. 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 Returns and Allowances Net Sales $3,500,000 $140,000 $3,360,000 Expenditure Labor Materials Fixed Overhead Variable Overhead Cost of Goods $490,000 $770,000 $70,000 $315,000 - $1,645,000 Profit and Taxes Gross Profit M, M and G Profit Before Tax Federal Tax State Tax Profit After Tax Net Income $1,715,000 - $604.800 $1,110,200 -$299,754 - $88.816 $721,630 $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 Sheet.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 $ 20 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 (50) 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)? DATABASE Base Year Change Results REPRESENTATION Income Statement Income Gross Sales Returns and Allowances Net Sales Gross Sales Return Fraction Labor Cost Fraction Materials Cost Fraction Fixed Overhead Overhead Fraction MMandG Fraction Federal Tax Fraction State Tax Fraction Expenditures Labor Materials Fixed Overhead Variable Overhead Cost of Goods MODEL Profit and Taxes Gross Profit M. M and G Returns and Allowances Cost of Goods (Total) Overhead Variable Overhead MMandG Federal Tax State Tax Labor Cost Materials Cost Profit Before Tax Federal Tax State tax Profit After Tax Net Income Net Sales Gross Profit Profit Before Tax Profit After Tax