Assignment 2 Decision Support System (DSS).
Added on - 22 Sep 2019
Assignment 2Decision Support System (DSS)DescriptionMarks out ofWtg(%)Due dateASSIGNMENT 2100.0025.0012thOctober 2016This assignmentmust be your own work. It is acceptable to discuss course content withothers to improve your understanding and clarify requirements, but solutions to assignmentquestions must be done on your own.You must not copyfrom anyone, including tutors andfellow students,nor allow others to copy your work. All Assignments will be checked usingcollusion monitoring tools to ensure that each assignment is the original work of thesubmitting student. Assignments that do not adhere to this requirement will be deemed asbeing the result of collusion or plagiarism. This may lead to severe academic penalties asoutlined in USQ Policy Library: Academic Integrity Policy and Procedure. It is your ownresponsibility to ensure the integrity of your work. Refer to the USQ Policy Library for moredetails:Academic Integrity PolicyAcademic Integrity ProcedureIn completing this assignment, you are expected to use available resources such as thepractical activities in the study modules, the Course Study Desk – especially the DiscussionForums (click the Study Desk link on UConnect–<http://uconnect.usq.edu.au>), as well asexploring and experimenting on your own.Applicable course objective:demonstrate problem-solving skills by identifying and resolving issues relating to informationsystems and their components, and proficiently utilise different types of information systemssoftware (especially gaining proficiency in utilising databases, spreadsheets, and presentationapplications).demonstrate written communication skills by understanding basic information, communicationand technology (ICT) terminology for effective communication and applying it within a businessenvironment.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(any version from Excel 2007 to current), some or all of which might be very unfamiliar to you. Theassignment description provides some explanation of how to use these components, but you are alsoexpected to use available resources such as Online Help, the Beskeen et al text, the Practical SkillsCIS1000 Assignment 2 QuestionPage1
sections at the end of each Module in the Study Materials, the SAM online tutorial activities and thecourse discussion forums, as well as exploring and experimenting on your own.PreambleDr Nikola Tesla wasextremely impressed with our computing firm’s –Einstein Technologies–development of theYe Olde Steampunk Emporium’sDatabase Management System (DBMS)utilising Microsoft Access (Assignment 1). The business has contracted our firm to assist in setting upmore ofthe business’svarious computer-based information systems.The next computer-based information system that the business is interested in is a Decision SupportSystem (DSS) utilising Microsoft Excel(any version from Excel 2007 to current). The DSS will beused 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)(Assignment 1) imported into Microsoft Excel(any version from Excel 2007 to current)so that theinformation can be summarised as a report and some future analysis of sales trends performed. Thesuppliers used by the business, source items from either Luxembourg (LU) orTuvalu (TV). Theyallow the business to select from either of these two regions on an ad hoc basic depending on the mostfavourable exchange rate at the time.Dr Nikola Tesla has noted that a number of the business’s competitors are providing a discount tocustomers who place large orders. The business would like to see what affect adopting a similarpolicy would have on the business. The business has also noted a growing number of onlinebusinesses 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 optionsto ascertain the best combination for the business if they were to adopt this policy too.All phases in this project must be developed with professionalism and user-friendliness in mind.www.steampunktribune.com,Karl Edwards Studios.CIS1000 Assignment 2 QuestionPage2
Decision Support System DesignMrs Ada Lovelace, your manager, has set up the following tasks for you to complete for this phase ofthe project:1.Create a Spreadsheet and import the four (4) Access Tables into four (4) Worksheets2.Data Validation Check3.Create an Index Worksheet4.Create a Data Input Worksheet Template for later use5.Create a Calculations Worksheet Template for later use6.Create Name Ranges for the Customers, Items, and Suppliers data7.Create a Report Worksheet and set up the column headings8.Modify the Report Worksheet by Cell Referencing all the Orders Table data9.Modify the Report Worksheet by using VLOOKUP to get Customer, Item and Supplier data10.Modify the Data Input Worksheet to include extra data needed for tasks 11 to 1511.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 Price13.Modify the Report Worksheet by using a Nested IF to calculate Freight Cost14.Modify the Report Worksheet by using a AND / IF to calculate Item Discount15.Modify the Report Worksheet by using simple formulas to find Purchases and Sales, andModify the Report Worksheet by using a formula to calculate the Order Discount16.Modify the Data Input Worksheet to include extra data needed for tasks 18 to 2217.Modify the Calculations Worksheet by using simple formulas18.Modify the Data Input Worksheet by Cell Referencing all the Calculations data19.Create eight (8) Scenarios on the Data Input Worksheet20.Create a Scenario Summary of the eight (8) Scenarios21.Create an Documentation Worksheet22.Create an Analytical Essay to describe the findings made using the SpreadsheetTask 1: Create and ImportOpen asinglenewExcel (any version from Excel 2007 to current)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 (any versionfrom Access 2007 to current) Database File and into Microsoft Excel (any version from Excel 2007 tocurrent) (tblCustomers, tblItems, tblSuppliers, tblOrders). The easiest and quickest way to import datafrom Access (any version from Access 2007 to current) into Excel (any version from Excel 2007 tocurrent) is by using the “Import From Access” Wizard. The following steps will assist you with thisprocess: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”.CIS1000 Assignment 2 QuestionPage3
3)Go to theDataIcon Ribbon (see below)4)Click on theFrom Accessoption in theGet External Dataicon area.5)In theGet External Data – From Accesswizard popup,browsetofind your Assignment 1Access Database file and select the – then click Open.6)In theSelect Tablewizard popup, select tblCustomers – click OK.7)In theImport Datawizard popup, selectTableandExisting Worksheet$A$3as the location toImport the place to put the data.8)Left click anywhere on the imported data in worksheet then go to theDesignIcon Ribbon andselectConvert to Rangethen 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 theheadings 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 asfollows:Database TableWorksheet NameWorksheet Title (Cell A1)tblItemsItemsTableItems TabletblSuppliersSuppliersTableSuppliers TabletblOrdersOrdersTableOrders TableTask 2: Data Validation CheckCheck the imported data in the CustomersTable, ItemsTable, SuppliersTable and OrdersTable toensure that:1.The column headings are displayed correctly2.The content of each column displays in a format that appears valid for that type of data (applyappropriate formatting if required).3.The content of each column contains complete and accurate data values (eg. Phone numbersare 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 orinaccurate values, please contact the CIS1000 course team immediately. You will be issued with aCIS1000 Assignment 2 QuestionPage4