Reference no: EM133147088

Assessment Task: Database Management System (DBMS) and Presentation

Decision Support System

Task Overview: Learning Objective:

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

Learning Objective 2: 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:

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. HarryF_64859504_AIS800_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).

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.

Note Task 03
You will be expected to follow the Appendix called "Spreadsheet Design Considerations" closely for Index worksheet, using the same layout and providing the same information.
The Index worksheet should contain all 10 worksheets and the Range Names created in Task 7.
I would recommend that students include the Index worksheet in the spreadsheet as per Task 3 instructions to the best of their ability initially. Your Index worksheet is just that, an index for the spreadsheet. It is placed as the first worksheet and should list not only identification details such as filename and author, but also list every worksheet that is contained within the entire spreadsheet. The worksheets should be listed in the order that are placed within the spreadsheet.
For task 3, you should initially include the worksheet names for those worksheets you have placed in for task 1. You will need to update this Index sheet either progressively as you add in new sheets, or at the end just prior to submission. Only some of the worksheets will have an associated Range Name as per the Appendix called "Spreadsheet Design Considerations" Index example and these Range Names align with Task 7 requirements.
The building of this assignment is progressive, and some aspects of particular assignment tasks do need to be reviewed or updated within later tasks. You will be directed to make changes such as this within the specification for later assignment tasks.
The index worksheet is one of the most important parts of the spreadsheet as it acts as the directory and has a similar function as an index in a book. This worksheet is also very useful in helping to identify the physical features of the spreadsheet and the information included should be such that it benefits the end users and reduces the time it takes to follow through the spreadsheet's workings.
The following is a recommended list of what should be included in the index worksheet:

• file name
• author(s)
• date created
• date last revised
• listing of ALL worksheets and the name ranges developed on each worksheet (if more than 1 range name, separate with a semi-colon)
• the purpose of the spreadsheet (this could be placed in the Documentation worksheet).

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.

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.

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.

Task 6: Name Ranges

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

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: Task 08

Initially the Report worksheet will have data filled for four columns from the OrdersTable - CustID, ItemID, OrderDate and OrderQty. Task 8 requires you to obtain the 2500 cells of data in these four columns by using Cell Referencing.

There are 2500 rows of data within the OrdersTable and the Report worksheet will have the exact same 2500 rows for four of its columns. This means that any changes that are made to the OrdersTable data will be automatically reflected in the Report summary worksheet.

A cell reference is created by typing equals into the cell where you want to display the cell reference, then going to the worksheet and cell that contains the original data and clicking on it, the cell reference formula appears as:

worksheetname!cell

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.

Task 09
Task 9, uses VLOOKUPs to find relevant information for some of the Report columns. The VLOOKUPS retrieve data from CustomersTable, ItemsTable and SuppliersTable. This task outlines which columns of information come from which worksheet.

Each Report row will have the Customer ID for an order. Vlookups are used to find that Customer ID within the CustomersTable (using Cust range) to return the associated Title, Family Name, Given Name etc for that Customer ID. Likewise each Report row will have the Item ID for an order.
VLOOKUP's are used to find that Item ID within the ItemsTable (using Itms range) to return the associated Type, Description etc. Some Report columns will come from the SupplierTable using the Supp range and Supplier ID.
Please see the Appendixes and Practical videos for more details about creating VLOOKUP Functions.

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:
o Title, Family Name, Given Names, Address, City, State, Postcode, Freight Distance (km)
• Items Worksheet:
o Type, Description, Size, Freight Weight (kg), Supplier ID, Cost Price (ME), Cost Price (BZ)
• Suppliers Worksheet:
o Supplier Name, Recommended Markup (%)

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:

Cell Range Names
D4 MarkupType
D5 FreightType
D6 StoreMarkup

D7 ExchRateType
D8 ExchRateME
D9 ExchRateBZ

• Type the following data into the specified Data Input worksheet cells for use in Tasks 12 to 16:
o Store Markup into the cell D4 containing the phrase: [Insert Markup Type here].
o Sheev Palpatine Freight into the cell D5 containing the phrase: [Insert Freight Type here].
o 37.5% into the cell D6 containing the phrase: [Insert Store MarkUp % here].
o ME into the cell D7 containing the phrase: [Insert Exchange Rate Type here].
o 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.
o 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.

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:
o 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.
o Test 2:
• On the Data Input worksheet, type GIGO into the Exchange Rate Type cell.
• Go to the Report worksheet and observe whether the Cost Price (AU) now displays an error message.
• On the Data Input worksheet, replace the word GIGO with one of the two valid values for the Exchange Rate Type cell.

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:

o Test 1:
• On the Data Input worksheet, type Supplier Markup into the Markup Type cell.
• Go to the Report worksheet and observe whether the Selling Price data has changed.

o Test 2:
• On the Data Input worksheet, type GIGO into the Markup Type cell.
• Go to the Report worksheet and observe whether the Selling Price now displays an error message.
• On the Data Input worksheet, replace the word GIGO with one of the two valid values for the Markup Type cell.

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:
o 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.
o Selling Price is greater than $75.00 and Order Qty is less than three (3), should result in
$0.00 Item Discount.
o 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.

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

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

o Test 2:

changed.

• 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.

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.

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.

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.

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.

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

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

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

2. How would the choice of the Mark-up Type affect 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 were implemented? Why?
(Minimum & Maximum Discounts Applied)

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

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?
(Optimal Scenario, Optimal Total Sales, and Optimal Total Profit)

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

Attachment:- Database Management System.rar