Task overview

Due Date

Assignment Due Date: 25 May 2022

Learning Objective

This assessment relates to the following:

Learning Objective 3: apply information systems skills to develop practical solutions to real-world business problems using database management and decision support systems.

Learning Objective 4: communicate practical solutions to issues dealing with implementation of appropriate information systems to both technical and non-technical audiences within a business context.

Task Description

Preamble

http://starwars.ea.com/starwars/battlefront/news/start-of-your-star-wars-adventure,
DiscoverSG, 2017 Is The Year Of Star Wars.

Dr Dan Tooine was extremely impressed with our computing firm’s – First Order Technologies – development of Kashyyyk Collectables’ 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. The DSS will analyse sales trends for the business to determine future courses of action for the business.

Dr Dan Tooine would like the information in the Database Management System (DBMS) (Assignment 1) imported into Microsoft Excel 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 Montenegro (ME) or Belize (BZ). 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 Dan Tooine has noted that several the business’s competitors are providing a discount to customers who place large orders. The business would like to see what affect adopting a similar policy would have on the business. The business has also noted a growing number of online businesses are starting to provide free freight delivery as a way of encouraging more online business; the business would like to run a number of scenarios based on potential Mark-up and Freight options to ascertain the best combination for the business if they were to adopt this policy too.

What you need to do

Task 1: Create and Import

Create a single new spreadsheet and name the file – [lastname] [initial] _ [student number] _ [course code] _ [assignment number] (eg. NerksF_0050051005_CIS5100_assign1.xlsx).

Import the four Database Tables developed in Assignment 1 (tblCustomers, tblItems, tblSuppliers, tblOrders), into four separate worksheets using the following naming and titles:

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, ItemsTable, SuppliersTable and OrdersTable to ensure that they correctly displaying information, formatting, and data values for use in business.

Note: A copy of the Assignment 1 Access Database file (containing the four (4) Database Tables) will be made available for download after Assignment 1 results are released to allow students to restart Task 1 – this can be used if you discover the data imported in any of your four (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 in the Study Materials.

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

Note: The Spreadsheet Contents must reflect the ten (10) worksheets developed over Tasks 1 to 20, including the three (3) Range Names developed in Task 6 and six (6) Range Names developed in Task 10.

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

Task 4: Data Input Worksheet Template

Add a worksheet labelled DataInput after the Index worksheet from Task 3 (but before the four tables from Task 1). 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: This template will be modified with correct number, formula and function in tasks 9 onwards.

Note: For this Assignment the Exchange Rate XX should be replaced with Exchange Rate ME and the Exchange Rate YY should be replaced with Exchange Rate BZ throughout the Data Input Worksheet.

Note: ME is the country code for Montenegro (their currency is the Euro) and BZ is the country code for Belize (their currency is the Belize dollar – BZD).

Task 5: Calculations Worksheet Template

Add a worksheet labelled Calculations after the Data Input 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 Calculations Worksheet. Ensure that it contains all the recommended data for this worksheet listed in the Practical Appendixes found in the Study Materials.

Input the following template in the exact cells ¹ shown below onto this worksheet.

Note: This template will be modified with correct number, formula and function in tasks 9 onwards.

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

Task 6: Name Ranges

On the Customers Table, Items Table and Suppliers Table worksheets set the following Cell Range Names:

Note: You must only create the three (3) name ranges listed in this task, and the six (6) name ranges listed in task 10, any other name ranges used may result in loss of marks.

Note: Mac users may find some minor differences in the steps needed to create Range Names – please check with the course team for details.

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). Put a heading at the top of the worksheet in cell A1 called Report Worksheet. Ensure that it contains all the recommended data for this worksheet listed in the Practical Appendixes found in the Study Materials. 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), Item ID, Type, Description, Size, Freight Weight (Kg), Supplier ID, Supplier Name, Recommended Markup (%), Order Date, Order Qty, Cost Price (ME), Cost Price (BZ), Cost Price (AU), Selling Price, Item Discount, Purchases, Sales, Freight Cost, Order Discount.

Note: Modify the headings so that they are in English (e.g. Customer ID not CustID).

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

Task 8: Report Worksheet Cell Reference

On the 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 9: Report Worksheet VLOOKUP

On the Report worksheet use the VLOOKUP functions with the Cell Range Names (Task 6), obtain all 2500 rows of data from the Customers Table, Items Table and Suppliers Table worksheets for the following.

Customers Worksheet:

Title, Family Name, Given Names, Address, City, State, Postcode, Freight Distance (km)

Items Worksheet:

Type, Description, Size, Freight Weight (kg), Supplier ID, Cost Price (ME), Cost Price (BZ)

Suppliers Worksheet:

Supplier Name, Recommended Markup (%)

Note: All VLOOKUP functions must use the Cell Range Names created in Task 6.

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

Task 10: Modify Data Input Worksheet

On the Data Input worksheet perform the following:

Create the following six (6) extra Range Names for use in Tasks 11 to 15:

Type the following data into the specified Data Input worksheet cells for use in Tasks 12 to 16:

Store Markup into the cell D4 containing the phrase: [Insert Markup Type here].

Sheev Palpatine Freight into the cell D5 containing the phrase: [Insert Freight Type here].

37.5% into the cell D6 containing the phrase: [Insert Store MarkUp % here].

ME into the cell D7 containing the phrase: [Insert Exchange Rate Type here].

The current Exchange Rate ME to AU into the cell containing the phrase: [Lookup & Insert ME to AU Exchange Rate here] and replace the “as at xx/xx/xx” with the date you looked up the exchange rate.

The current Exchange Rate BZ to AU into the cell containing the phrase: [Lookup & Insert BZ to AU Exchange Rate here] and replace the “as at xx/xx/xx” with the date you looked up the exchange rate.

Note: You must only create the three name ranges listed in task 6, and the six name ranges listed in this task, any other name ranges used may result in loss of marks.

Note: Look up Exchange Rate ME to AU and Exchange Rate BZ to AU: www.xe.com.

Note: Ensure you use the full Exchange Rate as per the XE website, do not truncate to two decimal places.

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

On the 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 ME then use the calculation: Cost Price (ME) x Exchange Rate (ME to AU).

If the Exchange Rate Type is BZ then use the calculation: Cost Price (BZ) x Exchange Rate (BZ 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:

Test 1:

On the Data Input worksheet, type BZ into the Exchange Rate Type cell.

Go to the Report worksheet and observe whether the Cost Price (AU) data has changed.

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, ExchRateME & ExchRateBZ).

Note: You must remove any reference to the prefix Report! from the 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 12: 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:

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.

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 the 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 13: 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 $75.00 AND the Order Qty is greater than or equal to three (3) of the same item in any single order then the Item Discount is calculated at 8.125% 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:

Selling Price is less than or equal to $75.00 and Order Qty is less than three (3), should result in $0.00 Item Discount.

Selling Price is greater than $75.00 and Order Qty is less than three (3), should result in $0.00 Item Discount.

Selling Price is less than or equal to $75.00 and Order Qty is greater than or equal to three (3), should result in $0.00 Item Discount.

Selling Price is greater than $75.00 and Order Qty is greater than or equal to three (3), should result in an Item Discount.

Note: You must remove any reference to the prefix Report! from the 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 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 15: 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 Sheev Palpatine Freight then use the calculation: ($2.99 x Freight Weight + IF(Freight Distance > 750 km, $0.004 x Freight Distance, 0)) x OrderQty.

If the Freight Type is Wedge Antilles Transport then use the calculation: ($2.50 + IF(Freight Weight > 1 kg, (Freight Weight – 1 kg) x $2.75, 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:

Test 1:

On the Data Input worksheet, type Wedge Antilles Transport into the Freight Type cell.

Go to the Report worksheet and observe whether the Freight Cost data has changed.

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 the 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 16: Modify Data Input Worksheet

On the Data Input worksheet replace the phrases for the following Fixed Expenses for the First Half of the Year 2021:

“[Insert Bank Charges Expenses here]” in cell D15 with $145.82

“[Insert Electricity Expenses here]” in cell D16 with $7,965.71

“[Insert Freight Inwards Expense here] ” in cell D17 with $31,432.41

“[Insert Internet Expense here] ” in cell D18 with $2,614.83

“[Insert Telephone Expenses here]” in cell D19 with $2,639.49

“[Insert Wages Expenses here]” in cell D20 with $33,617.10

Task 17: 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 “[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 18: 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 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 19: 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:

Task 20: 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 Skye Walker, your supervisor at First Order Technologies, has requested that as part of improving the visualization of the data to assist Dr Dan Tooine 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.

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 it contains all the recommended data for this worksheet listed in the Practical Appendixes found in the Study Materials.

The Documentation Worksheet must demonstrate the Builders Notes, User Notes and Calculation Notes Sections as discussed in the Spreadsheet Design Considerations Appendix:

The following is an example of the Builders Notes section:

Builder Notes:

This [Type of Information System] was developed by [Developer(s) Name] on [Date Last Modified] using [Name and version of Software].

File Name: [Filename] version [Version No.].

Purpose:

[What is the purpose of developing this Information System] and 

[Who was the Information Systems developed for].

Structure: This [Type of Information System] makes use of [insert number] worksheets, including: [List ALL worksheets used in the project].

Worksheet Protection: It is suggested that the following worksheets in this project should be protected or partially protected from end-user modification:

[Insert list of worksheets to be fully protected and explain why]

[Insert list of worksheets to be partially protected, what sections specific section(s), and explain why]

[Insert list of worksheets that do not need to be protected and explain why]

The following is an example of the User Notes section:

User Notes should provide a brief guide for a new user to use the Information System from changing the values on the Data Input Worksheet to observing the outcome of these changes on both Data Input and Report Worksheets.

User Notes:

This [Type of Information System] has been developed to allow an end-user to input data [list data sources] and [list changing cells] it will then perform a number of calculations on the data (see Calculations Section) using formulas and functions, and will result in the output of the following information [list information produced].

The following is an example of the Calculations Notes section:

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

Calculation Notes:

This [Type of Information System] has been developed using [Name and version of Software] formulas and functions on the following worksheets:

[Provide a brief statement of each Formulas and Functions used on each worksheet].

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

Task 22: Analytical Essay

Dr Skye Walker, your supervisor at First Order Technologies, has asked you to develop an analytical essay (between 750 and 1000 words in length) to the client, Dr Dan Tooine 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. NerksF_0050051005_cis5100_assign2.docx).

Ensure that the Analytical Essay is developed, structured as a essay, for the correct audience.

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

Essays have a particular structure – An introduction, a body (where you write your answer in a number of paragraphs, usually one for each idea or topic) and a conclusion. The conclusion is where you sum up your ‘argument’. Essays normally do not have headings. The information at the following URL may be useful: https://www.usq.edu.au/library/study-support/assignments/assignment-structure-and-writing

Essay Referencing:
It is expected that you will use the Baltzan et al and Beskeen et al textbooks to answer the topics Dr Lisa Chimes has given you for the essay, as well as reputable online sources of information. You need to include a List of References formatted using the Harvard AGPS style on a separate page and include In-text references for any direct or paraphrased quotes used – see the following USQ website for referencing help:
http://www.usq.edu.au/library/referencing/harvard-agps-referencing-guide

Dr Skye Walker has asked you to discuss the following issues in a non-technical language to the client, Dr Dan Tooine:

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):

Which Mark-up Type would be most appropriate for the business? Why?
(Optimal Scenario, Optimal Total Sales, and Optimal Total Profit)

How would the choice of the Mark-up Type affect the customer’s decision to purchase from the company? Why?

What would be the impact on the business’s profit if the plan to provide a discount to large orders were implemented? Why?
(Minimum & Maximum Discounts Applied)

Which Recommended Freight Type would be most appropriate for the business? Why?
(Optimal Scenario, Optimal Total Freight Outward, and Optimal Total Profit)

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?

Which country would be most appropriate for the business to import from at the moment? Why?
(Optimal Scenario, Optimal Total Sales, and Optimal Total Profit)

What issues could cause the business to rethink its choice of country of import? Why?

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

Task snapshot

30%

100

Individual

Summative

Rubric

Submission information

This assignment must be all your own work. It is acceptable to discuss course content with others to improve your understanding and clarify requirements, but solutions to assignment questions must be done on your own.

You must not copy from anyone, including tutors and fellow students, nor allow others to copy your work.

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

Decision Support System: [lastname] [initial] _ [student number] _ [course code] _ [assignment number].xlsx (eg. NerksF_0050051005_CIS5100_assign1.xlsx).

Progress Report Memo: [lastname] [initial] _ [student number] _ [course code] _ [assignment number].docx (eg. NerksF_0050051005_CIS5100_assign1.docx).

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 contact AskUSQ in the first instance. As a last resort only, email the course leader for instructions on an alternative course of action.

It is expected that all assessment tasks will be submitted/completed by the published due date. However, as per USQ Assessment Procedure, requests for an extension of due date can be made prior to the due date for the assessment. Extensions prior to the due date for all three assignments must be applied for (and will be granted) as per the expectations of the relevant USQ Policies and Procedures. Be sure to have appropriate documentation as you will need to upload this to support your request. Extensions can be applied for through the following link: Apply for Extension to USQ Assessment Item.

An Assignment submitted after the due date without an approved extension of time will be penalised. The penalty for late submission is a reduction by five percent (5%) of the maximum mark applicable for the assignment, for each University Calendar Day or part Calendar Day that the assignment is late. An assignment submitted more than seven (7) University Calendar Days after the due date will have a mark of zero recorded for that Assignment.

All staff who are assessing your work meet to discuss and compare their judgements before marks or grades are finalised.

ASSIGNMENT 2 RUBRIC

Student Name: Click here to enter text.

Date:

Student ID: Click here to enter text.

Weighting: 30%

Mark: Click here to enter text.

/ 100

MARKING SUMMARY

Spreadsheet Creation and Table Import

/ 2

Data Validation

/ 3

Index Worksheet

/ 3

Data Input Worksheet

/ 5

Calculations Worksheet

/ 10

Report Worksheet – Cell References

/ 2

Tables Worksheets – Range Names

/ 2

Report Worksheet – VLOOKUP Functions

/ 5

Report Worksheet – IF Functions (Cost Price AU)

/ 5

Report Worksheet – IF Functions (Selling Price)

/ 10

Report Worksheet – IF/AND Function (Discount)

/ 5

Report Worksheet – Purchases, Sales and Order Discount Formulas

/ 3

Report Worksheet – IF Functions (Freight Cost)

/ 10

Scenario Manager Development

/ 5

Scenario Manager Summary Output

/ 5

Documentation Worksheet

/ 10

Overall Professional Presentation of All Worksheets

/ 5

Scenario Manager Analysis Essay

/ 10

Final Mark

0

/ 100

ASSIGNMENT TASK 1

Demonstrate Problem Solving Skills by creating an Excel Spreadsheet and Importing Data from Assignment 1 Access Database into four Table Worksheets as per Task 1.

SPREADSHEET CREATION AND TABLE IMPORT

Logical development of four Table Worksheets as per instructions.

ASSIGNMENT TASK 2

Check the imported data in the CustomersTable, ItemsTable, SuppliersTable and OrdersTable as per Task 2.

TABLES WORKSHEETS – VALIDATION CHECK

Demonstrate knowledge of data validation by checking the imported data in the CustomersTable, ItemsTable, SuppliersTable and OrdersTable.

ASSIGNMENT TASK 3

Develop the Index Worksheet as per Task 3.

INDEX WORKSHEET

Demonstrate written communication skills by creating an Index Worksheet; ensuring 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.

ASSIGNMENT TASK 4, 10, 16 & 18

Develop the Data Input Worksheet as per Tasks 4, 10, 16 & 18.

DATA INPUT WORKSHEET

Demonstrate Problem Solving Skills by developing a Data Input worksheet; ensuring that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations.

ASSIGNMENT TASK 5 & 17

Develop the Calculations Worksheet as per Tasks 5 and 17.

CALCULATIONS WORKSHEET

Demonstrate Problem Solving Skills by developing a Calculations worksheet, ensuring that it contains functions to calculate Total Purchases, Total Freight, Total Discount for Orders and Total Sales. Formulas to calculate Total Fixed and Variable Expenses, Total Profit and Number of Orders where a Discount was Applied, and the Percentages of Freight Outwards and Discount to Total Sales. Ensuring that they conform to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations.

ASSIGNMENT TASKS 6, 7, 8, 9, 11, 12, 13, 14 & 15

Develop the Report Worksheet, including all formulas and functions as per Tasks 6, 7, 8, 9, 11, 12, 13, 14 and 15.

REPORT WORKSHEET – CELL REFERENCES

Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains cell references of CustID, ItemID, OrderDate and OrderQty for all rows of data on the Report worksheet with data drawn from the OrdersTable worksheets. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations.

TABLES WORKSHEETS – RANGE NAMES

Demonstrate Problem Solving Skills by developing the required three (3) Cell Range Names for task 6 on the CustomersTable, ItemsTable and SuppliersTable worksheets, and and six (6) Cell Range Names for task 10 on the DataInput worksheet.

REPORT WORKSHEET – VLOOKUP FUNCTIONS

Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains VLOOKUP functions using Cell Range Names, for all rows of data on the Report worksheet with data drawn from the CustomersTable, ItemsTable and SuppliersTable worksheets. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations.

REPORT WORKSHEET – IF FUNCTIONS (COST PRICE (AU))

Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains Nested IF functions to determine the Cost Price (AU) based on the Exchange Rate Type in cell D7 of the Data Input Worksheet. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations.

REPORT WORKSHEET – IF FUNCTIONS (SELLING PRICE)

Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains Nested IF functions to determine the Selling Price based on the MarkUp Type in cell D4 of the Data Input Worksheet. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations.

REPORT WORKSHEET – IF/AND FUNCTIONS (DISCOUNT)

Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains IF/AND functions to determine the Discount amount based on the Order Qty and the Selling Price. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations.

REPORT WORKSHEET – PURCHASES, SALES AND ORDER DISCOUNT FORMULAS

Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains three (3) simple formulas to calculate the Purchases, Sales and Order Discount Formulas. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations.

REPORT WORKSHEET – IF FUNCTIONS (FREIGHT COST)

Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains Nested IF functions to determine the Freight Cost based on the Freight Type in cell D5 of the Data Input Worksheet. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials – Spreadsheet design considerations.

ASSIGNMENT TASK 19 & 20

Develop the Scenario Summary as per Tasks 19 and 20.

SCENARIO MANAGER DEVELOPMENT

Demonstrate Problem Solving Skills by developing the eight (8) Scenarios as outlined in the assignment based on the changing cells on the Input Data worksheet.

SCENARIO MANAGER SUMMARY OUTPUT

Demonstrate Problem Solving Skills by developing a Scenario Summary for the eight (8) Scenarios outline in the assignment based on the results cells found on the eight (8) results cells from the Report worksheet. Ensuring that the output is user friendly and professionally presented.

ASSIGNMENT TASK 21

Develop the Documentation Worksheet, including all formulas and functions as per Task 21.

DOCUMENTATION WORKSHEET

Demonstrate written communication skills by creating a Documentation worksheet; ensuring 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.

ASSIGNMENT TASKS 1 to 21

Demonstrate communication skills by modifying all worksheets to a professional level of presentation.

OVERALL PROFESSIONAL PRESENTATION OF ALL WORKSHEETS

Demonstrate communication skills by modifying all worksheets to a professional level of presentation, making sure that the headings are in English, and that the data is formatted correctly. Use of bold, italics, font size, font colours, shading, lines and borders is essential to give the worksheets a professional look and feel.

ASSIGNMENT TASK 22

Develop an Analytical Essay based on the findings from the Scenario Summary as per Task 22.

SCENARIO MANAGER ANALYTICAL ESSAY:

Demonstrate written communication skills by creating a well-structured analytical essay based on the findings from the Scenario Summary outlining the major issues that it has highlighted to the business.