Assignment 2

Decision Support System (DSS)

Description Marks out of Wtg(%) Due date

ASSIGNMENT 2 100.00 25.00 25th May 2016

This assignment must be your own work. It is acceptable to discuss course content with others to improve your understanding and clarify requirements, but solutions to assignment questions must be done on your own. You must not copy from anyone, including tutors and fellow students, nor allow others to copy your work. All Assignments will be checked using collusion monitoring tools to ensure that each assignment is the original work of the submitting student. Assignments that do not adhere to this requirement will be deemed as being the result of collusion or plagiarism. This may lead to severe academic penalties as outlined in USQ Policy Library: Academic Integrity Policy and Procedure. It is your own responsibility to ensure the integrity of your work. Refer to the USQ Policy Library for more details:

• Academic Integrity Policy

• Academic Integrity Procedure

In completing this assignment, you are expected to use available resources such as the practical activities in the study modules, the Course Study Desk – especially the Discussion Forums (click the Study Desk link on UConnect – http://uconnect.usq.edu.au ), as well as exploring and experimenting on your own.

Applicable course objective:

• demonstrate problem-solving skills by identifying and resolving issues relating to information systems and their components, and proficiently utilise different types of information systems software (especially gaining proficiency in utilising databases, spreadsheets, and presentation applications).

• demonstrate written communication skills by understanding basic information, communication and technology (ICT) terminology for effective communication and applying it within a business environment.

Applicable graduate qualities and skills gained from this assessment instrument:

• Problem Solving (Skill U2)

• Written & Oral Communication (Skill U4)

This assignment is quite complex, and exposes you to many different components in Microsoft Excel 2007/2010/2013, some or all of which might be very unfamiliar to you. The assignment description provides some explanation of how to use these components, but you are also expected to use available resources such as Online Help, the Beskeen et al text, the Practical Skills sections at the end of each Module in the Study Materials, the SAM online tutorial activities and the course discussion forums, as well as exploring and experimenting on your own.

Preamble

Dr Dorothy “Dodo” Little was extremely impressed with our computing firm’s – Kinkajou Technologies – development of the All Creatures Great and Small’s Database Management System (DBMS) utilising Microsoft Access 2007/2010/2013 (Assignment 1). The business has contracted our firm to assist in setting up more 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 2007/2010/2013. The DSS will be used to analyse sales trends for the business to determine future courses of action for the business.

Dr Dorothy “Dodo” Little would like the information in the Database Management System (DBMS) (Assignment 1) imported into Microsoft Excel 2007/2010/2013 so that the information can be summarised as a report and some future analysis of sales trends performed. The suppliers used by the business, source items from either Netherlands (NL) or Cayman Islands (KY). 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 Dorothy “Dodo” Little has noted that a number of the business’s competitors are providing a discount to customers who place large orders. The business would like to see what affect adopting a similar policy would have on the business. The business has also noted a growing number of online businesses are starting to provide free freight delivery as a way of encouraging more online business; the business would like to run a number of scenarios based on potential Mark-up and Freight options to ascertain the best combination for the business if they were to adopt this policy too.

All phases in this project must be developed with professionalism and user-friendliness in mind.

karledwards.com/poster-gallery/, Karl Edwards Studios.

Decision Support System Design

Mrs Hyacinth Macaw, your manager, has set up the following tasks for you to complete for this phase of the project:

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

2. Data Validation Check

3. Create an Index Worksheet

4. Create a Data Input Worksheet Template for later use

5. Create a Calculations Worksheet Template for later use

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

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

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

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

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

11. Modify the Report Worksheet by using a Nested IF to calculate Cost Price (AU)

12. Modify the Report Worksheet by using a Nested IF to calculate Selling Price

13. Modify the Report Worksheet by using a Nested IF to calculate Freight Cost

14. Modify the Report Worksheet by using a AND / IF to calculate Item Discount

15. Modify the Report Worksheet by using simple formulas to find Purchases and Sales, and Modify the Report Worksheet by using a formula to calculate the Order Discount

16. Modify the Data Input Worksheet to include extra data needed for tasks 18 to 22

17. Modify the Calculations Worksheet by using simple formulas

18. Modify the Data Input Worksheet by Cell Referencing all the Calculations data

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

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

21. Create an Documentation Worksheet

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

Task 1: Create and Import

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

Import the following four (4) database tables from your Assignment 1 Microsoft Access 2007/2010/2013 Database File and into Microsoft Excel 2007/2010/2013 (tblCustomers, tblItems, tblSuppliers, tblOrders). The easiest and quickest way to import data from Access 2007/2010/2013 into Excel 2007/2010/2013 is by using the “Import From Access” Wizard. The following steps will assist you with this process:

1) Select the first unused tab at the bottom of the Spreadsheet, right click on it and rename it “CustomersTable”.

2) Put a heading at the top of the worksheet in cell A1 called “Customers Table”.

3) Go to the Data Icon Ribbon (see below)

4) Click on the From Access option in the Get External Data icon area.

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

6) In the Select Table wizard popup, select tblCustomers – click OK.

7) In the Import Data wizard popup, select Table and Existing Worksheet $A$3 as the location to Import the place to put the data.

8) Left click anywhere on the imported data in worksheet then go to the Design Icon Ribbon and select Convert to Range then click OK.

9) Check that the data has correctly been imported correctly into this worksheet.

10) Modify the layout of the data to a professional level of presentation, making sure that the headings are in English (Customer ID not CustID).

Use bold, italics, font size, font colours, shading, lines and borders.

Repeat the above 10 steps for the rest of the Access Database tables naming each worksheet as follows:

Database Table Worksheet Name Worksheet Title (Cell A1)

tblItems ItemsTable Items Table

tblSuppliers SuppliersTable Suppliers Table

tblOrders OrdersTable Orders Table

Task 2: Data Validation Check

Check the imported data in the CustomersTable, ItemsTable, SuppliersTable and OrdersTable to ensure that:

1. The column headings are displayed correctly

2. The content of each column displays in a format that appears valid for that type of data (apply appropriate formatting if required).

3. The content of each column contains complete and accurate data values (eg. Phone numbers are not truncated).

4. The content of each column contains reasonable data values for the use of this business.

If you discover that the data imported in any of your 4 table worksheets contains missing or inaccurate values, please contact the CIS5100 course team immediately. You will be issued with a new copy of the Assignment 1 Access 2007/2010/2013 Database file (containing the 4 Database Tables) to restart Task 1.

Task 3: Index Worksheet

Add a worksheet 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 Appendix 7 of the Study Materials – Spreadsheet design considerations.

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

Task 4: Data Input Worksheet Template

Add a worksheet labelled DataInput after the Index worksheet from Task 2 (but before the four tables from Task 1) that conforms to Appendix 7 of the Study Materials – Spreadsheet design considerations. Put a heading at the top of the worksheet in cell A1 called Data Input Worksheet and then input the following template in the exact cells shown below onto this worksheet:

A B C D

3 Changing Cells:

4 Recommended MarkUp Type [Insert Markup Type here]

5 Recommended Freight Type [Insert Freight Type here]

6 Store Recommended MarkUp % [Insert Standard MarkUp % here]

7 Recommended Exchange Rate Type [Insert Exchange Rate Type here]

8 Exchange Rate (NL to AU) [Insert NL to AU Exchange Rate here]

9 Exchange Rate (KY to AU) [Insert KY 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.

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 Appendix 7 of the Study Materials – Spreadsheet design considerations. Put a heading at the top of the worksheet in cell A1 called Calculations Worksheet and then input the following template in the exact cells shown below onto this worksheet:

A B C D

3 Quarterly Income

4 Total Sales: [Insert Formula here]

5

6 Quarterly Expenses

7 Total Quarterly Fixed Expenses: [Insert Formula here]

8

9 Total Quarterly Variable Expenses

10 Total Purchases Expenses [Insert Formula here]

11 Total Freight Outwards Expenses [Insert Formula here]

12

13 Total Quarterly Variable Expenses: [Insert Formula here]

14

15 Total Profit: [Insert Formula here]

16 Total Discount for Orders: [Insert Formula here]

17 No. Orders Discount Applied: [Insert Formula here]

* This template will be modified with correct number, formula and function in tasks 10 onwards.

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 Appendix 7 of the Study Materials – Spreadsheet design considerations. Put a heading at the top of the worksheet in cell A1 called Report Worksheet and then type the following column headings, starting in cell A3:

• Customer ID, Title, Family Name, Given Names, Address, City, State, Postcode, Distance (km), Item ID, Type, Description, Colour, Size, Freight Weight (Kg), Supplier ID, Supplier Name, Supplier Recommended Markup (%), Order Date, Order Qty, Cost Price (NL), Cost Price (KY), Cost Price (AU), Selling Price, Purchases, Item Discount, Sales, Freight Cost, Order Discount.

Modify the Report worksheets to a professional level of presentation, making sure that the headings are in English (Customer ID not CustID). Use bold, italics, font size, font colours, shading, lines and borders.

Task 8: Report Worksheet Cell Reference

On the Report worksheet, Use the Cell References formula, to obtain all 1000 rows of data from the OrdersTable worksheet for the following:

• Customer ID, Item ID, Order Date, Order Qty.

(For example, type =OrdersTable!A4 to reference data in cell A4 of the OrdersTable worksheet.)

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

Modify the Summary worksheets to a professional level of presentation, making sure that the data is formatted correctly.

Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column.

Task 9: Report Worksheet VLOOKUP

On the Report worksheet use the VLOOKUP function with the Cell Range Names (Task 2), obtain all 1000 rows of data from the CustomersTable, ItemsTable and SuppliersTable worksheets for the following:

• Customers Worksheet:

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

• Items Worksheet:

o Type, Description, Colour, Size, Freight Weight (Kg), Supplier ID, Cost Price (NL),

Cost Price (KY)

• Suppliers Worksheet:

o Supplier Name, Recommended Markup (%)

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

Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column – Beskeen Excel Unit B.

See the Beskeen Excel Unit H and the Videos provided in the Course Content section for more details on creating VLOOKUP functions

Task 10: Modify Data Input Worksheet

On the DataInput worksheet perform the following:

• type Store into the cell containing the phrase: [Insert Markup Type here],

• type 112.5% into the cell containing the phrase: [Insert Standard MarkUp here],

• type NL into the cell containing the phrase: [Insert Exchange Rate Type here],

• type 1.528538 into the cell containing the phrase: [Insert NL to AU Exchange Rate here],

• type 1.700735 into the cell containing the phrase: [Insert KY to AU Exchange Rate here].

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

On the Report worksheet:

1. Develop an IF function using the new Recommended Exchange Rate Type value (from the DataInput worksheet to calculate the Cost Price (AU) (in the Cost Price (AU) column) using the following criteria:

• If the Exchange Rate Type is NL then the Cost Price (AU) is calculated by multiplying the Cost Price (NL) by the Exchange Rate (NL to AU) cell on the DataInput worksheet.

Hint:

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

• If the Exchange Rate Type is KY then the Cost Price (AU) is calculated by multiplying the Cost Price (KY) by the Exchange Rate (KY to AU) cell on the DataInput worksheet.

Hint:

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

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

2. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Unit B) into each calculation in your IF function and error checking (Beskeen Excel Unit E) to avoid incorrect results due to typing mistakes.

Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column – Beskeen Excel Unit B.

Also do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above, any incorrect or extraneous usage of brackets may result in loss of marks.

You must remove any reference to the prefix Report! from these Nested IF functions to reduce complexity and redundancy of code.

See the Beskeen Excel Unit E and the Videos provided in the Course Content section for more details on creating IF functions

3. Test the IF function: Once you have completed the Cost Price (AU) IF function, perform the following two tests on it to ensure that it is working correctly:

• On the DataInput worksheet, type KY into the Exchange Rate Type cell.

• Go to the Report worksheet and observe whether the Cost Price (AU) have changed.

• On the DataInput worksheet, type GIGO into the Exchange Rate Type cell.

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

Task 12: Report Worksheet Selling Price Nested IF

On the Report worksheet:

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

• If the MarkUp Type is Store then the Selling Price is calculated by increasing Cost Price by the Store’s Recommended MarkUp % from the DataInput worksheet

Hint:

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

• If the MarkUp Type is Supplier then the Selling Price is calculated by increasing Cost Price by the Supplier’s Recommended MarkUp

Hint:

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

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

2. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Unit B) into each calculation in your IF function and error checking (Beskeen Excel Unit E) to avoid incorrect results due to typing mistakes.

Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column – Beskeen Excel Unit B.

Also do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above, any incorrect or extraneous usage of brackets may result in loss of marks.

You must remove any reference to the prefix Report! from these Nested IF functions to reduce complexity and redundancy of code.

See the Beskeen Excel Unit E and the Videos provided in the Course Content section for more details on creating IF functions

3. Test the IF function: Once you have completed the Selling Price IF function, perform the following two tests on it to ensure that it is working correctly:

• On the DataInput worksheet, type Supplier into the MarkUp Type cell.

• Go to the Report worksheet and observe whether the Selling Prices have changed.

• On the DataInput worksheet, type GIGO into the MarkUp Type cell.

• Go to the Report worksheet and observe whether the Selling Prices now displays an error message.

Task 13: Report Worksheet Freight Cost Nested IF

1. On the DataInput worksheet, type James Herriot 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 James Herriot Freight then the Freight Cost is calculated a quarter of a cent ($0.0025) per kilometre (Distance) and a seventy-five cents ($0.75) per kilogram of the item’s freight weight per item ordered (Order Qty).

Hint:

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

• If the Freight Type is Siegfried Farnon Transport then the Freight Cost is calculated at a dollar and fifty cents ($1.50) for the first kilograms (1.00 kg) and then at fifty cents ($0.50) per kilogram of the item’s freight weight over one kilograms (1.00 kg) per item ordered (Order Qty).

Hint:

($1.50 + IF(Item Weight 1.00 kg, (Item Weight – 1.00kg) x $0.50, 0)) x OrderQty

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

3. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Unit B) into each calculation in your IF function and error checking (Beskeen Excel Unit E) to avoid incorrect results due to typing mistakes.

Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column – Beskeen Excel Unit B.

Also do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above, any incorrect or extraneous usage of brackets may result in loss of marks.

You must remove any reference to the prefix Report! from these Nested IF functions to reduce complexity and redundancy of code.

See the Beskeen Excel Unit E and the Videos provided in the Course Content section for more details on creating IF functions

4. Test the IF function: Once you have completed the Freight Cost IF function, perform the following three tests on it to ensure that it is working correctly:

• On the DataInput worksheet, type Siegfried Farnon Transport into the Freight Type cell.

• Go to the Report worksheet and observe whether the Freight Costs have changed.

• On the DataInput worksheet, type GIGO into the Freight Type cell.

• Go to the Report worksheet and observe whether the Freight Costs now displays an error message.

Task 14: Report Worksheet IF / AND: Item Discount

1. On the Report worksheet develop an IF / AND function to calculate the amount of Item Discount given to an individual item on an order (in the Item Discount column) using the following criteria:

• If the OrderQty is greater than or equal to five (5) of the same item in any single order AND the Selling Price for that item is greater than $50.00, then the Item Discount is calculated at 7.5% of the Selling Price for that item, otherwise the Item Discount is zero.

Hint:

=IF (AND (OrderQty = 5, Selling Price $50), Selling Price x 7.5%, 0)

2. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Unit B) into each calculation in your IF / AND function.

Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column – Beskeen Excel Unit B.

Also do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above, any incorrect or extraneous usage of brackets may result in loss of marks.

You must remove any reference to the prefix Report! from these IF / AND functions to reduce complexity and redundancy of code.

See the Beskeen Excel Unit E and the Videos provided in the Course Content section for more details on creating IF functions

3. Test the IF function: Once you have completed the 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:

• OrderQty is less than five (5) and Selling Price is less than or equal to $50, should result in $0.00 Item Discount.

• OrderQty is less than five (5) and Selling Price is greater than $50, should result in $0.00 Item Discount.

• OrderQty is greater than or equal to five (5) and Selling Price is less than or equal to $50, should result in $0.00 Item Discount.

• OrderQty is greater than or equal to five (5) and Selling Price is greater than $50, should result in an Item Discount.

Task 15: 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 Unit B.

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

On the DataInput worksheet:

1. Replace the phrase “[Insert Bank Charges Expenses here]” in cell D15 with $2,830.46

2. Replace the phrase “[Insert Electricity Expenses here]” in cell D16 with $4,694.57

3. Replace the phrase “ [Insert Freight Inwards Expense here] ” in cell D17 with $80,721.94

4. Replace the phrase “ [Insert Internet Expense here] ” in cell D18 with $3,503.57

5. Replace the phrase “[Insert Telephone Expenses here]” in cell D19 with $4,882.58

6. Replace the phrase “[Insert Wages Expenses here]” in cell D20 with $44,148.76

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 Unit E.

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 James Herriot Freight NL

2 Store James Herriot Freight KY

3 Store Siegfried Farnon Transport NL

4 Store Siegfried Farnon Transport KY

5 Supplier James Herriot Freight NL

6 Supplier James Herriot Freight KY

7 Supplier Siegfried Farnon Transport NL

8 Supplier Siegfried Farnon Transport KY

Note: Ensure you have a suitable scenario name for each scenario, eg. Scenario 1, Scenario 2 …

See the Beskeen Excel Unit K 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 Unit K 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 Appendix 7 of the Study 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 Hyacinth Macaw has asked you to develop an analytical essay (between 750 and 1000 words in length) to the client, Dr Dorothy “Dodo” Little outlining the major issues that the Decision Support System’s results have highlighted. Create a Word 2007/2010/2013 Document and name it ‘[lastname] [initial] _ [student number] _ [course code] _ [assignment number’

(eg. genrichr_0050051005_CIS5100_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/assignment-skills/writing-assignments/essay-writing

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 discuss the following (keeping in mind the information provided in the preamble on page 2):

1. Which Mark-up Type would be most appropriate for the business? Why?

2. How would the choice of the Mark-up 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 Hyacinth Macaw has given you for the essay, as well as reputable online sources of information. You need to include a brief Bibliography formatted using the Harvard AGPS style on a separate page (see the following USQ website for referencing help: http://www.usq.edu.au/library/referencing/harvard-agps-referencing-guide.

Submission Guidelines

Attach the Excel 2007/2010/2013 & Word 2007/2010/2013 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.

1. [lastname] [initial] _ [student number] _ [course code] _ assign2.xlsx (eg. genrichr_0050051005_CIS5100_assign2.xlsx).

2. [lastname] [initial] _ [student number] _ [course code] _ assign2.docx (eg. genrichr_0050051005_CIS5100_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 CIS5100 StudyDesk. As a last resort only, email the course leader for instructions on an alternative course of action.

Please note that:

• The following is the USQ Assessment – Assignment (Late Submission) and Compassionate and Compelling Circumstances procedure that relate to Extensions and Late Assignments. They can be found under the following links:

? Assessment – Assignment (Late Submission) Procedure: http://policy.usq.edu.au/documents.php?id=14749PL#4.2_Assignments

? Assessment of Compassionate and Compelling Circumstances Procedures: http://policy.usq.edu.au/documents.php?id=131150PL

• Students seeking extensions for any Assignment work must provide appropriate documentation to support their request before the due date of the assignment (see points 4.3 and 4.4 in the Assessment of Compassionate and Compelling Circumstances Procedures above to see what is considered as Compassionate and Compelling reason for an extension and the level of documentation that will be needed).

• 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 Business Day or part Business Day that the Assignment is late. An Assignment submitted more than ten (10) University Business Days after the due date will have a Mark of zero recorded for that Assignment.

? The StudyDesk Assignments submission toolwill accept late assignments up until 23:55pm on the 10th University Business Day after the due date.

Decision Support System (DSS)

Description Marks out of Wtg(%) Due date

ASSIGNMENT 2 100.00 25.00 25th May 2016

This assignment must be your own work. It is acceptable to discuss course content with others to improve your understanding and clarify requirements, but solutions to assignment questions must be done on your own. You must not copy from anyone, including tutors and fellow students, nor allow others to copy your work. All Assignments will be checked using collusion monitoring tools to ensure that each assignment is the original work of the submitting student. Assignments that do not adhere to this requirement will be deemed as being the result of collusion or plagiarism. This may lead to severe academic penalties as outlined in USQ Policy Library: Academic Integrity Policy and Procedure. It is your own responsibility to ensure the integrity of your work. Refer to the USQ Policy Library for more details:

• Academic Integrity Policy

• Academic Integrity Procedure

In completing this assignment, you are expected to use available resources such as the practical activities in the study modules, the Course Study Desk – especially the Discussion Forums (click the Study Desk link on UConnect – http://uconnect.usq.edu.au ), as well as exploring and experimenting on your own.

Applicable course objective:

• demonstrate problem-solving skills by identifying and resolving issues relating to information systems and their components, and proficiently utilise different types of information systems software (especially gaining proficiency in utilising databases, spreadsheets, and presentation applications).

• demonstrate written communication skills by understanding basic information, communication and technology (ICT) terminology for effective communication and applying it within a business environment.

Applicable graduate qualities and skills gained from this assessment instrument:

• Problem Solving (Skill U2)

• Written & Oral Communication (Skill U4)

This assignment is quite complex, and exposes you to many different components in Microsoft Excel 2007/2010/2013, some or all of which might be very unfamiliar to you. The assignment description provides some explanation of how to use these components, but you are also expected to use available resources such as Online Help, the Beskeen et al text, the Practical Skills sections at the end of each Module in the Study Materials, the SAM online tutorial activities and the course discussion forums, as well as exploring and experimenting on your own.

Preamble

Dr Dorothy “Dodo” Little was extremely impressed with our computing firm’s – Kinkajou Technologies – development of the All Creatures Great and Small’s Database Management System (DBMS) utilising Microsoft Access 2007/2010/2013 (Assignment 1). The business has contracted our firm to assist in setting up more 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 2007/2010/2013. The DSS will be used to analyse sales trends for the business to determine future courses of action for the business.

Dr Dorothy “Dodo” Little would like the information in the Database Management System (DBMS) (Assignment 1) imported into Microsoft Excel 2007/2010/2013 so that the information can be summarised as a report and some future analysis of sales trends performed. The suppliers used by the business, source items from either Netherlands (NL) or Cayman Islands (KY). 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 Dorothy “Dodo” Little has noted that a number of the business’s competitors are providing a discount to customers who place large orders. The business would like to see what affect adopting a similar policy would have on the business. The business has also noted a growing number of online businesses are starting to provide free freight delivery as a way of encouraging more online business; the business would like to run a number of scenarios based on potential Mark-up and Freight options to ascertain the best combination for the business if they were to adopt this policy too.

All phases in this project must be developed with professionalism and user-friendliness in mind.

karledwards.com/poster-gallery/, Karl Edwards Studios.

Decision Support System Design

Mrs Hyacinth Macaw, your manager, has set up the following tasks for you to complete for this phase of the project:

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

2. Data Validation Check

3. Create an Index Worksheet

4. Create a Data Input Worksheet Template for later use

5. Create a Calculations Worksheet Template for later use

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

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

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

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

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

11. Modify the Report Worksheet by using a Nested IF to calculate Cost Price (AU)

12. Modify the Report Worksheet by using a Nested IF to calculate Selling Price

13. Modify the Report Worksheet by using a Nested IF to calculate Freight Cost

14. Modify the Report Worksheet by using a AND / IF to calculate Item Discount

15. Modify the Report Worksheet by using simple formulas to find Purchases and Sales, and Modify the Report Worksheet by using a formula to calculate the Order Discount

16. Modify the Data Input Worksheet to include extra data needed for tasks 18 to 22

17. Modify the Calculations Worksheet by using simple formulas

18. Modify the Data Input Worksheet by Cell Referencing all the Calculations data

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

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

21. Create an Documentation Worksheet

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

Task 1: Create and Import

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

Import the following four (4) database tables from your Assignment 1 Microsoft Access 2007/2010/2013 Database File and into Microsoft Excel 2007/2010/2013 (tblCustomers, tblItems, tblSuppliers, tblOrders). The easiest and quickest way to import data from Access 2007/2010/2013 into Excel 2007/2010/2013 is by using the “Import From Access” Wizard. The following steps will assist you with this process:

1) Select the first unused tab at the bottom of the Spreadsheet, right click on it and rename it “CustomersTable”.

2) Put a heading at the top of the worksheet in cell A1 called “Customers Table”.

3) Go to the Data Icon Ribbon (see below)

4) Click on the From Access option in the Get External Data icon area.

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

6) In the Select Table wizard popup, select tblCustomers – click OK.

7) In the Import Data wizard popup, select Table and Existing Worksheet $A$3 as the location to Import the place to put the data.

8) Left click anywhere on the imported data in worksheet then go to the Design Icon Ribbon and select Convert to Range then click OK.

9) Check that the data has correctly been imported correctly into this worksheet.

10) Modify the layout of the data to a professional level of presentation, making sure that the headings are in English (Customer ID not CustID).

Use bold, italics, font size, font colours, shading, lines and borders.

Repeat the above 10 steps for the rest of the Access Database tables naming each worksheet as follows:

Database Table Worksheet Name Worksheet Title (Cell A1)

tblItems ItemsTable Items Table

tblSuppliers SuppliersTable Suppliers Table

tblOrders OrdersTable Orders Table

Task 2: Data Validation Check

Check the imported data in the CustomersTable, ItemsTable, SuppliersTable and OrdersTable to ensure that:

1. The column headings are displayed correctly

2. The content of each column displays in a format that appears valid for that type of data (apply appropriate formatting if required).

3. The content of each column contains complete and accurate data values (eg. Phone numbers are not truncated).

4. The content of each column contains reasonable data values for the use of this business.

If you discover that the data imported in any of your 4 table worksheets contains missing or inaccurate values, please contact the CIS5100 course team immediately. You will be issued with a new copy of the Assignment 1 Access 2007/2010/2013 Database file (containing the 4 Database Tables) to restart Task 1.

Task 3: Index Worksheet

Add a worksheet 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 Appendix 7 of the Study Materials – Spreadsheet design considerations.

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

Task 4: Data Input Worksheet Template

Add a worksheet labelled DataInput after the Index worksheet from Task 2 (but before the four tables from Task 1) that conforms to Appendix 7 of the Study Materials – Spreadsheet design considerations. Put a heading at the top of the worksheet in cell A1 called Data Input Worksheet and then input the following template in the exact cells shown below onto this worksheet:

A B C D

3 Changing Cells:

4 Recommended MarkUp Type [Insert Markup Type here]

5 Recommended Freight Type [Insert Freight Type here]

6 Store Recommended MarkUp % [Insert Standard MarkUp % here]

7 Recommended Exchange Rate Type [Insert Exchange Rate Type here]

8 Exchange Rate (NL to AU) [Insert NL to AU Exchange Rate here]

9 Exchange Rate (KY to AU) [Insert KY 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.

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 Appendix 7 of the Study Materials – Spreadsheet design considerations. Put a heading at the top of the worksheet in cell A1 called Calculations Worksheet and then input the following template in the exact cells shown below onto this worksheet:

A B C D

3 Quarterly Income

4 Total Sales: [Insert Formula here]

5

6 Quarterly Expenses

7 Total Quarterly Fixed Expenses: [Insert Formula here]

8

9 Total Quarterly Variable Expenses

10 Total Purchases Expenses [Insert Formula here]

11 Total Freight Outwards Expenses [Insert Formula here]

12

13 Total Quarterly Variable Expenses: [Insert Formula here]

14

15 Total Profit: [Insert Formula here]

16 Total Discount for Orders: [Insert Formula here]

17 No. Orders Discount Applied: [Insert Formula here]

* This template will be modified with correct number, formula and function in tasks 10 onwards.

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 Appendix 7 of the Study Materials – Spreadsheet design considerations. Put a heading at the top of the worksheet in cell A1 called Report Worksheet and then type the following column headings, starting in cell A3:

• Customer ID, Title, Family Name, Given Names, Address, City, State, Postcode, Distance (km), Item ID, Type, Description, Colour, Size, Freight Weight (Kg), Supplier ID, Supplier Name, Supplier Recommended Markup (%), Order Date, Order Qty, Cost Price (NL), Cost Price (KY), Cost Price (AU), Selling Price, Purchases, Item Discount, Sales, Freight Cost, Order Discount.

Modify the Report worksheets to a professional level of presentation, making sure that the headings are in English (Customer ID not CustID). Use bold, italics, font size, font colours, shading, lines and borders.

Task 8: Report Worksheet Cell Reference

On the Report worksheet, Use the Cell References formula, to obtain all 1000 rows of data from the OrdersTable worksheet for the following:

• Customer ID, Item ID, Order Date, Order Qty.

(For example, type =OrdersTable!A4 to reference data in cell A4 of the OrdersTable worksheet.)

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

Modify the Summary worksheets to a professional level of presentation, making sure that the data is formatted correctly.

Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column.

Task 9: Report Worksheet VLOOKUP

On the Report worksheet use the VLOOKUP function with the Cell Range Names (Task 2), obtain all 1000 rows of data from the CustomersTable, ItemsTable and SuppliersTable worksheets for the following:

• Customers Worksheet:

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

• Items Worksheet:

o Type, Description, Colour, Size, Freight Weight (Kg), Supplier ID, Cost Price (NL),

Cost Price (KY)

• Suppliers Worksheet:

o Supplier Name, Recommended Markup (%)

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

Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column – Beskeen Excel Unit B.

See the Beskeen Excel Unit H and the Videos provided in the Course Content section for more details on creating VLOOKUP functions

Task 10: Modify Data Input Worksheet

On the DataInput worksheet perform the following:

• type Store into the cell containing the phrase: [Insert Markup Type here],

• type 112.5% into the cell containing the phrase: [Insert Standard MarkUp here],

• type NL into the cell containing the phrase: [Insert Exchange Rate Type here],

• type 1.528538 into the cell containing the phrase: [Insert NL to AU Exchange Rate here],

• type 1.700735 into the cell containing the phrase: [Insert KY to AU Exchange Rate here].

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

On the Report worksheet:

1. Develop an IF function using the new Recommended Exchange Rate Type value (from the DataInput worksheet to calculate the Cost Price (AU) (in the Cost Price (AU) column) using the following criteria:

• If the Exchange Rate Type is NL then the Cost Price (AU) is calculated by multiplying the Cost Price (NL) by the Exchange Rate (NL to AU) cell on the DataInput worksheet.

Hint:

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

• If the Exchange Rate Type is KY then the Cost Price (AU) is calculated by multiplying the Cost Price (KY) by the Exchange Rate (KY to AU) cell on the DataInput worksheet.

Hint:

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

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

2. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Unit B) into each calculation in your IF function and error checking (Beskeen Excel Unit E) to avoid incorrect results due to typing mistakes.

Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column – Beskeen Excel Unit B.

Also do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above, any incorrect or extraneous usage of brackets may result in loss of marks.

You must remove any reference to the prefix Report! from these Nested IF functions to reduce complexity and redundancy of code.

See the Beskeen Excel Unit E and the Videos provided in the Course Content section for more details on creating IF functions

3. Test the IF function: Once you have completed the Cost Price (AU) IF function, perform the following two tests on it to ensure that it is working correctly:

• On the DataInput worksheet, type KY into the Exchange Rate Type cell.

• Go to the Report worksheet and observe whether the Cost Price (AU) have changed.

• On the DataInput worksheet, type GIGO into the Exchange Rate Type cell.

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

Task 12: Report Worksheet Selling Price Nested IF

On the Report worksheet:

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

• If the MarkUp Type is Store then the Selling Price is calculated by increasing Cost Price by the Store’s Recommended MarkUp % from the DataInput worksheet

Hint:

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

• If the MarkUp Type is Supplier then the Selling Price is calculated by increasing Cost Price by the Supplier’s Recommended MarkUp

Hint:

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

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

2. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Unit B) into each calculation in your IF function and error checking (Beskeen Excel Unit E) to avoid incorrect results due to typing mistakes.

Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column – Beskeen Excel Unit B.

Also do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above, any incorrect or extraneous usage of brackets may result in loss of marks.

You must remove any reference to the prefix Report! from these Nested IF functions to reduce complexity and redundancy of code.

See the Beskeen Excel Unit E and the Videos provided in the Course Content section for more details on creating IF functions

3. Test the IF function: Once you have completed the Selling Price IF function, perform the following two tests on it to ensure that it is working correctly:

• On the DataInput worksheet, type Supplier into the MarkUp Type cell.

• Go to the Report worksheet and observe whether the Selling Prices have changed.

• On the DataInput worksheet, type GIGO into the MarkUp Type cell.

• Go to the Report worksheet and observe whether the Selling Prices now displays an error message.

Task 13: Report Worksheet Freight Cost Nested IF

1. On the DataInput worksheet, type James Herriot 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 James Herriot Freight then the Freight Cost is calculated a quarter of a cent ($0.0025) per kilometre (Distance) and a seventy-five cents ($0.75) per kilogram of the item’s freight weight per item ordered (Order Qty).

Hint:

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

• If the Freight Type is Siegfried Farnon Transport then the Freight Cost is calculated at a dollar and fifty cents ($1.50) for the first kilograms (1.00 kg) and then at fifty cents ($0.50) per kilogram of the item’s freight weight over one kilograms (1.00 kg) per item ordered (Order Qty).

Hint:

($1.50 + IF(Item Weight 1.00 kg, (Item Weight – 1.00kg) x $0.50, 0)) x OrderQty

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

3. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Unit B) into each calculation in your IF function and error checking (Beskeen Excel Unit E) to avoid incorrect results due to typing mistakes.

Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column – Beskeen Excel Unit B.

Also do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above, any incorrect or extraneous usage of brackets may result in loss of marks.

You must remove any reference to the prefix Report! from these Nested IF functions to reduce complexity and redundancy of code.

See the Beskeen Excel Unit E and the Videos provided in the Course Content section for more details on creating IF functions

4. Test the IF function: Once you have completed the Freight Cost IF function, perform the following three tests on it to ensure that it is working correctly:

• On the DataInput worksheet, type Siegfried Farnon Transport into the Freight Type cell.

• Go to the Report worksheet and observe whether the Freight Costs have changed.

• On the DataInput worksheet, type GIGO into the Freight Type cell.

• Go to the Report worksheet and observe whether the Freight Costs now displays an error message.

Task 14: Report Worksheet IF / AND: Item Discount

1. On the Report worksheet develop an IF / AND function to calculate the amount of Item Discount given to an individual item on an order (in the Item Discount column) using the following criteria:

• If the OrderQty is greater than or equal to five (5) of the same item in any single order AND the Selling Price for that item is greater than $50.00, then the Item Discount is calculated at 7.5% of the Selling Price for that item, otherwise the Item Discount is zero.

Hint:

=IF (AND (OrderQty = 5, Selling Price $50), Selling Price x 7.5%, 0)

2. Ensure that you include rounding (to 2 decimal places – Beskeen Excel Unit B) into each calculation in your IF / AND function.

Note: All formulas must be designed to be typed once at the top of each column and copied down to the remaining cells in the column – Beskeen Excel Unit B.

Also do not include any unit measures such as $, kg or km in the IF function as this will result in a #VALUE error message. Brackets must only be used as indicated in the hints given above, any incorrect or extraneous usage of brackets may result in loss of marks.

You must remove any reference to the prefix Report! from these IF / AND functions to reduce complexity and redundancy of code.

See the Beskeen Excel Unit E and the Videos provided in the Course Content section for more details on creating IF functions

3. Test the IF function: Once you have completed the 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:

• OrderQty is less than five (5) and Selling Price is less than or equal to $50, should result in $0.00 Item Discount.

• OrderQty is less than five (5) and Selling Price is greater than $50, should result in $0.00 Item Discount.

• OrderQty is greater than or equal to five (5) and Selling Price is less than or equal to $50, should result in $0.00 Item Discount.

• OrderQty is greater than or equal to five (5) and Selling Price is greater than $50, should result in an Item Discount.

Task 15: 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 Unit B.

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

On the DataInput worksheet:

1. Replace the phrase “[Insert Bank Charges Expenses here]” in cell D15 with $2,830.46

2. Replace the phrase “[Insert Electricity Expenses here]” in cell D16 with $4,694.57

3. Replace the phrase “ [Insert Freight Inwards Expense here] ” in cell D17 with $80,721.94

4. Replace the phrase “ [Insert Internet Expense here] ” in cell D18 with $3,503.57

5. Replace the phrase “[Insert Telephone Expenses here]” in cell D19 with $4,882.58

6. Replace the phrase “[Insert Wages Expenses here]” in cell D20 with $44,148.76

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 Unit E.

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 James Herriot Freight NL

2 Store James Herriot Freight KY

3 Store Siegfried Farnon Transport NL

4 Store Siegfried Farnon Transport KY

5 Supplier James Herriot Freight NL

6 Supplier James Herriot Freight KY

7 Supplier Siegfried Farnon Transport NL

8 Supplier Siegfried Farnon Transport KY

Note: Ensure you have a suitable scenario name for each scenario, eg. Scenario 1, Scenario 2 …

See the Beskeen Excel Unit K 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 Unit K 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 Appendix 7 of the Study 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 Hyacinth Macaw has asked you to develop an analytical essay (between 750 and 1000 words in length) to the client, Dr Dorothy “Dodo” Little outlining the major issues that the Decision Support System’s results have highlighted. Create a Word 2007/2010/2013 Document and name it ‘[lastname] [initial] _ [student number] _ [course code] _ [assignment number’

(eg. genrichr_0050051005_CIS5100_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/assignment-skills/writing-assignments/essay-writing

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 discuss the following (keeping in mind the information provided in the preamble on page 2):

1. Which Mark-up Type would be most appropriate for the business? Why?

2. How would the choice of the Mark-up 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 Hyacinth Macaw has given you for the essay, as well as reputable online sources of information. You need to include a brief Bibliography formatted using the Harvard AGPS style on a separate page (see the following USQ website for referencing help: http://www.usq.edu.au/library/referencing/harvard-agps-referencing-guide.

Submission Guidelines

Attach the Excel 2007/2010/2013 & Word 2007/2010/2013 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.

1. [lastname] [initial] _ [student number] _ [course code] _ assign2.xlsx (eg. genrichr_0050051005_CIS5100_assign2.xlsx).

2. [lastname] [initial] _ [student number] _ [course code] _ assign2.docx (eg. genrichr_0050051005_CIS5100_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 CIS5100 StudyDesk. As a last resort only, email the course leader for instructions on an alternative course of action.

Please note that:

• The following is the USQ Assessment – Assignment (Late Submission) and Compassionate and Compelling Circumstances procedure that relate to Extensions and Late Assignments. They can be found under the following links:

? Assessment – Assignment (Late Submission) Procedure: http://policy.usq.edu.au/documents.php?id=14749PL#4.2_Assignments

? Assessment of Compassionate and Compelling Circumstances Procedures: http://policy.usq.edu.au/documents.php?id=131150PL

• Students seeking extensions for any Assignment work must provide appropriate documentation to support their request before the due date of the assignment (see points 4.3 and 4.4 in the Assessment of Compassionate and Compelling Circumstances Procedures above to see what is considered as Compassionate and Compelling reason for an extension and the level of documentation that will be needed).

• 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 Business Day or part Business Day that the Assignment is late. An Assignment submitted more than ten (10) University Business Days after the due date will have a Mark of zero recorded for that Assignment.

? The StudyDesk Assignments submission toolwill accept late assignments up until 23:55pm on the 10th University Business Day after the due date.

Hi guys, 1. Your assessment clearly says that the assessment type is a chart/diagram. This is the basic requirement to pass, if you are not including them in your submission it's likely that you will...Assignment This assignment aims at developing a clear understanding of students on different sources of funds used/raised by companies. They will need to identify different sources of fund used by two...Assessment Task 3:Assessment TaskScheduleLearning Outcome AlignmentCompetency Element AlignmentLengthValue (% of 100 overall unit marks)Description and requirementsReport: Change Project PlanTBC4 &...This essay should be write about nonverbal communication and you should choose the concept in the file that I send to you and follow the essay example. It is a 30% essay so please take an extra care! Assessment...Steps that the Australian government has take to minimise breast cancer/colon cancer of indigenous people in Australianeed help with my essay. The question is:In doing a research using secondary data what are the major ethical issues that the research field raises? The research field is : TO WHAT EXTENT CAN THE LIFESTYLE...Table of ContentsTable of Contents 2Assessment Information 3Assessment Instructions 5Student Assessment Agreement 6Assessment Task 1 Cover Sheet 7Assessment Task 1: Written Questions 8Assessment Task 1...**Show All Questions**