Assignment 1 | UUIS7100
UUIS7100 PROFESSIONAL SKILLS FOR INFORMATION SYSTEMS | 3, 2018
Version: 1, 13 December, 2019
You are expected to submit professionally presented word-processed assessment documents. This includes:
• A title page showing: ID number/s, name/s, lecturers’ name/s, and assessment title.
• Correct spelling and appropriate use of grammar.
• Pages numbered including a contents page.
• Stapled or bound (no paper clips/plastic folders or plastic sleeves).
• Questions correctly labelled and numbered with clear and consistent headings
• Line spacing no less than 1.5 and no greater than double. Main text using 12pt font size.
• A complete reference list should be included at the back of the assessment using Harvard style of referencing with in-text citation
Description : Spread-sheet Activity
Objective : The LOs for this assignment are:
LO1 Compare and contrast the concepts of information systems (20 Marks)
LO2 Critically analyse business requirements from an information systems perspective (30 Marks)
LO5 Communicate information systems concepts to both technical and nontechnical audiences within a business context. (10 Marks)
The assignment description provides some explanation of how to use Microsoft Excel 2010 or above, but you are also expected to use available resources such as Online Help, textbooks, the practical skills sections at the end of each class handouts, and self-study.
Important Instructions
This assignment must be the expression of your own work. It is acceptable to discuss general course content with others to improve your understanding and clarify requirements, but solutions to this assignment question must be done on your own. You must not copy from anyone, including tutors and fellow students, nor provide copies of your work to others. Assignments that do not adhere to this requirement will be deemed as being the result of collusion or plagiarism.
Assessment Marks Weighting Issue Date Due Date
Assignment 1 60 20% 09/01/2019 31/01/2019
Assignment-1 Instructions:
1. In this assignment, you need to use different components of Microsoft Excel 2010/2013/2016, 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, textbooks, the practical skills sections at the end of each class handouts, and self-study.
2. This assessment requires you to answer ALL questions, which are awarded marks as indicated.
3. Use MS Excel 2010/2013 for summarizing data, making tables, and drawing graphs. You are expected to submit a professionally presented spreadsheet files.
4. Keep your hard and soft copies of this assignment.
5. Do not post formulas to the UUNZ online discussion forums; it may result in academic misconduct.
6. Late assignments will receive a 10% marks deduction for each day (including weekends) after the due date.
7. This is an individual assignment and must be your own work. Any student caught cheating will receive zero marks. If copying is identified, all parties involved will receive zero marks.
8. BEFORE submitting your FINAL soft copy of the assignment, you must upload your draft copies to the online plagiarism checker system.
Guidelines for Submission in UUNZ uPortal System
Attach the Microsoft Excel 2010/2013 files using the naming below convention to your online assignment submission link in the Assignment 1 area on the CIS5100 UUNZ Moodle System before 5PM on the day the assignment is due.
Lastname_Firstname_ Student ID _ Course Code_ Assignment number
For example: Bashar_Farhad_123456_CIS7100_A1.xlsx
Note: If the assignment files cannot be opened by the marker, it may be treated as late until a suitable replacement is received.
If you have difficulties submitting through the UUNZ Moodle System Assignments submission tool, please contract with your course lecturer /examiner. As a last resort only, email the course examiner for instructions on an alternative course of action.
Introduction of the case study
Mr. Singh retired from his long career in Human Resource Management and now started a Steam Punk themed mail order business called “Steam Age Emporium” and began selling various types of imported Steam Punk related merchandise such as ladies’ and gentlemen’s clothing, ladies’ and gentlemen’s boots, headwear, goggles and other assorted Steampunk related paraphernalia to customers via mail order around Australia and New Zealand.
These types of Steampunk products have become very popular and have increasing demand in the market. Mr. Singh’s business has experienced a rapid growth in their mail orders. When he first retired, he bought his own home computer to help manage the business’s books and finances. The business has contracted to your firm, Boco Technologies, to assist in setting up more of the business’s various computer-based information systems.
Mr. Singh is interested in a Decision Support System (DSS) based on Microsoft Excel spread-sheet application. The DSS will be used to analyse sales trends for the business to determine future courses of action for the business.
Mr. Singh would like the information in the text files imported into Microsoft Excel 2010/2013 to analyse the business performance. The suppliers used by the business, source items from either Finland (FI) or Brunei (BN). 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.
Mr. Singh 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.
Source: maskworld.com, Steampunk.
List of Spread-sheet activities under Decision Support System (DSS) project:
Mr Smith, your manager, has set up the 20 questions below for you to complete for the Spreadsheet activities under DSS project:
1. Develop a Spread-sheet, import the four (4) text files data in MS 2010/2013 and make four
(4) tables into separate worksheets for each type of data;
2. Check the data type and data validation;
3. Prepare an initial Index Worksheet;
4. Generate a Data Input Worksheet Template for later use;
5. Prepare a Calculations Worksheet Template for later use;
6. Create Name Ranges for the Customers, Items, and Suppliers data;
7. Develop a Report Worksheet and set up the column headings;
8. Alter the Report Worksheet by Cell Referencing all the Orders Table data;
9. Use VLOOKUP to get Customer, Item and Supplier data and Modify the Report Worksheet;
10. Include some extra data in Data Input Worksheet that will be needed for Q-11 to Q-15;
11. Use a Nested IF to calculate Cost Price (NZ) and modify the Report Worksheet;
12. Use a Nested IF to calculate Selling Price and modify the Report Worksheet;
13. Use a Nested IF to calculate Freight Cost and modify the Report Worksheet;
14. By using a AND / IF to calculate Item Discount in the Report Worksheet;
15. Modify the Report Worksheet by using simple formulas to find Purchases, Sales, and Order Discount;
16. Include further extra data in Data Input Worksheet for Q-17 to Q-20;
17. Modify the Calculations Worksheet by using simple Excel formulas;
18. Use Cell Referencing for all the Calculations data and modify the Data Input Worksheet;
19. Create eight (8) Scenarios on the Data Input Worksheet and Prepare a Scenario Summary which contains the eight (8) Scenarios; and
20. Generate a Documentation Worksheet.
In each question you are also asked to critique and critically analyse the spreadsheet activity on the perspective of Information systems concepts and its impact on the given case business requirements. Write your critical analysis at the bottom of each spreadsheet.

Question-1: Import of Data and Develop Tables
Open a blank new Excel 2010/2013 spreadsheet file and name the file - ‘[Lastname] [Initial] _ [Student ID] _ [Course Number] _ [Assignment Number]’ (eg. BasharF_123456_UUIS7100_A1.xlsx).
Import the four (4) text files data into Microsoft Excel 2010/2013 and make four (4) tables into separate worksheets for each type of data.
The easiest and quickest way to import data from Text Files into Excel 2010/2013 is by using the “Import From Text” Wizard. The below steps will assist you with this process:
1) Select the first unused tab at the bottom of the worksheet, right click on it and rename it “CustomersTable”. Remember there is no space exist in this renaming.
2) Put a heading at the top of the worksheet in cell A1 called “Customers Table”.
3) Then, go to the Ribbon named Data.
4) Click on the From Text option in the Get External Data icon area.
5) In the Get External Data – From Access wizard popup, browse to find your “Assignment01 Text File Customers” file and select the file and then click Import.
6) Select Existing Worksheet $A$3 as the location to Import the place to put the data.
7) Create professional style table (e.g. by using Insert – Table and Design Ribbon) and select Convert to Range then click OK.
8) Make sure that the data has been imported correctly into this worksheet. Amend the data layout 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 8 steps for the rest of the Text files naming for each worksheet as follows:
Data File Worksheet Name Worksheet Title (Cell A1)
Assign01 Text File Items ItemsTable Items Table
Assign01 Text File Orders OrdersTable Orders Table
Assign01 Text File Suppliers SuppliersTable Suppliers Table
9) Provide a one-paragraph critical analysis of how these activities will help Mr Singh’s business.
Question-2: Data Type and 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 suitable formatting if required).
3. The content of each column contains complete and accurate data values (e.g. Phone numbers are not shortened).
4. The content of each column contains reasonable data values for the use of this analysis.
5. Explain why data checking is important to the IS requirements for this business.
Hints: Amend the data layout to a professional level of presentation, making sure that the headings are in English (Customer ID not CustID).
Question-3: Adding an Index Worksheet
Add a worksheet labelled Index before the four tables from Q-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 your 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.
Explain why it is important to have an Index Worksheet
Question-4: Generate Data Input Worksheet Template
Add a worksheet labelled DataInput after the Index worksheet from Q-2 (but before the four tables from Q-1). 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 (FI to NZ) [Insert FI to NZ Exchange Rate here]
9 Exchange Rate (BN to NZ) [Insert BN to NZ 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]
onwards.
Modify the layout of the data to a professional level of presentation. Use bold, italics, font size, font colours, shading, lines and borders.
Explain why a Data Input Worksheet Template will improve the way the information system works for this business.
Question-5: Developing Calculations Worksheet Template
Add a worksheet labelled Calculations after the DataInput worksheet after Q-4 (but before the four tables from Q-1). 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 input number, formula and function from Questions 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.
Explain why it is necessary for a business to have a professional level of presentation.
Question-6: Create Cell Range Name
On the CustomersTable, SuppliersTable and ItemsTable worksheets set the following Cell Range Names:
• Cust – on all the data (not headings) in the CustomersTable worksheet
• Supp – on all the data (not headings) in the SuppliersTable worksheet
• Itms – on all the data (not headings) in the ItemsTable worksheet
Hints: You must only create the three name ranges listed above, any other name ranges used may result in loss of marks.
Explain why it is necessary to create A Cell Range Name.
Question-7 Develop Report Worksheet with Headings
Add a worksheet labelled Report after the Calculations worksheet after Q-5 (but before the four tables from Q-1). 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 (FI), Cost Price (BN), Cost Price (NZ), 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.
What purpose to Report Worksheets with Headings serve in setting up an Information System for a business.
Question-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.)
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. Modify the Summary worksheets to a professional level of presentation, making sure that the data is formatted correctly.
Explain what purpose Report Worksheet Cell Reference serves for the information system for this business.
Question-9: Use VLOOKUP in Report Worksheet
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:
? Title, Family Name, Given Names, Address, City, State, Postcode, Freight Distance (Km)
Items Worksheet:
? Type, Description, Colour, Size, Freight Weight (Kg), Supplier ID, Cost Price (FI), Cost Price (BN)
Suppliers Worksheet:
? Supplier Name, Recommended Markup (%)
Explain what purpose the VLOOKUP function in Report Worksheet serves and how this benefits the business.
Question-10: Inclusion of data in Data Input Worksheet
On the DataInput worksheet perform the following:
? Type Store into the cell containing the expression: [Insert Markup Type here],
? Type 72.5% into the cell containing the expression: [Insert Standard MarkUp here],
? Type FI into the cell containing the expression: [Insert Exchange Rate Type here],
? Type 1.52238 into the cell containing the expression: [Insert FI to NZ Exchange Rate here],
? Type 0.996373 into the cell containing the expression: [Insert BN to NZ Exchange Rate here].
Explain how the above functions help the business.
Question-11: Nested IF function in Report Worksheet Cost Price (NZ)
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 (NZ) (in the Cost Price (NZ) column) using the following criteria:
If the Exchange Rate Type is FI then the Cost Price (NZ) is calculated by multiplying the Cost Price (FI) by the Exchange Rate (FI to NZ) cell on the DataInput worksheet. Hint: Cost Price (FI) x Exchange Rate (FI to NZ)
If the Exchange Rate Type is BN then the Cost Price (NZ) is calculated by multiplying the Cost Price (BN) by the Exchange Rate (BN to NZ) cell on the DataInput worksheet.
Hint: Cost Price (BN) x Exchange Rate (BN to NZ)
2. Make sure that you include rounding (to 2 decimal places) into each calculation and error checking 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.
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.
3. Check the IF function: Once you have completed the Cost Price (NZ) IF function, perform the following two tests on it to ensure that it is working correctly:
On the DataInput worksheet, type BN into the Exchange Rate Type cell.
Go to the Report worksheet and observe whether the Cost Price (NZ) 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 (NZ) now shows an error message.
4. Explain what purpose a Nested If function in a Report Worksheet has for Cost Price.
Question-12: Nested IF function in Report Worksheet Selling Price
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 (NZ) + Cost Price (NZ) 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 (NZ) + Cost Price (NZ) x Supplier Recommended MarkUp
2. Confirm that you include rounding (to 2 decimal places) into each calculation and error checking 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.
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.
3. Check 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 shows an error message.
4. Explain what the productivity gains are for using a Nested If function in the Report Worksheet selling price.
Question-13: Nested IF function in Report Worksheet Freight Cost
1. On the DataInput worksheet, type Milady Freight into the cell containing the expression: [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 Milady Freight then the Freight Cost is calculated a quarter of a
cent ($0.0025) per kilometre (Distance) and a dollar seventy-five cents ($1.75) per kilogram of the item’s freight weight per item ordered (Order Qty).
Hint: ($0.0025 x Distance + $1.75 x Item Weight) x OrderQty
If the Freight Type is Monsi Transport then the Freight Cost is calculated at a dollar and fifty cents ($1.50) for the first one and a quarter kilograms (1.25 kg) and then at a dollar and fifty cents ($1.50) per kilogram of the item’s freight weight over one and a quarter kilograms (1.25 kg) per item ordered (Order Qty).
Hint: ($1.50 + IF(Item Weight 1.25 kg, (Item Weight – 1.25kg) x $1.50, 0)) x OrderQty
3. Make sure that you include rounding (to 2 decimal places) into each calculation and error checking 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.
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.
4. Check 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 Monsi 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.
5. Explain what you have done with this Nested If function for Freight Cost in the Report Worksheet.
Question-14: IF / AND function in Report Worksheet: 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 ten (10) of the same item in any single order AND the Selling Price for that item is greater than $150.00, then the Item Discount is calculated at 8.125% of the Selling Price for that item, otherwise the Item Discount is zero.
Hint: =IF (AND (OrderQty = 10, Selling Price $150), Selling Price x 8.125%, 0)
2. Ensure that you include rounding (to 2 decimal places )
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.
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.
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 ten (10) and Selling Price is less than or equal to $150, should result in $0.00 Item Discount.
• OrderQty is less than ten (10) and Selling Price is greater than $150, should result in $0.00 Item Discount.
• OrderQty is greater than or equal to ten (10) and Selling Price is less than or equal to $150, should result in $0.00 Item Discount.
• OrderQty is greater than or equal to ten (10) and Selling Price is greater than $150, should result in an Item Discount.
4. What was the purpose of testing the If function in 3 above. What added value is this to the organisation.
Question-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 (NZ).
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.
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.
4. What is the purpose of using formulas in the Report Worksheet. How does this benefit the business.
Question-16: Replace new input in Data Input Worksheet
On the DataInput worksheet:
1. Change the phrase “[Insert Bank Charges Expenses here]” in cell D15 with $2,830.46
2. Change the phrase “[Insert Electricity Expenses here]” in cell D16 with $4,694.57
3. Change the phrase “ [Insert Freight Inwards Expense here] ” in cell D17 with $80,721.94
4. Change the phrase “ [Insert Internet Expense here] ” in cell D18 with $3,503.57
5. Change the phrase “[Insert Telephone Expenses here]” in cell D19 with $4,882.58
6. Change the phrase “[Insert Wages Expenses here]” in cell D20 with $44,148.76
7. What purpose was served in changing the phrases above? Did this add value for the business?
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.
9. Explain what you have done in using formulas and functions that adds to business productivity.
Question-18: Cell Referencing in Data Input Worksheet
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.
9. Why is it important to use Cell Referencing in a Data Input Worksheet? Is this a productivity aid?
Note: Ensure that you only use cell referencing for the Totals data. 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.
Question-19: Create Scenarios and Prepare Scenario Summary
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 Milady Freight FI
2 Store Milady Freight BN
3 Store Monsi Transport FI
4 Store Monsi Transport BN
5 Supplier Milady Freight FI
6 Supplier Milady Freight BN
7 Supplier Monsi Transport FI
8 Supplier Monsi Transport BN
Note: Ensure you have a suitable scenario name for each scenario, eg. Scenario 1, Scenario 2 …
Using the Scenario Manager in Excel create a Scenario Summary worksheet from the DataInput worksheet based on the eight (8) Scenarios from above table 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.
Explain what you have done in creating a Scenario Summary worksheet.
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
Question-20: Generate Documentation Worksheet
Add a Documentation worksheet after the four tables from Question1 to 20. 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 Class Handout on – 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 created Worksheets and outline whether each sheet should contain protection and why.
Note: 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.
• Why is a Documentation Worksheet necessary for this business. Will this aid productivity?
Summary and Making Guideline:
Student ID: _____________ Name: ___________________ Code: _________
Assess ment
eleme nts:
Spreadsheet Activities Description Marks Allocated Marks Obtained
Q-1: Develop a Spread-sheet, import the four (4) text files data in MS 2010/2013 and make four (4) tables into separate worksheets for each type of data.
Marking Guide for Q-1: 2
Spread-sheet development and presentation
• Complete Spread-sheet without any error 1.0
• Complete Spread-sheet works with minor mistake 0.75
• Incomplete / partial Spread-sheet works 0.5
• Major mistake/ No attempt 0 - 0.25 1
Critical analysis of activities related to business
1
Q-2: Check the data type and data validation. Marking Guide for Q-2: 1
Implement data type and data validation in Spread-sheet
• Data type and data validation correct 0.5
• Incomplete / partial data type and data validation 0.25
• Major mistake/ No attempt 0 0.5
Critical analysis of data checking related to business 0.5
Q-3: Prepare an initial Index Worksheet Marking Guide for Q-3: 3
Implement initial Index Worksheet
• Complete Initial Index Worksheet without any error 1.5
• Initial Index Worksheet with minor mistake 1.0
• Incomplete / partial Initial Index Worksheet 0.5
• Major mistake/ No attempt 0
1.5
Critical analysis of Index Worksheet related to business 1.5
Q-4: Generate a Data Input Worksheet Template for later use
Marking Guide for Q-4: 2
Generate a Data Worksheet Template
• Complete Data Input Worksheet Template correct 1.0
• Complete Data Input Worksheet Template -minor mistake
.75
• Incomplete / partial Data Input Worksheet Template 0.5 1.0
• Major mistake/ No attempt 0
Critical analysis of Data Input Worksheet for later use for presentation of information related to the business
1.0
Q-5: Prepare a Calculations Worksheet Template for later use
Marking Guide for Q-5: 2
Generate a Calculations Worksheet Template
• Complete Calculations Worksheet Template 1.0
• Calculations Worksheet Template - minor mistake .75
• Incomplete / partial Calculations Worksheet Template 0.5
• Major mistake/ No attempt 0 1.0
Critical analysis of Calculations Worksheet Template related to IS for this business
1.0
Q-6: Create Name Ranges for the Customers, Items, and Suppliers d ata
Marking Guide for Q-6: 2
Create Name Ranges for Customers, Items and Suppliers data
• Complete Name Ranges 1.0
• Incomplete Name Ranges - minor mistakes 1.0
• Incomplete / partial Name Ranges 0.5
• Major mistake/ No attempt 0 1.0
Critical analysis of Name Ranges for key items related to
IS for this business
1.0
Q-7: Develop a Report Worksheet and set up the column headings
Marking Guide for Q-7: 4
Develop a Report Worksheet and set up the column headings
• Complete Report Worksheet with column headings 2.0
• Complete Report Worksheet with minor mistake 1.5
• Incomplete / partial Report Worksheet 1.0
• Major mistake 0.5
• No attempt 0 2.0
Critical analysis of how to present a Report Worksheet with column headings 2.0
Q-8: Alter the Report Worksheet by Cell Referencing all the Orders T data able
Marking Guide for Q-8: 2
Implement Cell Referencing for Orders Table data
• Cell Referencing correct 1.0 1.0
• Cell Referencing with minor mistakes 0.75 • Incomplete / partial Cell Referencing 0.5
• Major mistake/ No attempt 0
Critical analysis of Cell Referencing as a productivity aid 1.0
Q-9: Use VLOOKUP to get Customer, Item and Supplier data and Mo the Report Worksheet dify
Marking Guide for Q-9: 4
Implement VLOOKUP
• VLOOKUP used correctly and Report any error 2.0
• VLOOKUP used mostly correctly and Report modified with
minor mistake 1.5
• VLOOKUP and Report modification attempted but
incomplete 1.0
• VLOOKUP and Report modification incomplete and/or
incorrect 0.5
• Major mistake/ No attempt 0 2.0
Critical analysis of the purpose of using VLOOPUP related to business 2.0
Q-10: Include some extra data in Data Input Worksheet that will be needed for Q-11 to Q-15
Marking Guide for Q-10: 3
Implement extra data in Data Input Worksheet
• Extra data included for Q11 – Q15 without any error 1.5
• Extra data included for Q11 – Q15 with some errors 1.0
• Incomplete / partial data included 0.5
• Major mistake/ No attempt 0 1.5
Critical analysis of Data input Workshop activities related to business 1.5
Q-11: Use a Nested IF to calculate Cost Price (NZ) and modify the Re Worksheet port
Marking Guide for Q-11: 4
Implement Nested IF for Cost Price
• Nested IF used correctly 2.0
• Nested IF with minor mistake 1.5
• Nested IF with a number of mistakes 1.0
• Incomplete / partial Nested IF used 0.5
• Major mistake/ No attempt 0 2.0
• Critical analysis of Nested If for Cost Price calculations related to business 2.0
Q-12: Use a Nested IF to calculate Selling Price and modify the Repo rt
Worksheet
Marking Guide for Q-12: 4
Implement Nested IF for Selling Price 2.0
• Nested IF used correctly 2.0
• Nested IF with minor mistake 1.5
• Nested IF with a number of mistakes 1.0
• Incomplete / partial Nested IF used 0.5
• Major mistake/ No attempt 0
Critical analysis of the activities for calculation of Selling Price related to business 2.0
Q-13: Use a Nested IF to calculate Freight Cost and modify the Repo Worksheet by rt
Marking Guide for Q-13: 4
Implement Nested If for Freight Cost
• Nested IF used correctly 2.0
• Nested IF with minor mistake 1.5
• Nested IF with a number of mistakes 1.0
• Incomplete / partial Nested IF used 0.5
• Major mistake/ No attempt 0 2.0
Critical analysis of Nested IF for Freight Cost activities related to business 2.0
Q-14: By using a AND / IF to calculate Item Discount in the Report Worksheet
Marking Guide for Q-14: 4
Implement AND/IF
• AND/IF used correctly 2.0
• And/IF with minor mistake 1.5
• And/IF with a number of mistakes 1.0
• Incomplete / partial And/IF used 0.5
• Major mistake/ No attempt 0 2.0
Critical analysis of AND/IF activities related to business for Report Worksheet
2.0
Q-15: Modify the Report Worksheet by using simple formulas to find Purchases, Sales, and Order Discount
Marking Guide for Q-15: 3
Implement formulas for Purchases, Sales and Order Discount
• Formulas used correctly 1.5 • Formulas used mostly correctly 1.0
• Incomplete / partial use of Formulas 0.5
• Major mistake/ No attempt 0 1.5
Critical analysis of the use of formulas in Report Worksheet activities related to business 1.5
Q-16: Include further extra data in Data Input Worksheet for Q-17 to Q- 3
20
Marking Guide for Q-16:
Implement extra data in Data Input Worksheet
• Extra data used correctly 1.5
• Extra data used correctly with minor mistake 1.0
• Incomplete / partial extra data used 0.5
• Major mistake/ No attempt 0 1.5
Critical analysis of the reasons for adding extra data in the Data Input Worksheet 1.5
Q-17: Modify the Calculations Worksheet by using simple Excel form ulas
Marking Guide for Q-17: 4
Use Excel formulas to Modify Calculations Worksheet
• Calculations worksheet modified correctly 2.0
• Calculations worksheet modified with minor mistake 1.5
• Calculations worksheet modified incorrectly 1.0
• Major mistakes 0.5
• No attempt 0
2.0
Sound explanation of modifying calculations 2.0
Q-18: Use Cell Referencing for all the Calculations data and modify t Data Input Worksheet he
Marking Guide for Q-18: 3
Implement Cell Referencing for Calculations Data
• Complete Cell referencing without error and modify Data
Input Worksheet 1.5
• Complete most Cell Referencing and modify most of the
Data Worksheet 1.0
• Incomplete / partial Cell Referencing and some Data Input
modified 0.5
• No attempt 0 1.5
Critical analysis of why Cell Referencing for all
Calculations data is used to modify the Data Input Worksheet 1.5
Q-19: Create eight (8) Scenarios on the Data Input Worksheet and Prepare a Scenario Summary which contains the eight (8) Scenarios
Marking Guide for Q-19: 4
Implement eight (8) Scenarios
• Complete 8 Scenarios without any error 2.0
• Complete most Scenarios with minor errors 1.5 • Complete some Scenarios with errors 1.0 2.0
• Major mistakes 0.5
• No attempt 0
Critical analysis of why a Scenario Summary is necessary 2.0
Q-20: Generate a Documentation Worksheet
Marking Guide for Q-20: 2
Implement Documentation Worksheet
• Documentation Worksheet completed 1.0
• Documentation Worksheet works with minor mistake 0.5
• Incomplete / partial Documentation Worksheet works .25
• Major mistake/ No attempt 0
1.0
Critical analysis of the purpose of Documentation Worksheet related to business 1.0
TOTAL MARKS 60
Overall Comments
Marker : ___________________________ Initial:________________ Date:_______________