Decision Support System Design
Mrs Georgie Handel, 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]’ (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)
3)
Put a heading at the top of the
worksheet in cell A1 called “Customers
Table”.
4)
Go to the Data Icon
Ribbon (see below)
5)
Click on the From Access
option in the Get External
Data icon area.
6)
In the Get
External Data – From Access wizard popup, browse to find your Assignment 1 Access Database file and select the – then
click Open.
7)
In the Select Table wizard
popup, select tblCustomers – click OK.
8)
In the Import Data wizard
popup, select Table and
Existing
Worksheet $A$3 as
the location to Import the place to put the data.
9)
Select the Table Style
you wish to apply to this table. Note:
You should consider the colours from a professional perspective.
10)
Left click anywhere on the imported
data in worksheet then go to the Design Icon
Ribbon and select Convert
to Range
then click OK.
11)
Check that the data has correctly
been imported correctly into this worksheet.
12)
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.
A
copy of the Assignment 1 Access Database file (containing the 4 Database
Tables) will be made available for download after Assignment 1 results are
released to restart Task 1. This can be used if you discover the data imported
in any of your 4 table worksheets contains missing or inaccurate values.
⚠
If you use the copy
of the Assignment 1 Access Database file provided on the Study Desk – you must
change the data for Customer ID 112 to conform to the requirements for Assignment
1 Task 5.
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 cell shown
[1]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 (LU to AU as at
xx/xx/xx)


[Lookup & Insert LU to AU Exchange Rate
here]

9


Exchange Rate (AG to AU as at
xx/xx/xx)


[Lookup & Insert AG 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.[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 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 [3] 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.[4]
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.
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, Colour, Freight Weight (Kg), Supplier ID, Supplier Name, Supplier
Recommended Markup (%), Order Date, Order Qty, Cost Price (LU), Cost Price (AG),
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, Size, Colour, Freight Weight (kg), Supplier ID, Cost Price (LU),
Cost Price (AG)
·
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 Chapter 17.
See 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 25.75% into the cell containing the
phrase: [Insert Store MarkUp here],
·
Type LU into the cell containing the
phrase: [Insert Exchange Rate Type here],
Look up Exchange Rate LU to AU and
Exchange Rate AG to AU: www.xe.com
·
Type the current Exchange Rate LU to AU into
the cell containing the phrase: [Lookup & Insert LU 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 AG to AU into
the cell containing the phrase: [Lookup & Insert AG to AU Exchange Rate
here], and replace the “as at xx/xx/xx”
with the date you looked up the exchange rate.
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 (BE) 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 AG then the
Cost Price (AU) is calculated by multiplying the Cost Price (AG) by the Exchange
Rate (AG to AU) cell on the DataInput
worksheet.
Hint:
Cost Price (AG) x Exchange
Rate (AG 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 Chapter 17) into each
calculation in your IF function and error checking (Beskeen Excel Chapter 18) 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 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, 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 Chapter 18 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 AG 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.
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 Chapter 17) into each
calculation in your IF function and error checking (Beskeen Excel Chapter 18) 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 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, 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 Chapter 18 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.
·
On the DataInput
worksheet, replace the word GIGO with one of
the two valid values for the MarkUp Type
cell.
Task 13: Report Worksheet IF
/ AND: Item Discount
1.
On the Report worksheet
develop an IF / AND function to calculate the amount ofItem Discount
given to an individual item on an order (in the Item Discount column) using the
following criteria:
·
If the Selling
Price for that item is greater than $999.99
AND the OrderQty is greater than or equal to seven
(7) of the same item in any single order then the Item Discount
is calculated at 5.00% of the Selling
Price for that item, otherwise the Item Discount is
zero.
Hint:
=IF (AND (Selling Price > $999.99, OrderQty
>= 7), Selling Price x 5.00%, 0)
Note: You must either convert 5.00% to a decimal or include the
percentage symbol % in the formula.
2. Ensure that you include rounding (to 2 decimal
places – Beskeen Excel Chapter 17) 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
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, 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.
See the Beskeen Excel Chapter 18 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 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 seven (7) and Selling Price
is less than or equal to $999.99, should result in $0.00 Item Discount.
2.
OrderQty
is less than seven (7) and Selling Price
is greater than $999.99, should result in $0.00 Item Discount.
3.
OrderQty
is greater than or equal to seven (7) and Selling Price
is less than or equal to $999.99, should result in $0.00 Item Discount.
4.
OrderQty
is greater than or equal to seven (7) and Selling Price
is greater than $999.99, should result in an Item Discount.
Task 14: Report Worksheet
Purchases, Sales and Order Discount Formulas
On the Report worksheet calculate the
following three simple formulas:
1.
Purchases
by multiplying Order Qty with Cost Price (AU).
2.
Sales
by multiplying Order Qty by the difference of Selling Price less the Item Discount
amount.
Hint:
=(Selling Price – Item Discount) x Order Qty
3.
Order
Discount by multiplying Order Qty with 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.
You must
remove any reference to the prefix Report! from these formulae to reduce complexity and
redundancy of code. 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.
Do not 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 CharlesMarie Widor 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 CharlesMarie
Widor Freight then the
Freight Cost is calculated at a dollar and twentyfive cents ($1.25) per
kilogram of the item’s freight weight and a quarter of a cent ($ XXXXXXXXXXper
kilometre (Distance) if over 1,500 km
(otherwise there is no cost for Distance) per item ordered (Order Qty).
Hint:
($1.25 x Item Weight + IF(Distance > 1,500
km, $0.0025 x Distance, 0)) x OrderQty
·
If the Freight Type is
Antonio Vivaldi Transport then
the Freight Cost is calculated at a dollar seventyfive ($1.75) for the first
two and a half kilograms (2.5 kg) and then at a dollar and twentyfive ($1.25)
per kilogram of the item’s freight weight over two and a half kilograms (2.5
kg) per item ordered (Order Qty).
Hint:
($1.75 + IF(Item Weight > 2.5 kg, (Item Weight –
2.5 kg) x $1.25, 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 Chapter 17) into each
calculation in your IF function and error checking (Beskeen Excel Chapter 18) 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 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, 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 Chapter 18 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 Antonio Vivaldi 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.
Task 16: Modify Data Input
Worksheet
On the DataInput worksheet:
1.
Replace the phrase “[Insert Bank
Charges Expenses here]” in cell D15 with $131.75
2.
Replace the phrase “[Insert Electricity
Expenses here]” in cell D16 with $5,454.55
3.
Replace the phrase “[Insert Freight
Inwards Expense here] ” in cell D17 with $81,248.19
4.
Replace the phrase “[Insert Internet
Expense here] ” in cell D18 with $1,810.18
5.
Replace the phrase “[Insert Telephone
Expenses here]” in cell D19 with $4,051.17
6.
Replace the phrase “[Insert Wages
Expenses here]” in cell D20 with $51,412.86
Task 17: Calculations
Worksheet Formulas
On the Calculations worksheet 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 Quarterly Fixed Expenses by adding all the Quarterly
Fixed Expenses on the DataInput worksheet.
3.
Replace the phrase in D10 “[Insert
Formula here]” with a function
to calculate the Total Purchases 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 by adding all the Freight Costs in the Freight
Cost column on the Reports worksheet.
5.
Replace the phrase in D13 “[Insert
Formula here]” with a formula
to calculate the Total Quarterly Variable Expenses by adding all the Quarterly
Variable Expenses on the Calculations worksheet.
6.
Replace the phrase in D15 “[Insert
Formula here]” with a formula
to calculate the Total Profit by subtracting the Total Quarterly Fixed and
Total Quarterly Variable Expenses from the Total Sales on the Calculations
worksheet.
7.
Replace the phrase in D16 “[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.
8.
Replace the phrase in D17 “[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.
Hint: For
details on SUMIF and COUNTIF usage see Beskeen Excel Chapter 18.
Do not post formulas to the StudyDesk Forums; it may result in academic
misconduct.
Task 18: Data Input Cell
References
On the DataInput worksheet:
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 Quarterly 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 D28 with a cell
reference to the Total Quarterly Variable Expenses amount on the
Calculations worksheet.
6.
Replace the phrase “[Insert Cell
Reference Here]” in cell D30 with a cell
reference to the Total Profit amount on the Calculations worksheet.
7.
Replace the phrase “[Insert Cell
Reference Here]” in cell D31 with a cell
reference to the Total Discount for Orders amount on the Calculations
worksheet.
8.
Replace the phrase “[Insert Cell
Reference Here]” in cell D32 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
Totals data.
Do not post formulas to the StudyDesk Forums; it may result in academic
misconduct.
Modify
the CustomerTable, ItemTable, OrdersTable, SuppliersTable, DataInput,
Calculations and Report worksheets to a professional level of presentation,
making sure that the data is formatted correctly. Use bold, italics, font size,
font colours, shading, lines and borders.
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:
Scenario

Markup Type

Freight Type

Exchange Rate Type

1

Store

CharlesMarie Widor Freight

LU

2

Store

CharlesMarie
Widor Freight

AG

3

Store

Antonio Vivaldi Transport

LU

4

Store

Antonio
Vivaldi Transport

AG

5

Supplier

CharlesMarie Widor Freight

LU

6

Supplier

CharlesMarie Widor Freight

AG

7

Supplier

Antonio Vivaldi Transport

LU

8

Supplier

Antonio Vivaldi Transport

AG

Note: Ensure you have a suitable scenario name for each
scenario, eg. Scenario 1, Scenario 2 …
See the Beskeen
Excel Chapter 19 and the Videos provided in the Course Content section for more
details on creating Scenarios.
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 20 and focusing on the
Result cells D12, D22, D25, D26, D28, D30,
D31 and D32 on the DataInput
worksheet.
Place
the Scenario Summary worksheet between the Calculations
and Report worksheets.
Note: 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
See the Beskeen
Excel Chapter 19 and the Videos provided in the Course Content section for more
details on creating Scenarios.
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).
Modify the layout of the data to a professional
level of presentation. Use bold, italics, font size, font colours, shading,
lines and borders.
Task 22: Analytical Essay
Mrs Georgie Handel has asked you to develop an analytical essay (between 750 and 1000 words in length)
to the client, Dr Wagner 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’
(eg. genrichr_0050051005_cis1000_assign2.docx).
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:
http://www.usq.edu.au/learningcentre/assignmentskills/writingassignments/essaywriting
The body of your essay should consider the following issues:
·
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 (Report Worksheet and Scenario Summary) discuss the following (keeping
in mind the information provided in the preamble on page 2):
1.
Which Markup Type would be most appropriate
for the business? Why?
2.
How would the choice of the Markup Type impact
on the customer’s decision to purchase from the company? Why?
3.
What would be the impact on the business’s
profit if the plan to provide a discount to large orders was implemented?
Why?
4.
Which Recommended Freight Type would be most
appropriate for the business? Why?
5.
What would be the impact that the different
Recommended Freight Types would have on the business’s profit if the cost was
transferred to the business instead of the customer (as discussed in the preamble)?
Why?
6.
Which country would be most appropriate for the
business to import from at the moment? Why?
7.
What issues could cause the business to rethink
its choice of country of import? Why?
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.
Note: It is expected
that you will use the Baltzan et al and Beskeen et al textbooks to answer the
topics Mrs Georgie Handel 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 Intext references for any direct or paraphrased quotes used (see the following USQ website for referencing help: http://www.usq.edu.au/library/referencing/harvardagpsreferencingguide.
Submission Guidelines
Attach the Excel (any version from Excel 2007 to current)
& Word (any version from Word 2007 to current) files using the
naming convention below, to your online assignment submission in the Assignment
1 area on the CIS1000 StudyDesk before midnight
Australian Eastern Standard Time (AEST) on the day the assignment is due.
1.
[lastname]
[initial] _ [student number] _ [course code] _ assign2.xlsx (eg. genrichr_0050051005_cis1000_assign2.xlsx).
2.
[lastname] [initial] _ [student number] _ [course code]
_ assign2.docx
(eg. genrichr_0050051005_cis1000_assign2.docx).
Note: Simply changing the file extension to .xlsx or .docx on an 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.
Upon completion of the submission
process, check your uConnect 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.
If you have difficulties submitting
through the StudyDesk Assignments submission tool, please review the Student Instructions document listed also on the CIS1000 StudyDesk. As a last resort only, email the course
leader for instructions on an alternative course of action.
[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 (their currency is the Euro)
and AG is the country code for Antigua and Barbuda (their currency is the East
Caribbean Dollar)
[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.
Answered Same Day
May 24, 2020
CIS1000
Solution
Shikha answered on
May 29 2020
Index
Index Worksheet
File Name: Assignment.xlsx
Author: Student Name
Date Created: 26th May 2018
Date Last Revised: 28th May 2018
Spreadsheet Contents
DataInput
Calculations
Scenario Summary
Reports
CustomersTable
ItemsTable
OrdersTable
SuppliersTable
Documentation
DataInput
Data Input Worksheet
Changing Cells
Recommended MarkUp Type Supplie
Recommended Freight Type Antonio Vivaldi Transport
Store Recommended MarkUp% $0.26
Recommended Exchange Rate Type &n