Decision Support System (DSS)

Applicable course objective:

• demonstrate problem-solving skills by identifying and resolving issues relating to information systems and their components, and proficiently utilise different types of information systems software (especially gaining proficiency in utilising databases, spreadsheets, and presentation applications).

• demonstrate written communication skills by understanding basic information, communication and technology (ICT) terminology for effective communication and applying it within a business environment.

Applicable graduate qualities and skills gained from this assessment instrument:

• Problem Solving (Skill U2)
• Written & Oral Communication (Skill U4)

This assignment is quite complex, and exposes you to many different components in Microsoft Excel 2007/2010/2013, some or all of which might be very unfamiliar to you. The assignment description provides some explanation of how to use these components, but you are also expected to use available resources such as Online Help, the Beskeen et al text, the Practical Skills sections at the end of each Module in the Study Materials, the SAM online tutorial activities and the course discussion forums, as well as exploring and experimenting on your own.

Preamble

Dr Shirley "Shirl" Lock-Holmeswas extremely impressed with our computing firm's - Moriarty Technologies- development of the Steam Age Emporium's Database Management System (DBMS) utilising Microsoft Access 2007/2010/2013 (Assignment 1). The business has contracted our firm to assist in setting up more of the business's various computer-based information systems.

The next computer-based information system that the businessis interested in is a Decision Support System (DSS) utilising Microsoft Excel 2007/2010/2013. The DSS will be used to analyse sales trends for the business to determine future courses of action for the business.

Dr Shirley "Shirl" Lock-Holmes would like the information in the Database Management System (DBMS) (Assignment 1) imported into Microsoft Excel 2007/2010/2013 so that the information can be summarised as a report and some future analysis of sales trends performed. The suppliers used by the business, source items from either Finland(FI) or Brunei (BN). They allow the business to select from either of these two regions on an ad hoc basic depending on the most favourable exchange rate at the time.

Dr Shirley "Shirl" Lock-Holmeshas noted that a number of the business's competitors are providing a discount to customers who place large orders. The businesswould like to see what affect adopting a similar policy would have on the business. The businesshas also noted a growing number of online businesses are starting to provide free freight delivery as a way of encouraging more online business; the businesswould like to run a number of scenarios based on potential Mark-up and Freight options to ascertain the best combination for the business if they were to adopt this policy too.

Decision Support System Design

Mr Hach G Wells, your manager, has set up the following tasks for you to complete for this phase of the project:

1. Create a Spreadsheet and import the four (4) Access 2007/2010/2013 Tables into four (4) Worksheets
2. Data Validation Check
3. Create an Index Worksheet
4. Create a Data Input Worksheet Template for later use
5. Create a Calculations Worksheet Template for later use
6. Create Name Ranges for the Customers, Items, and Suppliers data
7. Create a Report Worksheet and set up the column headings
8. Modify the Report Worksheet by Cell Referencing all the Orders Table data
9. Modify the Report Worksheet by using VLOOKUP to get Customer, Item and Supplier data
10. Modify the Data Input Worksheet to include extra data needed for tasks 11 to 15
11. Modify the Report Worksheet by using a Nested IF to calculate Cost Price (AU)
12. Modify the Report Worksheet by using a Nested IF to calculate Selling Price
13. Modify the Report Worksheet by using a Nested IF to calculate Freight Cost
14. Modify the Report Worksheet by using a AND / IF to calculate Item Discount
15. Modify the Report Worksheet by using simple formulas to find Purchases and Sales, and Modify the Report Worksheet by using a formula to calculate the Order Discount
16. Modify the Data Input Worksheet to include extra data needed for tasks 18 to 22
17. Modify the Calculations Worksheet by using simple formulas
18. Modify the Data Input Worksheet by Cell Referencing all the Calculations data
19. Create eight (8) Scenarios on the Data Input Worksheet
20. Create a Scenario Summary of the eight (8) Scenarios
21. Create an Documentation Worksheet
22. Create an Analytical Essay to describe the findings made using the Spreadsheet

Task 1: Create and Import

Open a single new Excel 2007/2010/2013 spreadsheet and name the file - ‘[lastname] [initial] _ [student number] _ [course code] _ [assignment number]' (eg. genrichr_0050051005_cis1000_assign2.xlsx).

Import the following four (4) database tables from your Assignment 1 Microsoft Access 2007/2010/2013Database File and into Microsoft Excel 2007/2010/2013 (tblCustomers, tblItems, tblSuppliers, tblOrders). The easiest and quickest way to import data fromAccess 2007/2010/2013 into Excel 2007/2010/2013is by using the "Import From Access" Wizard. The following steps will assist you with this process:

1) Select the first unused tab at the bottom of the Spreadsheet, right click on it and rename it "CustomersTable".

2) Put a heading at the top of the worksheet in cell A1 called "Customers Table".

3) Go to the DataIcon Ribbon (see below)

4) Click on the From Access option in the Get External Dataicon area.

5) In the Get External Data - From Accesswizard popup, browse to find your Assignment 1 Access Database file and select the - then click Open.

6) In the Select Tablewizard popup, select tblCustomers - click OK.

7) In the Import Datawizard popup, select Tableand Existing Worksheet$A$3 as the location to Import the place to put the data.

8) Left click anywhere on the imported data in worksheet then go to the DesignIcon Ribbon and select Convert to Range then click OK.

9) Check that the data has correctly been imported correctly into this worksheet.

10) Modify the layout of the data to a professional level of presentation, making sure that the headings are in English (Customer ID not CustID).

Use bold, italics, font size, font colours, shading, lines and borders.

Repeat the above 10 steps for the rest of the Access Database tables naming each worksheet as follows:

Task 2: Data Validation Check

Check the imported data in the CustomersTable, ItemsTable, SuppliersTable and OrdersTable to ensure that:

1. The column headings are displayed correctly

2. The content of each column displays in a format that appears valid for that type of data (apply appropriate formatting if required).

3. The content of each column contains complete and accurate data values (eg. Phone numbers are not truncated).

4. The content of each column contains reasonable data values for the use of this business.

If you discover that the data imported in any of your 4 table worksheets contains missing or inaccurate values, please contact the CIS1000 course team immediately. You will be issued with a new copy of the Assignment 1 Access 2007/2010/2013 Database file (containing the 4 Database Tables) to restart Task 1.

Task 3: Index Worksheet

Add a worksheet labelled Index before the four tables from Task 1. Put a heading at the top of the worksheet in cell A1 called Index Worksheet.Ensure that it contains all the recommended data for this worksheet listed in Appendix 7 of the Study Materials - Spreadsheet design considerations.

Modify the layout of the data to a professional level of presentation. Use bold, italics, font size, font colours, shading, lines and borders.

Task 4-22 are in uploaded document

Attachment:- Assignment task.rar