Lab decision support and intelligence system 9th edition 
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 f...." 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 Gross Sales Retums and Net Sales $3,500.000 140 $3,360,000 Labor Materials Foxed Overhead Variable Overhead Cost of Goods $490,000 $770,000 $70,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 $1,110,200 $299,754 $721,630 721630 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 Page 2 of 4 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 f...." 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 Gross Sales Retums and Net Sales $3,500.000 140 $3,360,000 Labor Materials Foxed Overhead Variable Overhead Cost of Goods $490,000 $770,000 $70,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 $1,110,200 $299,754 $721,630 721630 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 Page 2 of 4
