Description

This solution contains Excel file with all 21 tasks done for CIS1000 Digital Disruption assessment 2 for Decision Support System (DSS) using Microsoft Excel :

Dr Vivien “Viv” Aldi retired in 2021 after a long and successful career in as a University Lecturer in
the Creative Arts, especially Music and Performance. Driven by her entrepreneurial spirit, she
established her own venture, “Four Seasons Music” to stay active and to allow her to continue her
lifelong passion for music. Four Seasons Music was established to specialise in importing and
selling unique and interesting musical instruments. The business offers a wide range of
instruments including harps, bagpipes, banjos, xylophones, Hawaiian guitars, and mandolins,
delivering to customers across Australia. Initially, the business operated as a small-scale enterprise
leveraging social media platforms to showcase and sell her various musical instruments.
As her business gained momentum and garnered a loyal customer base, Dr Viv Aldi recognised
the need to evolve her operations. Transitioning from digital transactions to a more personalised
approach, she began fulfilling orders via local freight companies, extending her services to
customers across Australia. This strategic shift enhanced customer satisfaction and paved the way
for further growth and expansion. She now runs the business from an industrial warehouse in
Toowoomba.

Task 1: Create and Import
Create a single new spreadsheet and name the file – [lastname][initial]_[course code]_[assignment
number] (e.g., NerksF_cis1000_A2.xlsx).
Import the four-tab delimited text files, which can be found on your firm’s Intranet (CIS1000 Study
Desk → Assessment Tab → Problem Solving Assignment 2), into four separate worksheets using
the following names and titles:
Text File Worksheet Name Worksheet Title (Cell A1)
Assignment02_Customers_TextFile CustomersTable Customers Table
Assignment02_Instruments_TextFile InstrumentsTable Instruments Table
Assignment02_Suppliers_TextFile SuppliersTable Suppliers Table
Assignment02_Orders_TextFile OrdersTable Orders Table
Modify the layout of the data to a professional level of presentation, making sure that the headings
are in English (Customer ID not CustID).
⚠ Note: Mac users may find some minor differences in the steps needed to import the
four text files – please check with the course team for details.
Task 2: Data Validation Check
Check the imported data in the CustomersTable, InstrumentsTable, SuppliersTable and OrdersTable
to ensure that they correctly displaying information, formatting, and data values for use in business.
Task 3: Editing Table Data
Edit Customer ID 150 in the Customers Table to your own details.
• Title, Given Names and Family Name.
• Address, City, State and Postcode.
• Date of Birth to your own Date of Birth (note only the course team and markers will have
CIS1000 – Assessment 2 Page 4 of 17
access to this data).
• Freight Distance using www.whereis.com.au look up the distance between your current
address and Toowoomba QLD 4350 (to the nearest kilometre) (if you live outside of
Australia, please chose an Australian address).
• Mobile Number to your student ID e.g., 0061 099999 (include a space after the fourth
digit). Do not use the u1099999 version.
• Email Address.
• Set Mailing List to be YES.
Task 4: 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 in the Study Materials.
Task 5: Data Input Worksheet Template
Add a worksheet labelled DataInput after the Index worksheet from Task 4 (but before the four tables
from Task 1). Put a heading at the top of the worksheet in cell A1 called Data Input Worksheet. Ensure
that it contains all the recommended data for this worksheet listed in the Practical Appendixes found
in the Study Materials.
⚠ Note: For this Assignment the Exchange Rate XX should be replaced with Exchange
Rate EE and the Exchange Rate YY should be replaced with Exchange Rate FJ
throughout the Data Input Worksheet.
⚠ Note: EE is the country code for Estonia (their currency is the Euro) and FJ is the
country code for Fiji (their currency is the Fijian Dollar – Fijian Dollar (FJD).
Task 6: Calculations Worksheet Template
Add a worksheet labelled Calculations after the Data Input worksheet from Task 5 (but before the four
tables from Task 1). Put a heading at the top of the worksheet in cell A1 called Calculations Worksheet.
Ensure that it contains all the recommended data for this worksheet listed in the Practical Appendixes
found in the Study Materials.
Task 7: Name Ranges
On the Customers Table, Instruments Table and Suppliers Table worksheets set the following Cell
Range Names:
Worksheet Name Range Name
CustomersTable Cust
InstrumentsTable Instr
SuppliersTable Supp
⚠ Note: You must only create the three (3) name ranges listed in this task, and the six (6)
name ranges listed in task 11, any other name ranges used may result in loss of marks.
⚠ Note: Mac users may find some minor differences in the steps needed to create Range
Names – please check with the course team for details.
CIS1000 – Assessment 2 Page 5 of 17
Task 8: Report Worksheet Headings
Add a worksheet labelled Report after the Calculations worksheet from Task 6 (but before the four
tables from Task 1). Put a heading at the top of the worksheet in cell A1 called Report Worksheet. Type
the following column headings on the Report Worksheet, starting in cell A3:
• Customer ID, Title, Family Name, Given Names, Address, City, State, Postcode, Freight Distance
(km), Instrument ID, Classification, Description, Freight Weight (Kg), Supplier ID, Supplier Name,
Recommended Markup (%), Order Date, Order Qty, Cost Price (EE), Cost Price (FJ), Cost Price (AU),
Selling Price, Item Discount, Purchases, Sales, Freight Cost, Order Discount.
Task 9: Report Worksheet Cell Reference
On the Report worksheet, use Cell Reference formulas, to obtain all 2500 rows of data from the Orders
Table worksheet for the following data:
• Customer ID, Item ID, Order Date, Order Qty.
Do not discuss with your fellow students, nor post formulas or functions to the
StudyDesk Forums; it may result in academic misconduct.
Task 10: Report Worksheet VLOOKUP
On the Report worksheet use the VLOOKUP functions with the Cell Range Names (Task 7), obtain all
2500 rows of data from the Customers Table, Instruments Table and Suppliers Table worksheets for
the following.
• Customers Worksheet:
o Title, Family Name, Given Names, Address, City, State, Postcode, Freight
Distance (km)
• Instruments Worksheet:
o Classification, Description, Freight Weight (Kg), Supplier ID, Cost Price (EE), Cost
Price (FJ)
• Suppliers Worksheet:
o Supplier Name, Recommended Markup (%)
⚠ Note: All VLOOKUP functions must use the Cell Range Names created in Task 7.
Do not discuss with your fellow students, nor post formulas or functions to the
StudyDesk Forums; it may result in academic misconduct.
Task 11: Modify Data Input Worksheet
On the Data Input worksheet perform the following:
• Create the following six (6) extra Range Names for use in Tasks 12 to 16:
Cell Range Name
D4 MarkupType
D5 FreightType
D6 StoreMarkup
D7 ExchRateType
CIS1000 – Assessment 2 Page 6 of 17
D8 ExchRateEE
D9 ExchRateFJ
• Type the following data into the specified Data Input worksheet cells for use in Tasks 12 to 16:
o Store Markup into the cell D4 containing the phrase: [Insert Markup Type here].
o Prime Freight Solutions into the cell D5 containing the phrase: [Insert Freight Type here].
o 46.3% into the cell D6 containing the phrase: [Insert Store MarkUp % here].
o EE into the cell D7 containing the phrase: [Insert Exchange Rate Type here].
o The current Exchange Rate EE to AU into the cell containing the phrase: [Lookup & Insert EE
to AU Exchange Rate here] and replace the “as at xx/xx/xx” with the date you looked up the
exchange rate.
o The current Exchange Rate FJ to AU into the cell containing the phrase: [Lookup & Insert FJ
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 7, 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 EE to AU and Exchange Rate FJ 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 12: Report Worksheet Cost Price (AU) Nested IF
On the Report worksheet 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 EE then use the calculation: Cost Price (EE) x Exchange Rate (EE to
AU).
• If the Exchange Rate Type is FJ then use the calculation: Cost Price (FJ) x Exchange Rate (FJ to AU).
• Ensure that you include rounding (to 2 decimal places) into each calculation in your IF function
and error checking.
• 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:
o Test 1:
 On the Data Input worksheet, type FJ into the Exchange Rate Type cell.
 Go to the Report worksheet and observe whether the Cost Price (AU) data has
changed.
o Test 2:
 On the Data Input 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 Data Input worksheet, replace the word GIGO with one of the two valid values for the
Exchange Rate Type cell.
⚠ Note: The Cost Price (AU) IF functions must use the Cell Range Names created in Task 11
(ExchRateType, ExchRateEE & ExchRateFJ).
⚠ Note: You must remove any reference to the prefix Report! from all IF and IF/AND
functions to reduce complexity and redundancy of code.
CIS1000 – Assessment 2 Page 7 of 17
Do not discuss with your fellow students, nor post formulas or functions to the
StudyDesk Forums; it may result in academic misconduct.
Task 13: Report Worksheet Selling Price Nested IF
On the Report worksheet 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 use the calculation: Cost Price (AU) + Cost Price (AU) x
Store Recommended Markup.
• If the MarkUp Type is Supplier Markup then use the calculation: Cost Price (AU) + Cost Price (AU)
x Supplier Recommended Markup.
• Ensure that you include rounding (to 2 decimal places) into each calculation in your IF function
and error checking.
• 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:
o Test 1:
 On the Data Input worksheet, type Supplier Markup into the Markup Type cell.
 Go to the Report worksheet and observe whether the Selling Price data has changed.
o Test 2:
 On the Data Input worksheet, type GIGO into the Markup Type cell.
 Go to the Report worksheet and observe whether the Selling Price now displays an
error message.
• On the Data Input worksheet, replace the word GIGO with one of the two valid values for the
Markup Type cell.
⚠ Note: The Selling Price IF functions must use the Cell Range Names created in Task 11
(MarkupType & StoreMarkup).
⚠ Note: You must remove any reference to the prefix Report! from all IF and IF/AND
functions to reduce complexity and redundancy of code.
Do not discuss with your fellow students, nor post formulas or functions to the
StudyDesk Forums; it may result in academic misconduct.
Task 14: Report Worksheet IF / AND: Item Discount
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 Selling Price for that item is greater than $20.00 AND the Order Qty is greater than or equal
to four (4) of the same Instruments in any single order then the Item Discount is calculated at
6.25% of the Selling Price for that item, otherwise the Item Discount is zero.
• Ensure that you include rounding (to 2 decimal places) into the calculation in your IF / AND
function.
• Test the IF function: Once you have completed the Item Discount IF / AND function, review the
values to ensure that it is working correctly in the following situations:
o Selling Price is less than or equal to $20.00 and Order Qty is less than four (4), should result
in $0.00 Item Discount.
o Selling Price is greater than $20.00 and Order Qty is less than four (4), should result in $0.00
Item Discount.
o Selling Price is less than or equal to $20.00 and Order Qty is greater than or equal four (4),
should result in $0.00 Item Discount.
CIS1000 – Assessment 2 Page 8 of 17
o Selling Price is greater than $20.00 and Order Qty is greater than or equal to four (4), should
result in an Item Discount.
⚠ Note: You must remove any reference to the prefix Report! from all IF and IF/AND
functions to reduce complexity and redundancy of code.
Do not discuss with your fellow students, nor post formulas or functions to the
StudyDesk Forums; it may result in academic misconduct.
Task 15: Report Worksheet Purchases, Sales, and Order Discount Formulas
On the Report worksheet develop the formulas to calculate Purchases, Sales, and Order Discount.
• Purchases – use the calculation: Cost Price (AU) x Order Qty
• Sales – use the calculation: (Selling Price – Item Discount) x Order Qty
• Order Discount – use the calculation: Item Discount x Order Qty
Do not discuss with your fellow students, nor post formulas or functions to the
StudyDesk Forums; it may result in academic misconduct.
Task 16: Report Worksheet Freight Cost Nested IF
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 Prime Freight Solutions, then use the calculation: ($2.5 x Freight Weight +
IF(Freight Distance > 250 km, $0.0025 x Freight Distance, 0)) x OrderQty.
• If the Freight Type is Universal Freight Services then use the calculation: ($1.99 + IF(Freight Weight
> 0.75 kg, (Freight Weight – 0.75 kg) x $2.99, 0)) x OrderQty.
• Ensure that you include rounding (to 2 decimal places) into each calculation in your IF function
and error checking.
• Test the IF function: Once you have completed the Freight Cost IF function, perform the following
two tests on it to ensure that it is working correctly:
o Test 1:
 On the Data Input worksheet, type Universal Freight Services into the Freight Type
cell.
 Go to the Report worksheet and observe whether the Freight Cost data has changed.
o Test 2:
 On the Data Input worksheet, type GIGO into the Freight Type cell.
 Go to the Report worksheet and observe whether the Freight Cost now displays an
error message.
• On the Data Input worksheet, replace the word GIGO with one of the two valid values for the
Freight Type cell.
⚠ Note: The Freight Cost IF functions must use the Cell Range Names created in Task 11
(FreightType).
⚠ Note: You must remove any reference to the prefix Report! from all IF and IF/AND
functions to reduce complexity and redundancy of code.
Do not discuss with your fellow students, nor post formulas or functions to the
StudyDesk Forums; it may result in academic misconduct.
CIS1000 – Assessment 2 Page 9 of 17
Task 17: Modify Data Input Worksheet
On the Data Input worksheet replace the phrases for the following Fixed Expenses for the Second Half
of the Year 2024:
• “[Insert Bank Charges Expenses here]” in cell D15 with $138.99
• “[Insert Electricity Expenses here]” in cell D16 with $5,671.07
• “[Insert Freight Inwards Expense here]” in cell D17 with $41,331.59
• “[Insert Internet Expense here]” in cell D18 with $1,897.02
• “[Insert Telephone Expenses here]” in cell D19 with $4,134.59
• “[Insert Wages Expenses here]” in cell D20 with $55,919.89
Task 18: Calculations Worksheet Formulas
On the Calculations worksheet develop appropriate formulas / functions to calculate the following:
• Total Sales: Replace the phrase in D4 “[Insert Formula or Function here]” with a formula or
• function to calculate the Total Sales.
• Total Half Year Fixed Expenses: Replace the phrase in D7 “[Insert Formula or Function here]” with
a formula or function to calculate the Total Half Year Fixed Expenses.
• Total Purchases Expenses: Replace the phrase in D10 “[Insert Formula or Function here]” with a
• formula or function to calculate the Total Purchases Expenses.
• Total Freight Outward Expenses: Replace the phrase in D11 “[Insert Formula or Function here]”
• with a formula or function to calculate the Total Freight Outward Expenses.
• Percentage Freight Outwards of Total Sales: Replace the phrase in D12 “[Insert Formula or
Function here]” with a formula or function to calculate the Percentage Freight Outwards of Total
Sales.
• Total Half Year Fixed & Variable Expenses: Replace the phrase in D14 “[Insert Formula or Function
here]” with a Formula or function to calculate the Total Half Year Fixed & Variable Expenses.
• Total Profit: Replace the phrase in D16 “[Insert Formula or Function here]” with a Formula or
function to calculate the Total Profit.
• Total Discount for Orders: Replace the phrase in D18 “[Insert Formula or Function here]” with a
SUMIF function to calculate the Total Discount for Orders.
• Percentage Discount of Total Sales: Replace the phrase in D19 “[Insert Formula or Function here]”
with a formula or function to calculate the Percentage Discount of Total Sales.
• Number of Orders where a Discount was Applied: Replace the phrase in D20 “[Insert Formula or
Function here]” with a COUNTIF function to calculate the Number of Orders where a Discount
was Applied.
Do not discuss with your fellow students, nor post formulas or functions to the
StudyDesk Forums; it may result in academic misconduct.
Task 19: Data Input Cell References
On the Data Input worksheet develop cell reference formulas to link the following data from the
Calculations worksheet in preparation for creating the What-If Scenarios in Task 20:
• Total Sales: Replace the phrase “[Insert Cell Reference Here]” in cell D12 with a cell reference.
• Total Half Year Fixed Expenses: Replace the phrase “[Insert Cell Reference Here]” in cell D22 with
a cell reference.
• Total Purchases Expenses: Replace the phrase “[Insert Cell Reference Here]” in cell D25 with a cell
reference.
• Total Freight Outwards Expenses: Replace the phrase “[Insert Cell Reference Here]” in cell D26
with a cell reference.
• Percentage Freight Outwards of Total Sales: Replace the phrase “[Insert Cell Reference Here]” in
cell D27 with a cell reference.
• Total Half Year Fixed and Variable Expenses: Replace the phrase “[Insert Cell Reference Here]” in
CIS1000 – Assessment 2 Page 10 of 17
cell D29 with a cell reference.
• Total Profit: Replace the phrase “[Insert Cell Reference Here]” in cell D31 with a cell reference.
• Total Discount for Orders: Replace the phrase “[Insert Cell Reference Here]” in cell D32 with a cell
reference.
• Percentage Discount of Total Sales: Replace the phrase “[Insert Cell Reference Here]” in cell D33
with a cell reference.
• Number of Orders where a Discount was Applied: Replace the phrase “[Insert Cell Reference
Here]” in cell D34 with a cell reference.
Do not discuss with your fellow students, nor post formulas or functions to the
StudyDesk Forums; it may result in academic misconduct.
Task 20: Scenarios
Using the Scenario Manager tool create and check the following eight (8) Scenarios using the Changing
Cells of D4, D5 and D7 on the Data Input worksheet:
Scenario Markup Type Freight Type Exchange Rate Type
1 Store Markup Prime Freight Solutions EE
2 Store Markup Prime Freight Solutions FJ
3 Store Markup Universal Freight Services EE
4 Store Markup Universal Freight Services FJ
5 Supplier Markup Prime Freight Solutions EE
6 Supplier Markup Prime Freight Solutions FJ
7 Supplier Markup Universal Freight Services EE
8 Supplier Markup Universal Freight Services FJ
Task 21: Scenario Summary Analysis
Using the Scenario Manager tool create a Scenario Summary worksheet based on the eight (8)
Scenarios from Task 20 and focusing on the Result cells D12, D22, D25, D26, D27, D29, D31, D32, D33
and D34 on the Data Input worksheet. Place the Scenario Summary worksheet between the Report
and Customers Table worksheets.
Modify and format your Scenario Summary worksheet to look professional, as discussed in the
Practical Appendixes found in the Study Materials.
Dr. Aria Linton, your supervisor at Valkyries Technologies, has requested that as part of improving the
visualization of the data to assist Dr. Viv Aldi that you highlight the following significant data on the
Scenario Summary worksheet:
• The Optimal Total Sales with Yellow Highlight.
• The Optimal Total Freight Outwards with Green Highlight.
• The Optimal Total Profit with Blue Highlight.
• The Minimum Number of Discounts Applied with Red Highlight (with white font colour).
• The Maximum Number of Discounts Applied with Orange Highlight.
• The Optimal Scenario with a Thick Red Border.
Do not discuss with your fellow students, nor post Scenario Summary information to the
StudyDesk Forums; it may result in academic misconduct.
CIS1000 – Assessment 2 Page 11 of 17
Task 22: 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 Worksheet. Ensure that it contains all the recommended
data for this worksheet listed in the Practical Appendixes found in the Study Materials.
AI usage is permittable ONLY in Documentation Worksheet – its use in any other
worksheet is not permittable.
It should only be used for initial research, planning stages and improving grammar,
clarity, or structure. You must include discussion about how it was used on the
Documentation Worksheet in the AI Use Statement in the Progress Report.
Do not discuss with your fellow students, nor post Documentation Worksheet
information to the StudyDesk Forums; it may result in academic misconduct.
Task 23: Visualisation of the DSS Data
Dr. Aria Linton, your supervisor at Valkyries Technologies, has asked you to review the data produced
in each of the worksheets in your Decision Support System and to identify how it can be visually
displayed in an easy- to-understand format for Dr. Viv Aldi.
To achieve this Dr. Aria Linton has requested that you develop three (3) different types of charts from
the data contained in the Scenario Summary and/or other worksheets.
⚠ You are permitted to create extra data information on one or more of your
worksheets (drawn from the existing worksheet and scenario summary data) to assist
you in the development of one or more of the three charts.
⚠ Please ensure that you clearly indicate that these extra data, formulas, or
functions are “Extra Data for Chart X” and place it slightly separately from the Tasks 1
– 22 data, formulas, and functions (e.g., Report Worksheet Row 2510).
⚠ A rationale for the creation of this extra data, formulas and functions must be
included in your Progress Report Memo.
Each chart must demonstrate legends, data labels, axis titles, axis scales, and trendlines charting
elements, where appropriate, to improve the readability of the visual display.
Place each chart on a new worksheet called Chart1, Chart2, Chart3 immediately after the Scenario
Summary.
Task 24: Progress Report Memo