Assignment Requirements:
Learning Objectives:
Applicable course objective:
A) Apply information systems concepts to identify and resolve problems within a business context
B) Apply information systems skills to develop practical solutions within a business context
C) Communicate information systems concepts to both technical and non-technical audiences within a business context.
Applicable graduate qualities and skills gained from this assessment instrument:
- Problem Solving
- Communications
Preamble:
Dr John Smith (The Doctor to his friends) was extremely impressed with our computing firm's - Clara Osbourne Technologies - development of the Whovian Pandorica's Database Management System (DBMS) utilizing 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) utilizing Microsoft Excel (any version from Excel 2007 to current). The DSS will analyze sales trends for the business to determine future courses of action for the business.
The Doctor would like the information in the Database Management System (DBMS) (Assignment 1) imported into Microsoft Excel (any version from Excel 2007 to current) so that the information can be summarized as a report and some future analysis of sales trends performed. The suppliers used by the business, source items from either Ireland (IE) or New Zealand (NZ). They allow the business to select from either of these two regions on an ad hoc basic depending on the most favorable exchange rate at the time.
The Doctor 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.
Decision Support System Design:
Dr Tara Dis, your manager, has set up the following tasks for you to complete for this phase of the project:
1. Create a Spreadsheet and import the four (4) Access Tables into four (4) Worksheets.
2. Data Validation Check.
3. Create an Index Worksheet.
4. Create a Data Input Worksheet Template for later use.
5. Create a Calculations Worksheet Template for later use.
6. Create Name Ranges for the Customers, Items, and Suppliers data.
7. Create a Report Worksheet and set up the column headings.
8. Modify the Report Worksheet by Cell Referencing all the Orders Table data.
9. Modify the Report Worksheet by using VLOOKUP to get Customer, Item and Supplier data.
10. Modify the Data Input Worksheet to include extra data needed for later tasks.
11. Modify the Report Worksheet by using a Nested IF to calculate Cost Price (AU).
12. Modify the Report Worksheet by using a Nested IF to calculate Selling Price.
13. Modify the Report Worksheet by using a Nested IF to calculate Freight Cost.
14. Modify the Report Worksheet by using a AND / IF to calculate Item Discount.
15. Modify the Report Worksheet by using simple formulas to find Purchases and Sales, and Modify the Report Worksheet by using a formula to calculate the Order Discount.
16. Modify the Data Input Worksheet to include extra data needed for later tasks.
17. Modify the Calculations Worksheet by using simple formulas.
18. Modify the Data Input Worksheet by Cell Referencing all the Calculations data.
19. Create eight (8) Scenarios on the Data Input Worksheet.
20. Create a Scenario Summary of the eight (8) Scenarios.
21. Create a Documentation Worksheet.
22. Create an Analytical Essay to describe the findings made using the Spreadsheet.
Task 1: Create and Import
Open a single new Excel (any version from Excel 2007 to current) spreadsheet and name the file - '[lastname] [initial] _ [student number] _ [course code] _ [assignment number]' (eg. genrichr_0050051005_cis1000_assign2.xlsx).
Import the following four (4) database tables from your Assignment 1 Microsoft Access (any version from Access 2007 to current) Database File and into Microsoft Excel (any version from Excel 2007 to current) (tblCustomers, tblItems, tblSuppliers, tblOrders). The easiest and quickest way to import data from Access (any version from Access 2007 to current) into Excel (any version from Excel 2007 to current) is by using the "Import From Access" Wizard. The following steps will assist you with this process:
1) Select the first unused tab at the bottom of the Spreadsheet, right click on it and rename it "CustomersTable".
2) 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) Select the Table Style you wish to apply to this table.
9) Left click anywhere on the imported data in worksheet then go to the Design Icon Ribbon and select Convert to Range then click OK.
10) Check that the data has correctly been imported correctly into this worksheet.
11) 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.
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.
Task 3: Index Worksheet
Add a worksheet labelled Index before the four tables from Task 1. Put a heading at the top of the worksheet in cell A1 called Index Worksheet. Ensure that it contains all the recommended data for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations.
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) that conforms to the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations. Put a heading at the top of the worksheet in cell A1 called Data Input Worksheet and then input the following template in the exact cell shown1 below onto this worksheet.
Task 5: Calculations Worksheet Template
Add a worksheet labelled Calculations after the DataInput worksheet from Task 4 (but before the four tables from Task 1) that conforms to the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations. Put a heading at the top of the worksheet in cell A1 called Calculations Worksheet and then input the following template in the exact cells 3 shown below onto this worksheet.
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.
Task 7: Report Worksheet Headings
Add a worksheet labeled Report after the Calculations worksheet from Task 5 (but before the four tables from Task 1) that conforms to the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations. Put a heading at the top of the worksheet in cell A1 called Report Worksheet and then type the following column headings, starting in cell A3:
Customer ID, Title, Family Name, Given Names, Address, City, State, Postcode, Distance (km), Item ID, Type, Description, Size, Freight Weight (Kg), Supplier ID, Supplier Name, Supplier Recommended Markup (%), Order Date, Order Qty, Cost Price (IE), Cost Price (NZ), Cost Price (AU), Selling Price, Item Discount, Purchases, Sales, Freight Cost, Order Discount.
Task 8: Report Worksheet Cell Reference
On the Report worksheet, Use the Cell References formula, to obtain all 2500 rows of data from the OrdersTable worksheet for the following - Customer ID, Item ID, Order Date, Order Qty.
Task 9: Report Worksheet VLOOKUP
On the Report worksheet use the VLOOKUP function with the Cell Range Names (Task 6), obtain all 2500 rows of data from the CustomersTable, ItemsTable and SuppliersTable worksheets for the following -
- Customers Worksheet: Title, Family Name, Given Names, Address, City, State, Postcode, Freight Distance (km).
- Items Worksheet: Type, Description, Size, Freight Weight (kg), Supplier ID, Cost Price (IE), Cost Price (NZ).
- Suppliers Worksheet: Supplier Name, Recommended Markup (%).
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 57.5% into the cell containing the phrase: [Insert Store MarkUp % here],
- Type IE into the cell containing the phrase: [Insert Exchange Rate Type here],
Type the current Exchange Rate IE to AU into the cell containing the phrase: [Lookup & Insert IE to AU Exchange Rate here], and replace the "as at xx/xx/xx" with the date you looked up the exchange rate.
Type the current Exchange Rate NZ to AU into the cell containing the phrase: [Lookup & Insert NZ to AU Exchange Rate here], and replace the "as at xx/xx/xx" with the date you looked up the exchange rate.
Task 11: Report Worksheet Cost Price (AU) Nested IF
On the Report worksheet:
1. Develop an IF function using the new Recommended Exchange Rate Type value (from the DataInput worksheet to calculate the Cost Price (AU) (in the Cost Price (AU) column) using the following criteria:
If the Exchange Rate Type is IE then the Cost Price (AU) is calculated by multiplying the Cost Price (IE) by the Exchange Rate (IE to AU) cell on the DataInput worksheet.
2. Ensure that you include rounding (to 2 decimal places - Beskeen Excel Chapter 17) into each calculation in your IF function and error checking (Beskeen Excel Chapter 18) to avoid incorrect results due to typing mistakes.
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 NZ into the Exchange Rate Type cell.
Go to the Report worksheet and observe whether the Cost Price (AU) have changed.
On the DataInput worksheet, type GIGO into the Exchange Rate Type cell.
Go to the Report worksheet and observe whether the Cost Price (AU) now displays an error message.
On the DataInput worksheet, replace the word GIGO with one of the two valid values for the Exchange Rate Type cell.
Task 12: Report Worksheet Selling Price Nested IF
On the Report worksheet:
1. Develop an IF function using the new Recommended MarkUp Type value (from the DataInput worksheet) to calculate the Selling Price (in the Selling Price column) using the following criteria:
If the MarkUp Type is Store then the Selling Price is calculated by increasing Cost Price by the Store's Recommended MarkUp % from the DataInput worksheet.
If the MarkUp Type is Supplier then the Selling Price is calculated by increasing Cost Price by the Supplier's Recommended MarkUp.
2. Ensure that you include rounding (to 2 decimal places - Beskeen Excel Chapter 17) into each calculation in your IF function and error checking (Beskeen Excel Chapter 18) to avoid incorrect results due to typing mistakes.
3. Test the IF function: Once you have completed the Selling Price IF function, perform the following two tests on it to ensure that it is working correctly:
On the DataInput worksheet, type Supplier into the MarkUp Type cell.
Go to the Report worksheet and observe whether the Selling Prices have changed.
On the DataInput worksheet, type GIGO into the MarkUp Type cell.
Go to the Report worksheet and observe whether the Selling Prices now displays an error message.
On the DataInput worksheet, replace the word GIGO with one of the two valid values for the MarkUp Type cell.
Task 13: Report Worksheet IF / AND: Item Discount
1. On the Report worksheet develop an IF / AND function to calculate the amount 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 $25.00 AND the OrderQty is greater than or equal to five (5) of the same item in any single order then the Item Discount is calculated at 7.25% of the Selling Price for that item, otherwise the Item Discount is zero.
2. Ensure that you include rounding (to 2 decimal places - Beskeen Excel Chapter 17) into the calculation in your IF / AND function.
3. Test the IF function: Once you have completed the Item Discount IF / AND function and filled it down the column, review the values to ensure that it is working correctly in the following situations:
1. OrderQty is less than five (5) and Selling Price is less than or equal to $25.00, should result in $0.00 Item Discount.
2. OrderQty is less than five (5) and Selling Price is greater than $25.00, should result in $0.00 Item Discount.
3. OrderQty is greater than or equal to five (5) and Selling Price is less than or equal to $25.00, should result in $0.00 Item Discount.
4. OrderQty is greater than or equal to five (5) and Selling Price is greater than $25.00, should result in an Item Discount.
Task 14: Report Worksheet Purchases, Sales and Order Discount Formulas
On the Report worksheet calculate the following three simple formulas:
1. Purchases by multiplying Order Qty with Cost Price (AU).
2. Sales by multiplying Order Qty by the difference of Selling Price less the Item Discount amount.
3. Order Discount by multiplying Order Qty with Item Discount.
Task 15: Report Worksheet Freight Cost Nested IF
1. On the DataInput worksheet, type Lethbridge-Stewart 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 Lethbridge-Stewart Freight then the Freight Cost is calculated at two dollars and ninety-nine cents ($2.99) per kilogram of the item's freight weight and a quarter of a cent ($0.0025) per kilometre (Distance) if over 250 km (otherwise there is no cost for Distance) per item ordered (Order Qty).
If the Freight Type is Sarah Jane Smith Transport then the Freight Cost is calculated at one dollars and seventy-five cents ($1.75) for the first three-quarters of a kilogram (0.75 kg) and then at two dollars and seventy-five ($2.75) per kilogram of the item's freight weight over the first three-quarters of a kilograms (0.75 kg) per item ordered (Order Qty).
3. Ensure that you include rounding (to 2 decimal places - Beskeen Excel Chapter 17) into each calculation in your IF function and error checking (Beskeen Excel Chapter 18) to avoid incorrect results due to typing mistakes.
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 Sarah Jane Smith Transport into the Freight Type cell.
Go to the Report worksheet and observe whether the Freight Costs have changed.
On the DataInput worksheet, type GIGO into the Freight Type cell.
Go to the Report worksheet and observe whether the Freight Costs now displays an error message.
On the DataInput worksheet, replace the word GIGO with one of the two valid values for the Freight Type cell.
Task 16: Modify Data Input Worksheet
On the DataInput worksheet:
1. Replace the phrase "[Insert Bank Charges Expenses here]" in cell D15 with $137.27
2. Replace the phrase "[Insert Electricity Expenses here]" in cell D16 with $5,675.12
3. Replace the phrase "[Insert Freight Inwards Expense here] " in cell D17 with $33,343.49
4. Replace the phrase "[Insert Internet Expense here] " in cell D18 with $1,823.56
5. Replace the phrase "[Insert Telephone Expenses here]" in cell D19 with $4,241.90
6. Replace the phrase "[Insert Wages Expenses here]" in cell D20 with $54,141.52
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 Half Year Fixed Expenses by adding all the Half Year Fixed Expenses on the DataInput worksheet.
3. Replace the phrase in D10 "[Insert Formula here]" with a function to calculate the Total Purchases Expenses 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 Outward Expenses by adding all the Freight Costs in the Freight Cost column on the Reports worksheet.
5. Replace the phrase in D12 "[Insert Formula here]" with a formula to calculate the Percentage Freight Outwards of Total Sales by dividing the Total Freight Outward Expenses by the Total Sales.
6. Replace the phrase in D14 "[Insert Formula here]" with a formula to calculate the Total Half Year Fixed & Variable Expenses by adding all the Half Year Fixed Expenses and Half Year Variable Expenses on the Calculations worksheet.
7. Replace the phrase in D16 "[Insert Formula here]" with a formula to calculate the Total Profit by subtracting the Total Half Year Fixed & Variable Expenses from the Total Sales on the Calculations worksheet.
8. Replace the phrase in D18 "[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.
9. Replace the phrase in D19 "[Insert Formula here]" with a function to calculate the Percentage Discount of Total Sales by dividing the Total Discount for Orders by the Total Sales.
10. Replace the phrase in D20 "[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.
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 Half Year 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 D27 with a cell reference to the Percentage Freight Outwards of Total Sales amount on the Calculations worksheet.
6. Replace the phrase "[Insert Cell Reference Here]" in cell D29 with a cell reference to the Total Half Year Fixed and Variable Expenses amount on the Calculations worksheet.
7. Replace the phrase "[Insert Cell Reference Here]" in cell D31 with a cell reference to the Total Profit amount on the Calculations worksheet.
8. Replace the phrase "[Insert Cell Reference Here]" in cell D32 with a cell reference to the Total Discount for Orders amount on the Calculations worksheet.
9. Replace the phrase "[Insert Cell Reference Here]" in cell D33 with a cell reference to the Percentage Discount of Total Sales amount on the Calculations worksheet.
10. Replace the phrase "[Insert Cell Reference Here]" in cell D34 with a cell reference to the No. Orders Discount Applied amount on the Calculations worksheet.
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.
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, D27, D29, D31, D32, D33 and D34 on the DataInput worksheet.
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
- Format the Colours and Shading to Match the other worksheets
Modify the Scenario Summary as follows:
- Highlight the cell(s) with the Optimal Total Sales with Yellow Highlight.
- Highlight the cell(s) with the Optimal Total Freight Outwards with Green Highlight.
- Highlight the cell(s) with the Optimal Total Profit with Blue Highlight.
- Highlight the cell(s) with the Minimum Number of Discounts Applied with Red Highlight.
- Highlight the cell(s) with the Maximum Number of Discounts Applied with Orange Highlight.
- Highlight the Optimal Scenario with a Thick Red Border.
Place the Scenario Summary worksheet between the Calculations and Report worksheets.
Task 21: Documentation Worksheet
Add a Documentation worksheet after the four tables from Task 1. Put a heading at the top of the worksheet in cell A1 called Documentation. Ensure that contains all the recommended information for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations.
The Documentation Worksheet must demonstrate the following:
A statement outlining the purpose of the Spreadsheet:
A statement about the protection level that should be used on each Worksheet.
A statement about how the user can access and use the Worksheets.
A statement about what calculations have been used in the development of this Spreadsheet.
Task 22: Analytical Essay
Dr Tara Dis has asked you to develop an analytical essay (between 750 and 1000 words in length) to the client, The Doctor outlining the major issues that the Decision Support System's results have highlighted. Create a Word Document (using Word 2007 to current).
The body of your essay should consider the following issues:
Define what a Decision Support System is.
Explain why a Decision Support System is the appropriate tool for this project.
From the results of the Decision Support System results (Report Worksheet and Scenario Summary) discuss the following (keeping in mind the information provided in the preamble on page 2):
1. Which 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?
Hire Competent Information Systems Concepts Assignment Help Tutors and Boost Up Your Academic Grades At Affordable Prices..!!
Tags: Information Systems Concepts Assignment Help, Information Systems Concepts Homework Help, Information Systems Concepts Coursework, Information Systems Concepts Solved Assignments, Database Management System Assignment Help, Database Management System Homework Help
Attachment:- Information Systems Concepts.rar