Assignment - Decision Support System

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)

Preamble

Dr Nikola Teslawas extremely impressed with our computing firm's - Einstein Technologies- development of the Ye Olde Steampunk Emporium's Database Management System (DBMS) utilising Microsoft Access (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 business is interested in is a Decision Support System (DSS) utilising Microsoft Excel (any version from Excel 2007 to current). The DSS will be used to analyse sales trends for the business to determine future courses of action for the business.

Dr Nikola Tesla would like the information in the Database Management System (DBMS)imported into Microsoft Excel (any version from Excel 2007 to current) 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 Luxembourg(LU) or Tuvalu(TV).  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 Nikola Teslahas 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 business would 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

Mrs Ada Lovelace, 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 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.

Attachment:- DSS Assignment.rar

Attachment:- Assignment01.zip