Limited Offer Get 25% off — use code BESTW25
No AI No Plagiarism On-Time Delivery Free Revisions
Claim Now
Prices start at $5 per page · plagiarism-free papers · free revisions for 14 days 24/7 support: support@bestwriters.com
Uncategorized

Database Management System

  • Posted
  • Authoradmin
  • Length11,155 words
  • Reading time49 min
Write My Assignment FREE
Database Management System (DBMS) & Decision Support System (DSS)
Description Marks out of Wtg(%) Due date
Assignment 1 100.00 20.00 6 May 2020




IMPORTANT INFORMATION

Academic Integrity

  • This assignment must be all your own work. It is acceptable to discuss course content with others to improve your understanding and clarify requirements, but solutions to assignment questions must be done on your own.
  • You must not copy from anyone, including tutors and fellow students, nor allow others to copy your work.
  • All Assignments will be checked using collusion monitoring tools to ensure that each assignment is the original work of the submitting student. Assignments that do not adhere to this requirement will be deemed as being the result of collusion or plagiarism. This may lead to severe academic penalties as outlined in USQ Policy Library: Academic Integrity Policy and Procedure. It is your own responsibility to ensure the integrity of your work. Refer to the USQ Policy Library for more details:
  • Academic Integrity Policy
  • Academic Integrity Procedure

Course Expectations

  • This assignment is quite complex, and exposes you to many different components in Microsoft Access (any version from Access 2007 to current), 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, the course discussion forums, appendices and practical related videos, as well as exploring and experimenting on your own.
  • It is important to make sure you have backups of all assignments you create. This is especially true if you store documents on a flash (thumb) drive. Flash drives can become corrupted or lost very easily. There are many options available (Dropbox, or Google Drive) and USQ students already have access to Google Drive via uConnect – UDrive. Another good idea is to Email your assignment file to yourself every-time you finish working on it, that way you have multiple copies of your progress in case you need to revert to a previous stage or are looking for a piece of information you have mistakenly deleted.
  • Note: Lost or corrupted assignment files is not grounds for an extension.
    It is your responsibility to ensure you have backup copies of your files.

Late submission policy

  • USQ’s Assessment – Assignment (Late Submission) and Compassionate and Compelling Circumstances procedures relate to Extensions and Late Assignments. They can be found under the following links:
  • Assessment – Assignment (Late Submission) Procedure
  • Assessment of Compassionate and Compelling Circumstances Procedures
  • An Assignment submitted after the due date without an approved extension of time will be penalised. The penalty for late submission is a reduction by five percent (5%) of the maximum mark applicable for the assignment, for each University Business Day or part Business Day that the assignment is late. An assignment submitted more than ten (10) University Business Days after the due date will have a mark of zero recorded for that Assignment.

Referencing requirements

  • References are required and the Harvard AGPS standard of referencing must be used, including in-text referencing. Ensure that your essay is fully referenced, including any references to the text book. Your essay should include a List of References using the Harvard AGPS standards. A useful link on referencing is from USQ’s Library site: <http://www.usq.edu.au/library/referencing>
  • Any direct and paraphrased quotes must be referenced using Harvard AGPS style and listed in the List of References.

Do not repeat verbatim large portions of information from other sources such as the text or video – you must put the ideas/information in your own words. Only use small direct quotes where possible.

ASSIGNMENT 1 REQUIREMENTS

Learning Objectives

Applicable course objective:

  • Course Objective 3: critique information systems concepts to identify and resolve organisational problems and to develop ethical considerations within a business context
  • Course Objective 4: identify the appropriate information systems to develop practical solutions within a business context
  • Course Objective 5: communicate information systems concepts to both technical and non-technical audiences within a business context.

Preamble

Dr Stan Lee retired in 2018 after a long career in General Practice Medicine. To keep busy and to fulfil a lifelong dream, Stan started a Memorabiliamail order business, specialising in importing “DC Comics and Marvel Superhero” related products, called the “Excelsior Emporium”. The business specialises in imported Superhero merchandise such as statues, iPhone skins, giftware, bobbleheads, mugs, clothing and other assorted Superhero related paraphernaliato customers via mail order around Australia.

Superhero merchandise has become very popular, since the release of movies like the Aquaman, Black Panther, Captain America, Captain Marvel, Iron Man, Thor, Spiderman and the Avengers have brought the phenomenon back into the public’s attention; and Stan’s business has experienced a rapid growth in their mail orders. When Stan first retired, the business bought a personal computer to help manage the books and finances.

Excelsior Emporium is located at Shop 19, 63 Kent-Parker Boulevard, Toowoomba (about 100 km west of Brisbane). Their ABN is 126 279 410. Stan can be contacted via phone on (07) 4632 1856; or email on enquiries@EE.com.au.

Stan is extremely impressed with our computing firm “Brainiac Systems” and the business has contracted our firm to assist them in setting up their various computer-based information systems.

Part A: The first computer-based information system that Stan is interested in is a Database Management System (DBMS) utilising Microsoft Access (any version from Access 2007 to current). The DBMS will store all the business’s customer details, information about the stock items that the business sells, the suppliers that the business uses and all postal order transactions for the business. It will allow Stan to run several queries on the data, which the business has specified below, and they wish to be able to print out an invoice for each customer at the end of each month.

Part B: 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 analyse sales trends for the business to determine future courses of action for the business.

Stan would like the information in the Database Management System (DBMS) (Assignment 1 Part A) 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 Belgium (BE) or Canada (CA). 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.

Stan has noted that several the business’s competitors are providing a discount to customers who place large orders. The business would like to see what affect adopting a similar policy would have on the business. The business has 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.

Part C: Stan also has several questions relating to the two computer-based information systems in Part A & Part B that he would like you to answer, prepare an Essay to answer these questions he has listed.

https://www.teepublic.com/en-gb/sticker/3548978-stan-lee-excelsior,
Stan Lee EXCELSIOR!.

Assignment 1: Part A (Database Management System)

Your firm’s Systems Analyst has developed a database structure that has been determined to be suitable to create a database to store Stan’s customer, item, supplier and order data for the business, which has been provided as follows:

Table Specifications

Table: tblCustomers
Fields: Cust ID (Primary Key), Title, Family Name, Given Names, Date of Birth, Address, City, State, Postcode, Freight Distance (Km), Mobile Number, eMail Address, Mailing List.
Table: tblItems
Fields: Item ID (Primary Key), Type, Description, Size, Freight Weight (Kg), Supplier ID, In Stock, On Order, Cost Price (BE), Cost Price (CA). BE is the country code for Belgium (their currency is the Euro) CA is the country code for Canada (their currency is the Canadian Dollar)
Table: tblSuppliers
Fields: Supplier ID (Primary Key), Supplier Name, Address, City, State, Postcode, Phone Number, Recommended MarkUp
Table: tblOrders
Fields: Cust ID, Item ID, Order Date (Primary Key), Dispatch Date, Order Qty Note: The primary key of the Orders table is a combined multiple-field key. It has three fields in one key.

Stan has provided to us a partial list of the business’s customers, stock items, and suppliers and a partial list of the business’s postal orders placed between January and June 2019 to use as test data in four comma delimited text files, which can be found on your firm’s Intranet (CIS5100 Study Desk).

Dr Zatanna Zatara, your manager, has set up the following tasks for you to complete for this phase of the project:

  1. Create a Database and import the four (4) Text files into four (4) Tables
  2. Modify Table Design
  3. Set Primary Keys
  4. Create Relationships between the Tables
  5. Edit Customers Table
  6. Create Data and Update Queries
  7. Create an Invoice Report

All phases in this project must be developed with professionalism and user-friendliness in mind.

Task 1: Create and Import

Create a new blank Access Database (using any version from Access 2007 to current) and name it [lastname]

[initial]

_ [student number] _ [course code] _ assign1_(part_a).accdb
eg. genrichr_0050051005_cis5100_assign1_(part_a).accdb.

Import the four sets of data from your firm’s Intranet (CIS5100 Study Desk).

The easiest and quickest way to import data into Access is by using the Import Text Wizard. The following steps must be followed carefully to ensure the data is imported correctly:

Importing Tab Delimited Text files into Access (any version from Access 2007 to current) using the Wizard  

  1. On the External Data tab, in the Import & Link group, click the Text File data source icon.
  2. In the Get External Data – Text Filewizard popup, browse to find the ‘Assignment01 Text File Customers.txt’ text file and select the Import the source data into a new table in the current database option – then click OK.
  3. Select the Delimited – Characters such as commas or tab separate each field option on the first page of the wizard – then click Next.
  4. Select the Tab option on the second page of the wizard and turn on the First Row Contains Field Namescheck box – then click Next.
  5. Set the following for each field on the third page of the wizard:
    • Field Name – keep the field name as it appears when imported
  • Note: Changing Field Names rather than using Captions will result in loss of marks.
  1. Data Type – giving each an appropriate data type (students will be marked on their logical choice of data types, based on the sample data provided).
  • See the Beskeen et al Microsoft Office Illustrated textbook Chapter 7, the Forum Postings, Practical Appendixes and the Practical Videos provided in the Assignment 1 Assessment Tab for more details on setting appropriate Data Types, particularly Chapter 7: Table 5-3: Common Number field properties for logical Data Types.
  • Note: Numbers that will not be used for calculations should be set as Text. Numbers such as Freight Weight and Order Qty must be carefully and logically set to avoid loss of data (numbers without decimal places should be set to be either binary, integer or long integer; and numbers with decimal places should be set to be single or double).
  • DO NOT USE Large Number (BigInt) Data Type.
  • When you are sure that each field has the appropriate data type, click Next.
  1. Select No Primary Key option on the fourth page of the wizard – these will be set in Task 2 – then click Next.
  2. Name the table tblCustomers on the final page of the wizard and click Finish.

Repeat the above 7 steps for the rest of the text files naming each Database Table as follows:

Text File Table Name
Assignment01 Text File Items.txt tblItems
Assignment01 Text File Suppliers.txt tblSuppliers
Assignment01 Text File Orders.txt tblOrders
  • Note: Table names must conform to the Leszynski Naming Convention (LNC) as per the Naming Convention for Access Database Objects document in the Practical Appendixes found on the Study Schedule and Module Materials.

Task 2: Modify Table Design

Modify the four imported tables’ structures using the given Table Specifications, from the section above, in Microsoft Access using Table Design View:

See the Beskeen et al Microsoft Office Illustrated textbook Chapter 5, the Forum Postings, Practical Appendixes and the Practical Videos provided in the Assignment 1 Assessment Tab for more details on appropriate Field Property settings for each Data Type.

  • Use the above table specifications, checking that you have the correct table names and field names.
  • Check that all fields have the appropriate Data Type from the following:
    • Text, Number, Date/Time, Currency, Yes/No, Hyperlink.
  • Add appropriate Field Descriptions to ALL data fields with a well-structured description, eg. for CustID an appropriate description would be:

Customer Identification | Data Type: Short Text: 3 Characters

  • Ensure that you also set properties for each field where appropriate. You MUST set the following:
    • Field Size – set appropriate logical fields sizes for all Text and Number data types (numbers without decimal places should be set to be either binary, integer or long integer; and numbers with decimal places should be set to be single or double).
    • Caption – set an appropriate English caption for all fields where the field name is not in English (eg. Customer ID for CustID).
    • Format – set an appropriate format for all: Yes/No and Date data fields; Text data fields that should be stored in Upper-Case (State); Hyperlinks data fields that should be stored in lower-case (EmailAddress); and numbers that contain currency or percentages.
  • BE is the country code for Belgium – should be formatted in Euros;
  • CA is the country code for Canada – should be formatted in Dollars.
    • Decimals – set an appropriate number of decimal places for any single or double numbers
    • Default Value – set an appropriate default value for all Yes / No data Fields
    • Required – set any data Fields that must contain data to Required and leave any Fields where data is optional as unchecked.
    • Validation Rule and Validation Text – include appropriate Validation Rules for each of the following data Fields: Title; State; and Item Type. This ensures that only limited value options can be entered for these data Fields.

For example:

Task 3: Set Primary Keys

  • Note: Before proceeding to Task 3, check the Data in the four tables against the Data in the four text Files to ensure that there has been no loss of Data during Task 1 and 2.

Set the Primary Keys for the four (4) Tables as per the following table specifications in Microsoft Access using Table Design View:

See the Beskeen et al Microsoft Office Illustrated textbook Chapter 4, the Forum Postings, Practical Appendixes and the Practical Videos provided in the Assignment 1 Assessment Tab for more details on creating primary keys.

Table Name Primary Key(s)
tblCustomers CustID
tblItems ItemID
tblSuppliers SupplierID
tblOrders CustID, ItemID, OrderDate
  • Note: The primary key of the Orders table is a combined multiple-field key. It has three fields in one key.

Task 4: Create Relationships between the Tables

Create the following relationships between the four (4) tables:

See the Beskeen et al Microsoft Office Illustrated textbook Chapter 5, the Forum Postings, Practical Appendixes, the Practical Videos provided in the Assignment 1 Assessment Tab, and the YouTube videos on the StudyDesk for more details on creating relationships.

  • tblCustomers to tblOrders
  • tblItems to tblOrders
  • tblSuppliers to tblItems
  • Check that the correct table names and field names are listed in the ‘Edit Relationships’ screen.
  • Ensure that you ENFORCE referential integrity, but do not set Cascading Updates or Cascading Deletes.

You should check that the computer has identified correctly the 1 to ∞ relationships between the four (4) tables.

  • Note: This must be done after you have established the primary keys.

Task 5: Edit Customers Table

Edit Customer ID 112 in the Customers Table as per the following instructions in Microsoft Access using Table Datasheet View:

  1. Change the Title, Given Names and Family Name to your own details.
  2. Change the Address, City, State and Postcode to your own details.
  3. Using www.whereis.com.au look up the distance between your current address and Toowoomba QLD 4350 (to the nearest kilometre) – change the Freight Distance to this new value.
  4. Change the Mobile Number to your student ID eg. 0061 099999
  • Do not use the u1099999 version.
  1. Change the Email Address to your own details.
  2. Select that you would like to be on the Business’s Mail List.

Task 6: Create Data and Update Queries

Create the following Three (3) queries using the Query Design View. Use the names given for each query to avoid confusion for the client (make sure you submit them in the order given below).

  • You must create the Queries using Access Design View, if you create the Queries using SQL this will result in ZERO marks for the Query.
  • Query names must conform to the Leszynski Naming Convention (LNC), as per the Naming Convention for Access Database Objects document in the Practical Appendixes found on the Study Schedule and Module Materials – including the Query Number and Two Meaningful Words to describe the query eg. qry1XxxxxxXxxxxx.
  • Not all queries will require the inclusion of all four tables; you must decide which tables are appropriate for the generation of each query’s output. Only tables needed should be included in the query design as extraneous tables could cause problems with the query results

See the Beskeen et al Microsoft Office Illustrated textbook Chapter 8, Access Chapter 9 and Access Chapter 10, the Forum Postings, Practical Appendixes and the Practical Videos provided in the Assignment 1 Assessment Tab for more details on creating queries

Do not discuss with your fellow students, nor post information about your queries to the StudyDesk Forums; it may result in academic misconduct.

  • Query 1: Import Duty Belguim (BE) Cost Price Increase:

A new Australian import duty of 5.75% has been added for all products being imported from Belguim (BE)and Stan has asked you to develop a query that will update the Cost Price from Belguim (BE) of all items that are currently on order. As it would be considered profiteering, it is important that the query does not update the Cost Price of items already in stock.

Create an Update Query that will raise the Belguim (BE) Cost Price for all items on order that are not in stock by 5.75%. It is always good practice to backup any data before running an update or a delete query. The following steps should be followed to achieve this Query:

  1. In the List of Tables, copy the table called tblItems and paste it with the new name tblBackupItems.

Use tblItems for this Update Query, not your back up table tblBackupItems. If you have difficulties with this query and need to restore your data to the original values, you can copy columns as necessary from tblBackupItems to tblItems and start again.

  1. Create an Update Query that will increase the Cost Price (BE) in tblItems by 5.75% for all items currently not in stock but on order.
  2. Include rounding to 2 decimal places with the update calculation by placing the following function around the update formula: ROUND(update_formula ,2)

Hint:

ROUND(Cost Price (BE) + Cost Price (BE) * 5.75% ,2) 1

Do not discuss with your fellow students nor post formulas to the StudyDesk Forums; it may result in academic misconduct.

Make sure you DO NOT change the Query Type – keep it as an Update Query and
do not run the Update Query more than once.

  • Note: This Query will update 36 rows of data if done correctly.
    Compare the following three Items (
    ItemID 128, 145 & 177) between the tblItems and tblBackupItems to see if the update has been applied successfully.
  • Query 2: Late Dispatched Orders Form Query:

Stan would like to have a single query that the business can display within a form (Task 7: Form 1) to see within this single query the customers who are either:

  1. Living in Queensland, have had items dispatched late (between 15th June and today) and where the item was ordered after the 1st June and the item is in stock

Or

  1. Living in New South Wales, have had items dispatched late (between 20th June and today) and where the item was ordered after the 5th June and the item is in stock

Or

  1. Living in Victoria, have had items dispatched late (between 25th June and today) and where the item was ordered after the 10th June and the item is in stock

Create a Select Query that will display the CustID, Full Customer’s Name (including Title, Given Names, and Family Name), Full Customer’s Address, Date of Birth, eMail Address, MailingList, ItemID, Full Item Details (including Type, Description, Size, Freight Weight and Supplier Name), OrderDate, Dispatch Date and OrderQty for those customers.

Limit the data in the single query to either customers (using the OR Criteria):

  1. living in Queensland, who have placed Orders after the 1st June 2019 and where these orders were only dispatched between the 15th June 2019 and today (using the BETWEEN criteria tool and the DATE() function), and where the business currently has that item in stock
  2. OR living in New South Wales (using the OR criteria row), who have placed Orders after the 5th June 2019 but where these orders were only dispatched between the 20th June 2019 and today (using the BETWEEN criteria tool and the DATE() function), and where the business currently has that item in stock
  3. OR living in Victoria (using the OR criteria row), who have placed Orders after the 10th June 2019 but where these orders were only dispatched between the 25th June 2019 and today (using the BETWEEN criteria tool and the DATE() function), and where the business currently has that item in stock

Display the results sorted in Ascending Order by State and Order Date, check that have you included all necessary Fields and ensure that you only display the required Fields.

  • Note: This Query will display 92 rows of data if done correctly
    (with CustID 112 living in QLD).
    Check that you have
    27 QLD records, 46 NSW records and 19 VIC records.
  • Query 3: Invoice Query with Calculated Fields:

Stan would like to have a query that the business can use to produce customer invoices (Task 8) to see how many customers on our Mailing List ordered Giftware, Bobbleheads, or Pet Accessories, but only those items that were not considered to be Wonder Woman related, and where the Item is in stock. This query should also calculate the selling price (based on goods purchased from the Canada (CA) and order total, both of which will be needed on the customer invoices.

Create a Select Query that will display the CustID, Full Customer’s Name (including Title, Given Names and Family Name), Full Customer Address, ItemID, Full Item Details (including Type, Description, Size, Weight, and Supplier Name), OrderDate, and OrderQty, including two calculated Fields – Selling Price (AU) and Order Total (AU).

Limit the query to only those customers on our Mailing List who ordered “Giftware”, “Bobbleheads”, or “Pet Accessories” (using the IN criteria tools),where the Item DOES NOT contain the words “Wonder Woman” (using the NOT criteria tools and the LIKE criteria tool), and where the business currently has that item in stock.

SellingPrice(AU) Hint:

CostPrice (CA) x Exchange Rate x (1 + Recommended MarkUp)

OrderTotal(AU) Hint:

SellingPrice(AU) x Ordered Qty

Canada (CA) currency is the Canadian Dollar –
Look up Exchange Rate CAD to AUD:
www.xe.com

Include rounding to 2 decimal places for the Selling Price Calculated Field by placing the following function around the formula:

Hint:
ROUND( calculated_Field ,2)

SellingPrice and OrderTotal must be formatted to the appropriate currency and provided with meaningful captions in the properties for these two calculated Fields.

Display the results sorted in Ascending Order by CustID, ItemID and Order Date, check that have you included all necessary Fields and ensure that you only display the required Fields.

  • Note: This query will display 123 rows of data if done correctly.

Task 7: Create an Invoice Report

Stan has provided you with the following sample Invoice. The client requires you to develop a similar invoice for their business.

See the Beskeen et al Microsoft Office Illustrated textbook Chapter 13 and 14, the Forum Postings, Practical Appendixes and the Practical Videos provided in the Assignment 1 Assessment Tab for more details on creating forms

Do not discuss with your fellow students, nor post information about your report to the StudyDesk Forums; it may result in academic misconduct.

  • Note: You must use the correct business name and address details given in the assignment preamble for your invoices – the example above is an example of what the client is looking for only.

Using all Fields from Task 6 Query 3, create in the Report Wizard (using the Block Report Wizard Template) a single Report called rptCustomerInvoice that produces a professional looking invoice for the business.

The invoice must have the following attributes:

  • The report must contain at least all the report elements displayed in the sample invoice (above) and it must use all of the data fields shown in Query 3.
  • The report must be grouped by CustID.
  • Each Customer’s invoice must be on a separate page.
    • Note: To do this, in the Report Design View, right click on ‘CustID Header’, choose ‘Properties’, find the ‘Force New Page’ and change it to ‘Before Section’.
      You should
      only view the Invoice in Print Preview to test that this is working – Report View will not put the Customers on separate pages.
    • Note: This Report will display 50 customer invoices if done correctly.
      Please also note that
      CustID 501 should display all SIX (6) ordered items on ONE page.
  1. Each Customer’s invoice must have the Customer’s name merged into a single Field including Title, Given Names and Family Name by modifying one of the Fields to include:

[Title] & ” ” & [GivenNames] & ” ” & [FamilyName]

  1. Each Customer’s invoice must have part of the Customer’s address merged into a single Field including City, State and Postcode by modifying one of the Fields to include:

[City] & ” ” & [State] & ” ” & [Postcode]

  • Each Customer’s invoice musthave Today’s Date in the Page Header
  • Each Customer’s invoice must have an Invoice Total for each customer
  • Each Customer’s invoice must not have any extraneous data including Grand Totals, Page Numbering, nor Summary Information.
  • Each Customer’s order must have the Order Date in the Details Section
  • Each Customer’s order must have alternating background colours in the Details Section
  • Each Image on the invoice must be inserted using the Image Control and must have the following properties: Properties Options Picture Type: Embedded Control Source: Blank Picture: [File Name]
    • Note: Report names must conform to the Leszynski Naming Convention (LNC) as per the Naming Convention for Access Database Objects document in the Practical Appendixes found on the Study Schedule and Module Materials.

Assignment 1: Part B (Decision Support System)

Dr Zatanna Zatara, 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 later tasks
  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 later tasks
  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 a 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 (any version from Excel 2007 to current) spreadsheet and name the file – ‘[lastname]

[initial]

_ [student number] _ [course code] _ [assignment number]_(part_b)’
eg. genrichr_0050051005_cis5100_assign1_(part_b).xlsx.

Import the following four (4) database tables from your Assignment 1 Microsoft Access (any version from Access 2007 to current) Database File and into Microsoft Excel (any version from Excel 2007 to current) (tblCustomers, tblItems, tblSuppliers, tblOrders). The easiest and quickest way to import data from Access (any version from Access 2007 to current) into Excel (any version from Excel 2007 to current) is 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”.
  1. Put a heading at the top of the worksheet in cell A1 called “Customers Table”.
  2. Go to the Data Icon Ribbon (see below)
  1. Click on the From Access option in the Get External Data icon area.
  2. In the Get External Data – From Accesswizard popup, browse to find your Assignment 1 Access Database file and select the – then click Open.
  3. In the Select Table wizard popup, select tblCustomers – click OK.
  4. In the Import Data wizard popup, select Table and Existing Worksheet $A$3 as the location to Import the place to put the data.
  5. Select the Table Style you wish to apply to this table.
  • Note: You should consider the colours from a professional perspective.
  1. Left click anywhere on the imported data in worksheet then go to the Design Icon Ribbon and select Convert to Range then click OK.
  • Note: If you use Excel Tables to change the background colours of a worksheet anywhere in your Spreadsheet, you must perform this Convert to Range otherwise marks will be deducted.
  1. Check that the data has correctly been imported correctly into this worksheet.
  2. 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 11 steps for the rest of the Access Database tables naming each worksheet as follows:

Database Table Worksheet Name Worksheet Title (Cell A1)
tblItems ItemsTable Items Table
tblSuppliers SuppliersTable Suppliers Table
tblOrders OrdersTable Orders Table

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.

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 the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations.

The Index Worksheet must use the following layout, modified for this assignment:

  • Note: The Spreadsheet Contents must reflect the ten (10) worksheets developed over Tasks 1 to 21, including the three (3) Range Names developed in Task 6 and six (6) Range Names developed in Task 10.
  • Index Worksheet layout must be developed to a professional level of presentation.
    Use bold, italics, font size, font colours, shading, lines and borders.

Task 4: Data Input Worksheet Template

Add a worksheet labelled DataInput after the Index worksheet from Task 3 (but before the four tables from Task 1) that conforms to the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. Put a heading at the top of the worksheet in cell A1 called Data Input Worksheet and then input the following template in the exact cell shown2 below onto this worksheet.


A B C D
3 Changing Cells:

4
Recommended MarkUp Type
[Insert Markup Type here]
5
Recommended Freight Type
[Insert Freight Type here]
6
Store Recommended MarkUp %
[Insert Store MarkUp % here]
7
Recommended Exchange Rate Type
[Insert Exchange Rate Type here]
8
Exchange Rate (BE to AU as at xx/xx/xx)
[Lookup & Insert BE to AU Exchange Rate here] 3
9
Exchange Rate (CA to AU as at xx/xx/xx)
[Lookup & Insert CA to AU Exchange Rate here]
10



11 Half Year Income:

12
Total Sales
[Insert Cell Reference here]
13



14 Half Year Fixed Expenses:

15
Bank Charges
[Insert Bank Charges Expense here]
16
Electricity Expenses
[Insert Electricity Expense here]
17
Freight Inwards Expenses
[Insert Freight Inwards Expense here]
18
Internet Expenses
[Insert Internet Expense here]
19
Telephone Expenses
[Insert Telephone Expense here]
20
Wages Expenses
[Insert Wages Expense here]
21



22
Total Half Year Fixed Expenses
[Insert Cell Reference here]
23



24 Half Year Variable Expenses:

25
Total Purchases Expenses
[Insert Cell Reference here]
26
Total Freight Outwards Expenses
[Insert Cell Reference here]
27
Percentage Freight Outwards of Total Sales:
[Insert Cell Reference here]
28



29 Total Half Year Fixed & Variable Expenses
[Insert Cell Reference here]
30



31 Total Profit:
[Insert Cell Reference here]
32 Total Discount for Orders:
[Insert Cell Reference here]
33 Percentage Discount of Total Sales:
[Insert Cell Reference here]
34 No. Orders Discount Applied:
[Insert Cell Reference here]
  • Note: This template will be modified with correct number, formula and function in tasks 10 onwards.
  • Data Input Worksheet layout must be developed to a professional level of presentation.
    Use bold, italics, font size, font colours, shading, lines and borders.

Task 5: Calculations Worksheet Template

Add a worksheet labelled Calculations after the DataInput worksheet from Task 3 (but before the four tables from Task 1) that conforms to the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. Put a heading at the top of the worksheet in cell A1 called Calculations Worksheet and then input the following template in the exact cells 4 shown below onto this worksheet.


A B C D
3 Half Year Income

4
Total Sales
[Insert Formula here]
5



6 Half Year Fixed Expenses

7
Total Half Year Fixed Expenses
[Insert Formula here]
8



9 Half Year Variable Expenses

10
Total Purchases Expenses
[Insert Formula here]
11
Total Freight Outward Expenses
[Insert Formula here]
12
Percentage Freight Outwards of Total Sales
[Insert Formula here]
13
 

14 Total Half Year Fixed & Variable Expenses
[Insert Formula here]
15



16 Total Profit
[Insert Formula here]
17



18 Total Discount for Orders
[Insert Formula here]
19 Percentage Discount of Total Sales
[Insert Formula here]
20 No. Orders Discount Applied
[Insert Formula here]
  • Note: This template will be modified with correct number, formula and function in tasks 10 onwards.
  • Calculations Worksheet layout must be developed to a professional level of presentation.
    Use bold, italics, font size, font colours, shading, lines and borders.

Task 6: Name Ranges

On the CustomersTable, ItemsTable and SuppliersTable worksheets set the following Cell Range Names:

  • Cust – on all the data (not headings) in the CustomersTable worksheet
  • Itms – on all the data (not headings) in the ItemsTable worksheet
  • Supp – on all the data (not headings) in the SuppliersTable worksheet
  • Note: You must only create the three (3) name ranges listed in this task, and the six (6) name ranges listed in task 10, any other name ranges used may result in loss of marks.

Task 7: Report Worksheet Headings

Add a worksheet labelled Report after the Calculations worksheet from Task 5 (but before the four tables from Task 1) that conforms to the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations. Put a heading at the top of the worksheet in cell A1 called Report Worksheet and then type the following column headings, starting in cell A3:

  • Customer ID, Title, Family Name, Given Names, Address, City, State, Postcode, Distance (km), Item ID, Type, Description, Size, Freight Weight (Kg), Supplier ID, Supplier Name, Supplier Recommended Markup (%), Order Date, Order Qty, Cost Price (BE), Cost Price (CA), Cost Price (AU), Selling Price, Item Discount, Purchases, Sales, Freight Cost, Order Discount.
  • Note: Modify the headings so that they are in English (e.g. Customer ID not CustID).
  • Report Worksheet layout must be developed to a professional level of presentation.
    Use bold, italics, font size, font colours, shading, lines and borders..

Task 8: Report Worksheet Cell Reference

On the Reportworksheet, Use the Cell References formula, to obtain all 2500 rowsof data from the OrdersTableworksheet for the following.

  • Customer ID, Item ID, Order Date, Order Qty.

(For example, type =OrdersTable!A4 to reference data in cell A4 of the OrdersTable worksheet.)

  • Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column.

Do not discuss with your fellow students, nor post formulas to the StudyDesk Forums; it may result in academic misconduct.

Task 9: Report Worksheet VLOOKUP

On the Reportworksheet use the VLOOKUP function with the Cell Range Names (Task 6), obtain all 2500 rows of data from the CustomersTable, ItemsTable and SuppliersTable worksheets for the following.

See the Forum Postings, Practical Appendixes and the Practical Videos provided in the Assignment 2 Assessment Tab for more details on creating VLOOKUP functions

  • Customers Worksheet:
    • Title, Family Name, Given Names, Address, City, State, Postcode, Freight Distance (km)
  • Items Worksheet:
    • Type, Description, Size, Freight Weight (kg), Supplier ID, Cost Price (BE),

Cost Price (CA)

  • Suppliers Worksheet:
    • Supplier Name, Recommended Markup (%)
  • Note: All VLOOKUP functions must use the Cell Range Names created in Task 6.
  • Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column.

Do not discuss with your fellow students, nor post formulas to the StudyDesk Forums; it may result in academic misconduct.

Task 10: Modify Data Input Worksheet

On the DataInput worksheet perform the following:

  • Create the following six (6) extra Range Names for use in Tasks 10 to 15: Cell Range Names D4 MarkupType D5 FreightType D6 StoreMarkup D7 ExchRateType D8 ExchRateBE D9 ExchRateCA
  • Type Store Markup into the cell D4 containing the phrase: [Insert Markup Type here],
  • Type 97.5% into the cell D6 containing the phrase: [Insert Store MarkUp % here],
  • Type BE into the cell D7 containing the phrase: [Insert Exchange Rate Type here],
  • Type the current Exchange Rate BE to AU into the cell containing the phrase: [Lookup & Insert BE to AU Exchange Rate here] and replace the “as at xx/xx/xx” with the date you looked up the exchange rate.
  • Type the current Exchange Rate CA to AU into the cell containing the phrase: [Lookup & Insert CA to AU Exchange Rate here] and replace the “as at xx/xx/xx” with the date you looked up the exchange rate.
  • Note: You must only create the three name ranges listed in task 6, and the six name ranges listed in this task, any other name ranges used may result in loss of marks.
  • Note: Look up Exchange Rate BE to AU and Exchange Rate CA to AU: www.xe.com
  • Note: Ensure you use the full Exchange Rate as per the XE website, do not truncate to two decimal places.

Task 11: Report Worksheet Cost Price (AU) Nested IF

On the Reportworksheet:

See the Beskeen Excel Chapter 18, the Forum Postings, the Practical Appendixes and the Practical Videos provided in the Assignment 2 Assessment Tab for more details on creating IF functions

  1. Develop an IF function using the new Recommended Exchange Rate Type value (from the DataInput worksheet to calculate the Cost Price (AU) (in the Cost Price (AU) column) using the following criteria:
  • If the Exchange Rate Type is BE then the Cost Price (AU) is calculated by multiplying the Cost Price (BE) by the Exchange Rate (BE to AU) cell on the DataInput worksheet.

Hint:
Cost Price (BE) x Exchange Rate (BE to AU)

  • If the Exchange Rate Type is CA then the Cost Price (AU) is calculated by multiplying the Cost Price (CA) by the Exchange Rate (CA to AU) cell on the DataInput worksheet.

Hint:
Cost Price (CA) x Exchange Rate (CA to AU)

  • Note:Cost Price (AU) IF functions must use the Cell Range Names created in Task 10 (ExchRateType, ExchRateBE & ExchRateCA).
  1. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Chapter 17) into each calculation in your IF function and error checking (Beskeen Excel Chapter 18) to avoid incorrect results due to typing mistakes.
  1. Test the IF function: Once you have completed the Cost Price (AU) IF function, perform the following two tests on it to ensure that it is working correctly:
  • On the DataInput worksheet, type CA into the Exchange Rate Type cell.
  • Go to the Report worksheet and observe whether the Cost Price (AU) have changed.
  • On the DataInput worksheet, type GIGO into the Exchange Rate Type cell.
  • Go to the Report worksheet and observe whether the Cost Price (AU) now displays an error message.
  • On the DataInput worksheet, replace the word GIGO with one of the two valid values for the Exchange Rate Type cell.
  • Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column (Beskeen Excel Chapter 17).
  • Note: Do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above and those needed for rounding, any incorrect or extraneous usage of brackets may result in loss of marks.
  • Note: You must remove any reference to the prefix Report! from these Nested IF functions to reduce complexity and redundancy of code.

Do not discuss with your fellow students, nor post formulas to the StudyDesk Forums; it may result in academic misconduct.

Task 12: Report Worksheet Selling Price Nested IF

On the Reportworksheet:

See the Beskeen Excel Chapter 18, the Forum Postings, the Practical Appendixes and the Practical Videos provided in the Assignment 2 Assessment Tab for more details on creating IF functions

  1. Develop an IF function using the new Recommended MarkUp Type value (from the DataInput worksheet) to calculate the Selling Price (in the Selling Price column) using the following criteria:
  • If the MarkUp Type is Store Markup then the Selling Price is calculated by increasing Cost Price by the Store’s Recommended MarkUp % from the DataInput worksheet

Hint:

Cost Price (AU) + Cost Price (AU) x Store Recommended Markup

  • If the MarkUp Type is Supplier Markup then the Selling Price is calculated by increasing Cost Price by the Supplier’s Recommended MarkUp

Hint:

Cost Price (AU) + Cost Price (AU) x Supplier Recommended Markup

  • Note:Selling Price IF functions must use the Cell Range Names created in Task 10 (MarkupType & StoreMarkup).
  1. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Chapter 17) into each calculation in your IF function and error checking (Beskeen Excel Chapter 18) to avoid incorrect results due to typing mistakes.
  1. Test the IF function: Once you have completed the Selling Price IF function, perform the following two tests on it to ensure that it is working correctly:
  • On the DataInput worksheet, type Supplier Markup into the MarkUp Type cell.
  • Go to the Report worksheet and observe whether the Selling Prices have changed.
  • On the DataInput worksheet, type GIGO into the MarkUp Type cell.
  • Go to the Report worksheet and observe whether the Selling Prices now displays an error message.
  • On the DataInput worksheet, replace the word GIGO with one of the two valid values for the MarkUp Type cell.
  • Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column (Beskeen Excel Chapter 17).
  • Note: Do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above and those needed for rounding, any incorrect or extraneous usage of brackets may result in loss of marks.
  • Note: You must remove any reference to the prefix Report! from these Nested IF functions to reduce complexity and redundancy of code.

Do not discuss with your fellow students, nor post formulas to the StudyDesk Forums; it may result in academic misconduct.

Task 13: Report Worksheet IF / AND: Item Discount

  1. On the Reportworksheet develop an IF / AND function to calculate the amount of Item Discount given to an individual item on an order (in the Item Discount column) using the following criteria:

See the Beskeen Excel Chapter 18, the Forum Postings, the Practical Appendixes and the Practical Videos provided in the Assignment 2 Assessment Tab for more details on creating IF/AND functions

  • If the OrderQty is greater than or equal to three (3) of the same item AND the Selling Price for that item is greater than $100.00 in any single order then the Item Discount is calculated at 7.25% of the Selling Price for that item, otherwise the Item Discount is zero.

Hint:
IF (AND (OrderQty >= 3, Selling Price > $100.00), Selling Price x 7.25%, 0)

  • Note: You must either convert 7.25% to a decimal or include the percentage symbol % in the formula.
  1. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Chapter 17) into the calculation in your IF / AND function.
  1. Test the IF function: Once you have completed the Item Discount IF / AND function and filled it down the column, review the values to ensure that it is working correctly in the following situations:
  1. OrderQty is less than three (3) and Selling Price is less than or equal to $100.00, should result in $0.00 Item Discount.
  2. OrderQty is less than three (3) and Selling Price is greater than $100.00, should result in $0.00 Item Discount.
  3. OrderQty is greater than or equal to three (3) and Selling Price is less than or equal to $100.00, should result in $0.00 Item Discount.
  4. OrderQty is greater than or equal to three (3) and Selling Price is greater than $100.00, should result in an Item Discount.
  • Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column (Beskeen Excel Chapter 17).
  • Note: Do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above and those needed for rounding, any incorrect or extraneous usage of brackets may result in loss of marks.
  • Note: You must remove any reference to the prefix Report! from the IF/AND functions to reduce complexity and redundancy of code.

Do not discuss with your fellow students, nor post formulas to the StudyDesk Forums; it may result in academic misconduct.

Task 14: Report Worksheet Purchases, Sales and Order Discount Formulas

On the Reportworksheet calculate the following three simple formulas:

  1. Purchases by multiplying Order Qty with Cost Price (AU).

Hint:
Cost Price (AU) x Order Qty

  1. Sales by multiplying Order Qty by the difference of Selling Price less the Item Discount amount.

Hint:
(Selling Price – Item Discount) x Order Qty

  1. Order Discount by multiplying Order Qty with Item Discount.

Hint:
Item Discount x Order Qty

  • Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column (Beskeen Excel Chapter 17).
  • Note: Do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above and those needed for rounding, any incorrect or extraneous usage of brackets may result in loss of marks.
  • Note: You must remove any reference to the prefix Report! from these functions to reduce complexity and redundancy of code.

Do not discuss with your fellow students, nor post formulas to the StudyDesk Forums; it may result in academic misconduct.

Task 15: Report Worksheet Freight Cost Nested IF

  1. On the DataInput worksheet, type Oswald Chesterfield Cobblepot Freightinto the cell containing the phrase: [Insert Freight Type here].
  2. On the Reportworksheet develop an IF function using the new Recommended Freight Type value (from the DataInput worksheet to calculate the Freight Cost (in the Freight Cost column) using the following criteria:
  • If the Freight Type is Oswald Chesterfield Cobblepot Freight, then the Freight Cost is calculated at two dollars and ninety-nine cents ($2.99)per kilogram of the item’s freight weight and a quarter of a cent ($0.0025) per kilometre (Distance) if over 250 km (otherwise there is no cost for Distance) per item ordered (Order Qty).

Hint:
($2.99 x Item Weight + IF(Distance > 250 km, $0.0025 x Distance, 0)) x OrderQty

  • If the Freight Type isNorman Osborn Transport, then the Freight Cost is calculated at one dollars and seventy-five cents ($1.75) for the first three-quarters of a kilogram (0.75 kg) and then at two dollars and seventy-five ($2.75) per kilogram of the item’s freight weight over the first three-quarters of a kilograms (0.75 kg) per item ordered (Order Qty).

Hint:
($1.75 + IF(Item Weight > 0.75 kg, (Item Weight – 0.75 kg) x $2.75, 0)) x OrderQty

  • Note:Freight Cost IF functions must use the Cell Range Name created in Task 10 (FreightType).
  1. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Chapter 17) into each calculation in your IF function and error checking (Beskeen Excel Chapter 18) to avoid incorrect results due to typing mistakes.
  2. Test the IF function: Once you have completed the Freight Cost IF function, perform the following three tests on it to ensure that it is working correctly:
  • On the DataInput worksheet, type Norman Osborn Transport into the Freight Type cell.
  • Go to the Report worksheet and observe whether the Freight Costs have changed.
  • On the DataInput worksheet, type GIGO into the Freight Type cell.
  • Go to the Report worksheet and observe whether the Freight Costs now displays an error message.
  • On the DataInput worksheet, replace the word GIGO with one of the two valid values for the Freight Type cell.
  • Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column (Beskeen Excel Chapter 17).
  • Also do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above and those needed for rounding, any incorrect or extraneous usage of brackets may result in loss of marks.
  • You must remove any reference to the prefix Report! from these Nested IF functions to reduce complexity and redundancy of code.

Do not discuss with your fellow students, nor post formulas to the StudyDesk Forums; it may result in academic misconduct.

Task 16: Modify Data Input Worksheet

On the DataInputworksheet:

  1. Replace the phrase “[Insert Bank Charges Expenses here]” in cell D15 with $137.77
  2. Replace the phrase “[Insert Electricity Expenses here]” in cell D16 with $5,675.62
  3. Replace the phrase “[Insert Freight Inwards Expense here] ” in cell D17 with $33,343.99
  4. Replace the phrase “[Insert Internet Expense here] ” in cell D18 with $1,824.06
  5. Replace the phrase “[Insert Telephone Expenses here]” in cell D19 with $4,242.40
  6. Replace the phrase “[Insert Wages Expenses here]” in cell D20 with $54,142.02

Task 17: Calculations Worksheet Formulas

On the Calculationsworksheet calculate the following four simple formula.

  1. Replace the phrase in D4 “[Insert Formula here]” with a function to calculate the Total Sales by adding all the Sales in the Sales column on the Reports worksheet.
  2. Replace the phrase in D7 “[Insert Formula here]” with a function to calculate the Total Half Year Fixed Expenses by adding all the Half Year Fixed Expenses on the DataInput worksheet.
  3. Replace the phrase in D10 “[Insert Formula here]” with a function to calculate the Total Purchases Expenses by adding all the Purchases in the Purchases column on the Reports worksheet.
  4. Replace the phrase in D11 “[Insert Formula here]” with a function to calculate the Total Freight Outward Expenses by adding all the Freight Costs in the Freight Cost column on the Reports worksheet.
  5. Replace the phrase in D12 “[Insert Formula here]” with a formula to calculate the Percentage Freight Outwards of Total Sales by dividing the Total Freight Outward Expenses by the Total Sales.
  • Note: This formula must be formatted as a percentage, do not divide by 100.
  1. Replace the phrase in D14 “[Insert Formula here]” with a formula to calculate the Total Half Year Fixed & Variable Expenses by adding all the Half Year Fixed Expenses and Half Year Variable Expenses on the Calculations worksheet.
  2. Replace the phrase in D16 “[Insert Formula here]” with a formula to calculate the Total Profit by subtracting the Total Half Year Fixed & Variable Expenses from the Total Sales on the Calculations worksheet.
  3. Replace the phrase in D18 “[Insert Formula here]” with a function to calculate the Total Discount for Orders by using the SUMIF() function to add up all cells with an Order Discount greater than 0 in the Order Discount column on the Reports worksheet.
  • Note: For details on SUMIF usage, see Beskeen Excel Chapter 18, the Forum Postings, the Practical Appendixes and the Practical Videos provided in the Assignment 2 Assessment Tab.
  1. Replace the phrase in D19 “[Insert Formula here]” with a function to calculate the Percentage Discount of Total Sales by dividing the Total Discount for Orders by the Total Sales.
  • Note: Thisformula must be formatted as a percentage, do not divide by 100.
  1. Replace the phrase in D20 “[Insert Formula here]” with a function to calculate the Number of Orders where a Discount was Applied by using the COUNTIF() function to count all cells with an Order Discount greater than 0 in the Order Discount column on the Reports worksheet.
  • Note: For details on COUNTIF usage, see Beskeen Excel Chapter 18, the Forum Postings, the Practical Appendixes and the Practical Videos provided in the Assignment 2 Assessment Tab.

Do not discuss with your fellow students, nor post formulas to the StudyDesk Forums; it may result in academic misconduct.

Task 18: Data Input Cell References

On the DataInputworksheet:

  1. Replace the phrase “[Insert Cell Reference Here]” in cell D12 with a cell reference to the Total Sales amount on the Calculations worksheet.
  2. Replace the phrase “[Insert Cell Reference Here]” in cell D22 with a cell reference to the Total Half Year Fixed Expenses amount on the Calculations worksheet.
  3. Replace the phrase “[Insert Cell Reference Here]” in cell D25 with a cell reference to the Total Purchases Expenses amount on the Calculations worksheet.
  4. Replace the phrase “[Insert Cell Reference Here]” in cell D26 with a cell reference to the Total Freight Outwards Expenses amount on the Calculations worksheet.
  5. Replace the phrase “[Insert Cell Reference Here]” in cell D27 with a cell reference to the Percentage Freight Outwards of Total Sales amount on the Calculations worksheet.
  6. Replace the phrase “[Insert Cell Reference Here]” in cell D29 with a cell reference to the Total Half Year Fixed and Variable Expenses amount on the Calculations worksheet.
  7. Replace the phrase “[Insert Cell Reference Here]” in cell D31 with a cell reference to the Total Profit amount on the Calculations worksheet.
  8. Replace the phrase “[Insert Cell Reference Here]” in cell D32 with a cell reference to the Total Discount for Orders amount on the Calculations worksheet.
  9. Replace the phrase “[Insert Cell Reference Here]” in cell D33 with a cell reference to the Percentage Discount of Total Sales amount on the Calculations worksheet.
  10. Replace the phrase “[Insert Cell Reference Here]” in cell D34 with a cell reference to the No. Orders Discount Applied amount on the Calculations worksheet.
  • Note: Ensure that you only use cell referencing for the DataInput Totals data.

Do not discuss with your fellow students, nor post formulas to the StudyDesk Forums; it may result in academic misconduct.

Task 19: Scenarios

Using the Scenario Manager in Excel create and check the following eight (8) Scenarios using the Changing Cells of D4, D5 and D7 on the DataInput worksheet:

See the Beskeen Excel Chapter 19, the Forum Postings, the Practical Appendixes and the Practical Videos provided in the Assignment 2 Assessment Tab for more details on creating Scenarios.

Scenario Markup Type Freight Type Exchange Rate Type
1 Store Markup Oswald Chesterfield Cobblepot Freight BE
2 Store Markup Oswald Chesterfield Cobblepot Freight CA
3 Store Markup Norman Osborn Transport BE
4 Store Markup Norman Osborn Transport CA
5 Supplier Markup Oswald Chesterfield Cobblepot Freight BE
6 Supplier Markup Oswald Chesterfield Cobblepot Freight CA
7 Supplier Markup Norman Osborn Transport BE
8 Supplier Markup Norman Osborn Transport CA
  • Note: Ensure you have a suitable scenario name for each scenario, eg. Scenario 1, Scenario 2, Scenario 3, …

Task 20: Scenario Summary

Using the Scenario Manager in Excel create a Scenario Summary worksheet from the DataInput worksheet based on the eight (8) Scenarios from Task 19 and focusing on the Result cells D12, D22, D25, D26, D27, D29, D31, D32, D33 and D34 on the DataInput worksheet.

See the Beskeen Excel Chapter 19, the Forum Postings, the Practical Appendixes and the Practical Videos provided in the Assignment 2 Assessment Tab for more details on creating Scenarios.

Format your Scenario Summary worksheet to look professional as follows:

  • Modify the heading of the Scenario Summary
  • Remove the Current Value and Notes
  • Label the Changing Cells and Results Cells
  • Format the Colours and Shading to Match the other worksheets

Modify the Scenario Summary as follows:

  • Highlight the cell(s) with the Optimal Total Sales with Yellow Highlight
  • Highlight the cell(s) with the Optimal Total Freight Outwards with Green Highlight
  • Highlight the cell(s) with the Optimal Total Profit with Blue Highlight
  • Highlight the cell(s) with the Minimum Number of Discounts Applied with Red Highlight (with white font colour)
  • Highlight the cell(s) with the Maximum Number of Discounts Applied with Orange Highlight
  • Highlight the Optimal Scenario with a Thick Red Border

Place the Scenario Summary worksheet between the Report and CustomersTable worksheets.

  • Scenario Summary layout must be developed to a professional level of presentation.
    Use bold, italics, font size, font colours, shading, lines and borders.

Do not discuss with your fellow students, nor post Scenario Summary information to the StudyDesk Forums; it may result in academic misconduct.

Task 21: Documentation Worksheet

Add a Documentation worksheet after the four tables from Task 1. Put a heading at the top of the worksheet in cell A1 called Documentation. Ensure that contains all the recommended information for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations.

The Documentation Worksheet must demonstrate the following:

  • A statement outlining the purpose of the Spreadsheet:

Purpose should outline why the Spreadsheet was created including – discussion about the Markup Type, Exchange Rate and Freight Type.

  • A statement about the protection level that should be used on each Worksheet.

Protection should look at all 10 Worksheets and outline whether each sheet should contain protection and why.

DO NOT actually apply protection to your Spreadsheet. This will result in it being unable to be marked.

  • A statement about how the user can access and use the Worksheets.

User Notes should provide a step by step guide for a new user to use the Spreadsheet from changing the values on the DataInput Worksheet to observing the outcome of these changes on both DataInput and Report Worksheets.

  • A statement about what calculations have been used in the development of this Spreadsheet.

Calculations Notes should provide a brief description of each calculation found on the Calculations and Report Worksheets (does not need to discuss individual Cell References and VLOOKUPs – these can be discussed generally as a single calculation type).

  • Documentation Worksheet layout must be developed to a professional level of presentation.
    Use bold, italics, font size, font colours, shading, lines and borders.

Do not discuss with your fellow students, nor post Documentation Worksheet information to the StudyDesk Forums; it may result in academic misconduct.

Assignment 1: Part C (Analytical Essay)

Dr Zatanna Zatara has asked you to develop an analytical essay (between 750 and 1000 words in length) to the client, Stan outlining the major issues that the Decision Support System’s results have highlighted. Create a Word Document (using Word 2007 to current) and name it ‘[lastname]

[initial]

_ [student number] _ [course code] _ [assignment number_(part_c)’
eg. genrichr_0050051005_cis1000_assign1_(part_c).docx).

  • Essay Layout:
    The essay should be using a proportional font (eg. Arial, Times New Roman etc.), with a font size of 11 or 12, and be laid out using 1 ½ line spacing.

    Essays have a particular structure – An introduction, a body (where you write your answer in a number of paragraphs, usually one for each idea or topic) and a conclusion. The conclusion is where you sum up your ‘argument’. Essays normally do not have headings. The information at the following URL may be useful:
    https://www.usq.edu.au/library/study-support/assignments/assignment-structure-and-writing
  • Essay Referencing:
    It is expected that you will use the Baltzan et al and Beskeen et al textbooks to answer the topics Dr Zatanna Zatara has given you for the essay, as well as reputable online sources of information. You need to include a List of References formatted using the Harvard AGPS style on a separate page and include In-text references for any direct or paraphrased quotes used – see the following USQ website for referencing help:
    http://www.usq.edu.au/library/referencing/harvard-agps-referencing-guide

The body of your essay should consider the following issues:

From Part A (Approx. 250 – 350 words):

  • Define what a Database Management System is.
  • Explain why a Database Management System is the appropriate tool for this project.

From Part B (Approx. 500 – 650 words):

  • Define what a Decision Support System is.
  • Explain why a Decision Support System is the appropriate tool for this project.
  • From the results of the Decision Support System results discuss the following (keeping in mind the information provided in the preamble on page 2):
  • Which Mark-up Type would be most appropriate for the business? Why?
    (Optimal Scenario, Optimal Total Sales, and Optimal Total Profit)
  • What would be the impact on the business’s profit if the plan to provide a discount to large orders were implemented? Why?
    (Minimum & Maximum Discounts Applied)
  • Which Recommended Freight Type would be most appropriate for the business? Why?
    (Optimal Scenario, Optimal Total Freight Outward, and Optimal Total Profit)
  • Which country would be most appropriate for the business to import from at the moment? Why?
    (Optimal Scenario, Optimal Total Sales, and Optimal Total Profit)

Submission Guidelines

Attach the three (3) files:

  • Access (any version from Access 2007 to current),
  • Excel (any version from Excel 2007 to current), &
  • Word (any version from Word 2007 to current)

Use the naming convention below, to your online assignment submission in the Assignment 1 area on the CIS5100 StudyDesk before midnight Australian Eastern Standard Time (AEST) on the day the assignment is due.

  1. lastname]
[initial]

_ [student number] _ [course code] _ assign1_(part_a).accdb
eg. genrichr_0050051005_cis5100_assign1_(part_a).accdb

[lastname]

[initial]

_ [student number] _ [course code] _ assign1_(part_b).xlsx
eg. genrichr_0050051005_cis5100_assign1_(part_b).xlsx.

[lastname]

[initial]

_ [student number] _ [course code] _ assign1_(part_c).docx
(eg. genrichr_0050051005_cis5100_assign1_(part_c).docx).

  • Note: Simply changing the File extension to .accdb or .xlsx or .docx on an Access 2003, Excel 2003 or Word 2003 File will not result in a suitable submission. If the assignment Files cannot be opened by the marker, it may be treated as late until a suitable replacement is received..
  • If you have difficulties submitting through the StudyDesk Assignments submission tool, please review the Student Instructions document listed also on the CIS5100 StudyDesk. As a last resort only, email the course leader for instructions on an alternative course of action.
  • You must click on the Submit button to submit the final Assignment for marking, if you do not click on the Submit button, your assignment will not be marked (or may be treated as late).
  • Upon completion of the submission process, check your uMail email account for an automatically generated confirmation email (if you do not have an email account, print out the Submission Complete screen before exiting the Submission System). You must check that the file name and file size are listed correctly – if there is a problem with either, please email the course leader immediately.

Turnitin Process

Your assignment will automatically be sent to Turnitin for checking once you upload the files. You must accept the Turnitin user agreement (you only need to do this once).

Allow up to 24 hours for a Turnitin report to be generated (however, it is unlikely you will need to wait this long). Your Turnitin originality report will be displayed under your “file submission”. If you edit your assignment and resubmit, please allow another 24 hours for the Turnitin originality report to be regenerated.

  • Note: Please only use the Turnitin report for your Essay (Word Document) – Turnitin will be unable to create a report for the Database (Access File) and the Spreadsheet (Excel File) will not be useful. The course team will review each Access and Excel file manually to check for academic integrity issues.
  • Note: The following links have been provided to assist you in understanding the Turnitin Similarity Report Information for the Essay (Word Document).
  • Read the Assignment Submissions using TurnItIn Information:
    USQAssist Assignment Submission Page
    USQAssist Assignment Submission Handout
  • Watch the Assignment Submissions using TurnItIn Video:
    USQAssist Assignment Submission Video
  • Read the Reviewing the TurnItIn Similarity Report Information:
    USQAssist Checking your Assignment using Turnitin-within StudyDesk Handout
  • Watch the Reviewing the TurnItIn Similarity Report Videos:
    Understanding the TurnItIn Similarity Report
    USQAssist Checking your Assignment using Turnitin-within StudyDesk Video

1Percentages must be converted to decimals in Update Queries.

2The row and column details are included above to establish exact cell references only. These are not to be included within your final DataInput worksheet.

3BE is the country code for Belgium (their currency is the Euro) and CA is the country code for Canada (their currency is the Canadian Dollar)

4The row and column details are included above to establish exact cell references only. These are not to be included within your final Calculations worksheet.

The post Database Management System appeared first on My Assignment Online.

Plagiarism Free Assignment Help

Expert Help With This Assignment — On Your Terms

✓ Native UK, USA & Australia writers ✓ Deadline from 3 hours ✓ 100% Plagiarism-Free — Turnitin included ✓ Unlimited free revisions ✓ Free to submit — compare quotes

admin

Academic writer and editor at BestWriters.

Get your paper off your plate tonight

Post your instructions for free. Prices start at $5 per page.

Place an order