Create a Spreadsheet and Import the Four (4) Access Tables Into Four (4) Worksheets

Part B Tasks:

Create a Spreadsheet and import the four (4) Access Tables into four (4) Worksheets
Data Validation Check
Create an Index Worksheet
Create a Data Input Worksheet Template for later use
Create a Calculations Worksheet Template for later use
Create Name Ranges for the Customers, Items, and Suppliers data
Create a Report Worksheet and set up the column headings
Modify the Report Worksheet by Cell Referencing all the Orders Table data
Modify the Report Worksheet by using VLOOKUP to get Customer, Item and Supplier data
Modify the Data Input Worksheet to include extra data needed for tasks 11 to 15
Modify the Report Worksheet by using a Nested IF to calculate Cost Price (AU)
Modify the Report Worksheet by using a Nested IF to calculate Selling Price
Modify the Report Worksheet by using a Nested IF to calculate Freight Cost
Modify the Report Worksheet by using a AND / IF to calculate Item Discount
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
Modify the Data Input Worksheet to include extra data needed for tasks 18 to 22
Modify the Calculations Worksheet by using simple formulas
Modify the Data Input Worksheet by Cell Referencing all the Calculations data
Create eight (8) Scenarios on the Data Input Worksheet
Create a Scenario Summary of the eight (8) Scenarios
Create a Documentation Worksheet

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_cis5100_assign1.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 DataIcon Ribbon (see below)

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

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

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

7)In the Import Datawizard popup, select Tableand 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 DesignIcon 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:

The column headings are displayed correctly
The content of each column displays in a format that appears valid for that type of data (apply appropriate formatting if required).
The content of each column contains complete and accurate data values (eg. Phone numbers are not truncated).
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 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 the Practical Appendixes found on the Study Schedule and Module 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 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 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 (IE to AU) / [Insert IE to AU Exchange Rate here]
9 / Exchange Rate (NZ to AU) / [Insert NZ 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.[1][2]

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 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 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.[3]

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 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, 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 Reportworksheet, Use the Cell References formula, to obtain all 1000 rowsof data from the OrdersTableworksheet 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 Reportworksheet 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:
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 (%)

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 47.25% into the cell containing the phrase: [Insert Standard MarkUp here],
type IE into the cell containing the phrase: [Insert Exchange Rate Type here],
type 1.38 into the cell containing the phrase: [Insert IE to AU Exchange Rate here],
type 0.93 into the cell containing the phrase: [Insert NZ to AU Exchange Rate here].

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

On the Reportworksheet:

Develop an IF function using the new RecommendedExchange 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.

Hint:

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

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

Hint:

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

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

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

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.

Task 12: Report Worksheet Selling Price Nested IF

On the Reportworksheet:

Develop an IF function using the new RecommendedMarkUp 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 bythe 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 bythe 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.

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.

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

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

On the DataInput worksheet, type Lethbridge-Stewart Transport into the cell containing the phrase: [Insert Freight Type here].
On the Reportworksheet develop an IF function using the new RecommendedFreight 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 Transport then the Freight Cost is calculated at twenty-five cents ($0.25) per kilogram of the item’s freight weight and a third of a cent ($0.0033) per kilometre (Distance) if over 750 km per item ordered (Order Qty).

Hint:

($0.25 x Item Weight + IF(Distance > 750 km, $0.0033 x Distance, 0)) x OrderQty

If the Freight Type is Sarah Jane Smith Logisticsthen the Freight Cost is calculated at a four dollars and fifty cents ($4.50) for the first nine hundred and ninety grams (0.99 kg) and then at a dollar and five cents ($1.05) per kilogram of the item’s freight weight over nine hundred and ninety grams (0.99 kg) per item ordered (Order Qty).

Hint:

($4.50 + IF(Item Weight > 0.99 kg, (Item Weight – 0.99 kg) x $1.05, 0)) x OrderQty

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

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.

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