CIS1000 Assignment 2: DSS Design & Analysis for Einstein Technologies

This assignment requires the creation of a Decision Support System (DSS) using Microsoft Excel. The project involves importing data from a database, designing worksheets for data input, calculations, and reporting. The student is tasked with creating various components, including data validation, index, data input and calculation templates, and a report worksheet. They need to use formulas, functions (VLOOKUP, Nested IF, AND/IF), and scenario analysis to analyze sales trends, calculate costs, and determine optimal business strategies. The assignment emphasizes professional presentation, user-friendliness, and the application of spreadsheet design principles. The student must also prepare an analytical essay describing the findings and insights gained from the developed DSS, focusing on sales trends and the impact of discount and freight policies on the business. The project aims to provide a comprehensive understanding of DSS design and its practical application in a business context.

Assignment 2
Decision Support System (DSS)
Description Marks out of Wtg(%) Due date
ASSIGNMENT 2 100.00 25.00 12th October 2016
This assignment must be 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
In completing this assignment, you are expected to use available resources such as the
practical activities in the study modules, the Course Study Desk – especially the Discussion
Forums (click the Study Desk link on UConnect – <http://uconnect.usq.edu.au>), as well as
exploring and experimenting on your own.
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
(any version from Excel 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
CIS1000 Assignment 2 Question Page 1

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 Nikola Tesla was 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)
(Assignment 1) 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 Tesla has noted that a number of 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.
All phases in this project must be developed with professionalism and user-friendliness in mind.
www.steampunktribune.com, Karl Edwards Studios.
CIS1000 Assignment 2 Question Page 2

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
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]’ (eg.
genrichr_0050051005_cis1000_assign2.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”.
2) Put a heading at the top of the worksheet in cell A1 called “Customers Table”.
CIS1000 Assignment 2 Question Page 3

3) Go to the Data Icon Ribbon (see below)
4) Click on the From Access option in the Get External Data icon area.
5) In the Get External Data – From Access wizard popup, browse to find your Assignment 1
Access Database file and select the – then click Open.
6) In the Select Table wizard popup, select tblCustomers – click OK.
7) In the Import Data wizard popup, select Table and 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 Design Icon 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:
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.
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 Database file (containing the 4 Database Tables) to restart
Task 1.
CIS1000 Assignment 2 Question Page 4

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.
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: Data Input Worksheet Template
Add a worksheet labelled DataInput after the Index worksheet from Task 2 (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 cells shown
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 Standard MarkUp % here]
7 Recommended Exchange Rate Type [Insert Exchange Rate Type here]
8 Exchange Rate (LU to AU) [Insert LU to AU Exchange Rate here]
9 Exchange Rate (TV to AU) [Insert TV to AU Exchange Rate here]
10
11 Quarterly Income:
12 Total Sales [Insert Cell Reference here]
13
14 Quarterly 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 Quarterly Fixed Expenses [Insert Cell Reference here]
23
24 Quarterly Variable Expenses:
25 Total Purchases Expenses [Insert Cell Reference here]
26 Total Freight Outwards Expenses [Insert Cell Reference here]
27
28 Total Quarterly Variable Expenses [Insert Cell Reference here]
29
30 Total Profit: [Insert Cell Reference here]
31 Total Discount for Orders: [Insert Cell Reference here]
32 No. Orders Discount Applied: [Insert Cell Reference here]
* This template will be modified with correct number, formula and function in tasks 10 onwards.1 2
1 The row and column details are included above to establish exact cell references only. These are not to be included within your final
DataInput worksheet.
2 LU is the country code for Luxembourg; TV is the country code for Tuvalu.
CIS1000 Assignment 2 Question Page 5

Modify the layout of the data 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 4 (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
shown below onto this worksheet:
A B C D
3 Quarterly Income
4 Total Sales: [Insert Formula here]
5
6 Quarterly Expenses
7 Total Quarterly Fixed Expenses: [Insert Formula here]
8
9 Total Quarterly Variable Expenses
10 Total Purchases Expenses [Insert Formula here]
11 Total Freight Outwards Expenses [Insert Formula here]
12
13 Total Quarterly Variable Expenses: [Insert Formula here]
14
15 Total Profit: [Insert Formula here]
16 Total Discount for Orders: [Insert Formula here]
17 No. Orders Discount Applied: [Insert Formula here]
* This template will be modified with correct number, formula and function in tasks 10 onwards.3
Modify the layout of the data 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 name ranges listed in this task, any other name ranges used may
result in loss of marks.
3 The row and column details are included above to establish exact cell references only. These are not to be included within your final
Calculations worksheet.
CIS1000 Assignment 2 Question Page 6

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, Colour, Size, Freight Weight (Kg), Supplier ID, Supplier
Name, Supplier Recommended Markup (%), Order Date, Order Qty, Cost Price (LU), Cost
Price (TV), Cost Price (AU), Selling Price, Purchases, Item Discount, Sales, Freight Cost,
Order Discount.
Modify the Report worksheets 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.
Task 8: Report Worksheet Cell Reference
On the Report worksheet, Use the Cell References formula, to obtain all 1000 rows of data from the
OrdersTable worksheet 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.)
Do not post formulas to the StudyDesk Forums; it may result in academic misconduct.
Modify the Summary worksheets to a professional level of presentation, making sure that the data is
formatted correctly.
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.
Task 9: Report Worksheet VLOOKUP
On the Report worksheet use the VLOOKUP function with the Cell Range Names (Task 2), obtain
all 1000 rows of data from the CustomersTable, ItemsTable and SuppliersTable worksheets for the
following:
 Customers Worksheet:
o Title, Family Name, Given Names, Address, City, State, Postcode, Freight Distance
(km)
 Items Worksheet:
o Type, Description, Colour, Size, Freight Weight (kg), Supplier ID, Cost Price (LU),
Cost Price (TV)
 Suppliers Worksheet:
CIS1000 Assignment 2 Question Page 7

o Supplier Name, Recommended Markup (%)
Do not post formulas to the StudyDesk Forums; it may result in academic misconduct.
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 Unit B.
See the Beskeen Excel Unit H and the Videos provided in the Course Content section for
more details on creating VLOOKUP functions
Task 10: Modify Data Input Worksheet
On the DataInput worksheet perform the following:
 type Store into the cell containing the phrase: [Insert Markup Type here],
 type 62.5% into the cell containing the phrase: [Insert Standard MarkUp here],
 type LU into the cell containing the phrase: [Insert Exchange Rate Type here],
 type 1.49066 into the cell containing the phrase: [Insert LU to AU Exchange Rate here],
 type 0.95887 into the cell containing the phrase: [Insert TV to AU Exchange Rate here].
Task 11: Report Worksheet Cost Price (AU) Nested IF
On the Report worksheet:
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 LU then the Cost Price (AU) is calculated by multiplying
the Cost Price (LU) by the Exchange Rate (LU to AU) cell on the DataInput worksheet.
Hint:
Cost Price (LU) x Exchange Rate (LU to AU)
 If the Exchange Rate Type is TV then the Cost Price (AU) is calculated by multiplying
the Cost Price (TV) by the Exchange Rate (TV to AU) cell on the DataInput worksheet.
Hint:
Cost Price (TV) x Exchange Rate (TV to AU)
Do not post formulas to the StudyDesk Forums; it may result in academic misconduct.
2. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Unit B) into each
calculation in your IF function and error checking (Beskeen Excel Unit E) to avoid incorrect
results due to typing mistakes.
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 Unit B.
CIS1000 Assignment 2 Question Page 8

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, 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.
See the Beskeen Excel Unit E and the Videos provided in the Course Content section for
more details on creating IF functions
3. 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 TV 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.
Task 12: Report Worksheet Selling Price Nested IF
On the Report worksheet:
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 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 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
Do not post formulas to the StudyDesk Forums; it may result in academic misconduct.
2. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Unit B) into each
calculation in your IF function and error checking (Beskeen Excel Unit E) to avoid incorrect
results due to typing mistakes.
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 Unit B.
CIS1000 Assignment 2 Question Page 9

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, 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.
See the Beskeen Excel Unit E and the Videos provided in the Course Content section for
more details on creating IF functions
3. 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 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.
Task 13: Report Worksheet Freight Cost Nested IF
1. On the DataInput worksheet, type Johannes Kepler Freight into the cell containing the
phrase: [Insert Freight Type here].
2. On the Report worksheet 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 Johannes Kepler Freight then the Freight Cost is calculated at
seventy-five cents ($0.75) per kilogram of the item’s freight weight a half of a cent
($0.005) per kilometre (Distance) over 500 km and per item ordered (Order Qty).
Hint:
($0.75 x Item Weight + IF(Distance > 500 km, $0.005 x Distance, 0)) x OrderQty
 If the Freight Type is Nicolaus Copernicus Transport then the Freight Cost is calculated
at a seventy-five cents ($0.75) for the first two and a half kilograms (2.50 kg) and then at
a dollar ($1.00) per kilogram of the item’s freight weight over two and a half kilograms
(2.50 kg) per item ordered (Order Qty).
Hint:
($0.75 + IF(Item Weight > 2.50 kg, (Item Weight –2.50kg) x $1.00, 0)) x OrderQty
Do not post formulas to the StudyDesk Forums; it may result in academic misconduct.
3. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Unit B) into each
calculation in your IF function and error checking (Beskeen Excel Unit E) to avoid incorrect
results due to typing mistakes.
CIS1000 Assignment 2 Question Page 10

CIS1000 Assignment 2 Question Page 11

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 Unit B.
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, 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.
See the Beskeen Excel Unit E and the Videos provided in the Course Content section for
more details on creating IF functions
4. 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 Nicolaus Copernicus 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.
Task 14: Report Worksheet IF / AND: Item Discount
1. On the Report worksheet 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:
 If the OrderQty is greater than or equal to seven (7) of the same item in any single order
AND the Selling Price for that item is greater than $30.00, then the Item Discount is
calculated at 5% of the Selling Price for that item, otherwise the Item Discount is zero.
Hint:
=IF (AND (OrderQty >= 7, Selling Price > $30), Selling Price x 5%, 0)
2. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Unit B) into each
calculation in your IF / AND function.
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 Unit B.
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, any incorrect or extraneous usage of brackets may result in loss of marks.
You must remove any reference to the prefix Report! from these IF / AND functions to reduce
complexity and redundancy of code.
CIS1000 Assignment 2 Question Page 12