demonstrate written communication skills by understanding basic information

 Assignment 2

Decision Support System (DSS)

Description	Marks out of	Wtg(%)	Due date
ASSIGNMENT 2	100.00	25.00	14th October 2015

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 2007/2010/2013, some or all of which
might be very unfamiliar to you. The assignment description provides some
explanation of how to use these components, but you are also expected to use
available resources such as Online Help, the Beskeen et al text, the Practical
Skills sections at the end of each Module in the Study Materials, the SAM
online tutorial activities and the course discussion forums, as well as
exploring and experimenting on your own.

Preamble

Dr Vivien “Viv” Aldi was extremely impressed with our computing firm’s
–Valkyries
Technologies –
development of theFour Seasons Music’s Database Management System (DBMS) utilising Microsoft Access
2007/2010/2013 (Assignment 1). The business has contracted our firm to assist
in setting up more ofthe 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 2007/2010/2013. The DSS will be used to analyse sales trends
for the business to determine future courses of action for the business.

Dr Vivien “Viv” Aldi would like the information
in the Database Management System (DBMS) (Assignment 1) imported into Microsoft
Excel 2007/2010/2013 so that the information can be summarised as a report and
some future analysis of sales trends performed. The suppliers used by the
business, source items from either Austria (AT) orSaint Lucia(LC).
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 Vivien “Viv” Aldi 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.

Anne Vallayer-Coster,
Attributes of Music, 1770.

Decision Support System Design

Mr Peter Tchaikovsky, your manager, has set up
the following tasks for you to complete for this phase of the project:

1.
Create a
Spreadsheet and import the four (4) Access 2007/2010/2013 Tables into four (4)
Worksheets

2.
Data
Validation Check

3.
Create
an Index Worksheet

4.
Create a
Data Input Worksheet Template for later use

5.
Create a
Calculations Worksheet Template for later use

6.
Create
Name Ranges for the Customers, Items, and Suppliers data

7.
Create a
Report Worksheet and set up the column headings

8.
Modify
the Report Worksheet by Cell Referencing all the Orders Table data

9.
Modify
the Report Worksheet by using VLOOKUP to get Customer, Item and Supplier data

10. Modify the Data Input Worksheet to include
extra data needed for tasks 11 to 14

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 an Documentation Worksheet

20. Create eight (8) Scenarios on the Data Input
Worksheet

21. Create a Scenario Summary of the eight (8)
Scenarios

22. Create an Analytical Essay to describe the
findings made using the Spreadsheet

Task 1: Create and Import

Open a single
newExcel 2007/2010/2013 spreadsheet and name the file – ‘[lastname] [initial] _
[student number] _ [course code] _ [assignment number]’ (eg. genrichr_0050051005_cis5100_assign2.xlsx).

Import the following four (4) database tables from your Assignment
1 Microsoft Access 2007/2010/2013 Database File and into Microsoft Excel
2007/2010/2013 (tblCustomers, tblItems, tblSuppliers, tblOrders). The easiest
and quickest way to import data from Access 2007/2010/2013 into Excel
2007/2010/2013 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”.

3)
Go to theDataIcon Ribbon (see
below)

4)
Click on theFrom Access option in theGet External Dataicon area.

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

6)
In theSelect Tablewizard popup, select
tblCustomers – click OK.

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

8)
Left click anywhere on the imported data in worksheet then go to theDesignIcon Ribbon and selectConvert 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 CIS5100 course team immediately. You will be issued
with a new copy of the Assignment 1 Access 2007/2010/2013 Database file
(containing the 4 Database Tables) to restart Task 1.

Task 3: Index Worksheet

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

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

Task 4: Data Input Worksheet
Template

Add a worksheet labelledDataInput after the Index worksheet from Task 2 (but before the four tables
from Task 1) that conforms to Appendix 7 of the Study Materials – Spreadsheet
design considerations. Put a heading at
the top of the worksheet in cell A1 called Data
Input Worksheetand 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 (AT to AU)		[Insert AT to AU Exchange Rate here]
9		Exchange Rate (LC to AU)		[Insert LC 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		TotalQuarterly 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		TotalQuarterly 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]

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 labelledCalculations after the DataInput worksheet from Task 4 (but before the four
tables from Task 1) that conforms to Appendix 7 of the Study Materials –
Spreadsheet design considerations. Put a
heading at the top of the worksheet in cell A1 called Calculations Worksheetand 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 theCustomersTable,ItemsTable andSuppliersTable 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.

Task 7: Report Worksheet
Headings

Add a worksheet labelledReport after the Calculations worksheet from Task 5 (but before the four
tables from Task 1) that conforms to Appendix 7 of the Study Materials –
Spreadsheet design considerations. Put a
heading at the top of the worksheet in cell A1 called Report Worksheetand 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, Classification, Instrument, Freight
Weight (Kg), Supplier ID, Supplier Name, Supplier Recommended Markup (%), Order
Date, Order Qty, Cost Price (AT), Cost Price (LC), 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 theReportworksheet, Use the Cell References formula, to obtain all 1000 rowsof data from theOrdersTableworksheet 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 theReportworksheet use the VLOOKUP function with theCell
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
Classification, Instrument, Freight Weight (Kg), Supplier ID, Cost
Price (AT), Cost Price (LC)

·
Suppliers Worksheet:

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 theDataInput worksheet perform the following:

·
typeStore into the cell
containing the phrase: [Insert Markup Type here],

·
type212.5% into the cell
containing the phrase: [Insert Standard MarkUp here],

·
typeAT into the cell
containing the phrase: [Insert Exchange Rate Type here],

·
type1.46538 into the cell containing the phrase: [Insert AT to
AU Exchange Rate here],

·
type1.146797into the cell
containing the phrase: [Insert LC to AU Exchange Rate here].

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

On theReportworksheet:

1.
Develop an IF
function using the newRecommendedExchange 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 isAT then the Cost Price
(AU) is calculated by multiplying the Cost Price (AT) by the Exchange Rate (AT
to AU) cell on theDataInput worksheet.

Hint:

Cost Price (AT) x Exchange Rate (AT to AU)

·
If the Exchange Rate Type isLC then the Cost Price
(AU) is calculated by multiplying the Cost Price (LC) by the Exchange Rate (LC
to AU) cell on theDataInput worksheet.

Hint:

Cost Price (LC) x Exchange Rate (LC to AU)

Do not post formulas to the StudyDesk
Forums; it may result in academic misconduct.

2.
Ensure that you includerounding
(to
2 decimal places – Beskeen Excel Unit B) into each calculation in your IF
function anderror 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.

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 theDataInput worksheet, typeLC into the Exchange Rate Type cell.

·
Go to theReport worksheet and
observe whether the Cost Price (AU) have changed.

·
On theDataInput worksheet, typeGIGO into the Exchange Rate Type cell.

·
Go to theReport worksheet and
observe whether the Cost Price (AU) now displays an error message.

Task 12: Report Worksheet
Selling Price Nested IF

On theReportworksheet:

1.
Develop an IF
function using the newRecommendedMarkUp Type value (from the DataInput worksheet) to calculate the Selling
Price (in the Selling Price column) using the following criteria:

·
If the MarkUp Type isStore then the Selling Price is
calculated by increasing Cost Price by the Store’s Recommended MarkUp % from
theDataInput worksheet

Hint:

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

·
If the MarkUp Type isSupplier 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 includerounding(to 2 decimal places – Beskeen
Excel Unit B) into each calculation in your IF function anderror
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.

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 theDataInput worksheet, typeSupplier into the MarkUp Type cell.

·
Go to theReport worksheet and
observe whether the Selling Prices have changed.

·
On theDataInput worksheet, typeGIGO into the MarkUp Type cell.

·
Go to theReport worksheet and
observe whether the Selling Prices now displays an error message.

Task 13: Report Worksheet
Freight Cost Nested IF

1.
On theDataInput worksheet, typeStradivari Freightinto the cell containing the phrase: [Insert
Freight Type here].

2.
On theReportworksheet develop an IF
function using the newRecommendedFreight Type value (from the DataInput worksheet to calculate the Freight Cost
(in the Freight Cost column) using the following criteria:

·
If the Freight Type isStradivari Freight then the Freight Cost is calculated a quarter of a cent ($0.0025)
per kilometre (Distance) and three dollars seventy-five cents ($3.75) per
kilogram of the item’s freight weight per item ordered (Order Qty).

Hint:

($0.0025 x Distance + $3.75 x Item Weight) x
OrderQty

·
If the Freight Type is Guarneri Transportthen the Freight Cost