ASSIGNMENT 1 PART A: TASK 1
Demonstrate Problem Solving Skills bycreating a Database and import the four (4) Text files into four (4) Tables
- Database correctly created.
- Four Database Tables correctly imported.
DATABASE - CREATION:
Logical development of database as per instructions.
DATABASE TABLES - TEXTFILE IMPORT:
Logical development of database tables through the use of the Textfile Import tool.
Issues detected about your Database Tables Import from Textfiles:
ASSIGNMENT 1 PART A:TASK 2
Demonstrate Problem Solving Skills bymodifying the four imported tables' structures using the given table specifications in Microsoft Access using TableDesign View.
- Data fields have the appropriate data type.
- Data fields have appropriate Field Description to describe the data field.
- Data fields have appropriately set properties.
TABLE DESIGN - SETTING APPROPRIATE DATA TYPES:
Logical development of appropriate data typesfor all data fields in the four database tables.
ASSIGNMENT 1 PART A: TASK 3
TABLE DESIGN - PRIMARY KEYS:
Logical development of database tables primary keys.
Issues detected about your Database Tables Primary Key(s) Development:
ASSIGNMENT 1 PART A: TASK 4
TABLE RELATIONSHIPS:
Demonstrate Problem Solving Skills by the logical development of the relationships between the four (4) tables.
ASSIGNMENT PART A: TASK 5
EDIT CUSTOMERS TABLE:
Demonstrate understanding of Database structure by editing the Customer Table Data details for Customer ID 112.
ASSIGNMENT 1 PART A: TASK 6
Demonstrate Problem Solving Skills by the creation the following THREE(3) queries using the QueryDesign View:
- Query 1: Import Duty Liberia (LR) Increase
- Query 2: Late Dispatched Orders Form Query
- Query 3: Invoice Query with Calculated Fields
ASSIGNMENT ONE PART B
ASSIGNMENT 1 PART B: TASK 3 & 19
Develop the Index Worksheet as per Task 3 and the Documentation Worksheet, including all formulas and functions as per Task 19.
INDEX AND DOCUMENTATION WORKSHEETS
Demonstrate written communication skills by creating an Index and Documentation Worksheets; ensuring that theycontain all the recommended data for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations.
DATA INPUT WORKSHEET
Demonstrate Problem Solving Skills by developing a Data Input worksheet; ensuring that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations.
ASSIGNMENT 1 PART B: TASK 5 & 17
Develop the Calculations Worksheet as per Tasks 5 and 17.
CALCULATIONS WORKSHEET
Demonstrate Problem Solving Skills by developing a Calculations worksheet, ensuring that it contains functions to calculate Total Purchases, Total Freight, Total Discount for Orders and Total Sales. Formulas to calculate Total Fixed and Variable Expenses, Total Profit and Number of Orders where a Discount was Applied. Ensuring that they conform to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations.
ASSIGNMENT 1 PART B: TASKS 6, 7, 8, 9, 11, 12, 13, 14 & 15
Develop the Report Worksheet, including all formulas and functions as per Tasks 6, 7, 8, 9, 11, 12, 13, 14 and 15.
REPORT WORKSHEET - CELL REFERENCES
Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains cell references of CustID, ItemID, OrderDate and OrderQty for all rows of data on the Report worksheet with data drawn from the OrdersTable worksheets. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations.
REPORT WORKSHEET - VLOOKUP FUNCTIONS
Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains VLOOKUP functions using Cell Range Names, for all rows of data on the Report worksheet with data drawn from the CustomersTable, ItemsTable and SuppliersTable worksheets. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations.
REPORT WORKSHEET - IF FUNCTIONS (COST PRICE (AU))
Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains Nested IF functions to determine the Cost Price (AU) based on the Exchange Rate Type in cell D7 of the Data Input Worksheet. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations.
REPORT WORKSHEET - IF FUNCTIONS (SELLING PRICE)
Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it contains Nested IF functions to determine the Selling Price based on the MarkUp Type in cell D4 of the Data Input Worksheet. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations.
REPORT WORKSHEET - IF FUNCTIONS (FREIGHT COST)
Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it containsNested IF functions to determine the Freight Cost based on the Freight Type in cell D5 of the Data Input Worksheet. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations.
REPORT WORKSHEET - IF/AND FUNCTIONS (DISCOUNT)
Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it containsIF/AND functions to determine the Discount amount based on the Order Qty and the Selling Price. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations.
REPORT WORKSHEET - PURCHASES, SALES AND ORDER DISCOUNT FORMULAS
Demonstrate Problem Solving Skills by developing a Report worksheet, ensuring that it containsthree (3) simple formulas to calculate the Purchases, Sales and Order Discount Formulas. Ensure that it conforms to the guidelines for this worksheet listed in the Practical Appendixes found on the Study Schedule and Module Materials - Spreadsheet design considerations.
ASSIGNMENT 1 PART B: TASK 20 & 21
Develop the Scenario Summary as per Tasks 20 and 21.
SCENARIO MANAGER DEVELOPMENT& OUTPUT
Demonstrate Problem Solving Skills by developingthe eight (8) Scenarios as outlined in the assignment based on the changing cells on the Input Data worksheet and a Scenario Summary for the eight (8) Scenarios outline in the assignment based on the results cells found on the eight (8) results cells from the Report worksheet. Ensuring that the output is user friendly and professionally presented.
ASSIGNMENT 1 PART C: TASK 1
Develop an Analytical Essay based on the findings from the Scenario Summary as per Task 22.
ANALYTICAL ESSAY:
Demonstrate written communication skills by creating a well-structured analytical essay based on the findings from the Scenario Summary outlining the major issues that it has highlighted to the business.
Attachment:- Assignment Marking Scheme.rar