CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 Decision Support System (DSS) and Essay…

CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 Decision Support System (DSS) and Essay Description Marks out of Wtg(%) Due date Assignment 2 100.00 30.00 26January 2020 IMPORTANT INFORMATION ACADEMIC INTEGRITY _ This assignment must be all your own work. It is acceptable to discuss course content with others to improve your understanding and clarify requirements, but solutions to assignment questions must be done on your own. _ You must not copy from anyone, including tutors and fellow students, nor allow others to copy your work.
CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 Decision Support System (DSS) and Essay Description Marks out of Wtg(%) Due date Assignment 2 100.00 30.00 26January 2020 IMPORTANT INFORMATION ACADEMIC INTEGRITY _ This assignment must be all your own work. It is acceptable to discuss course content with others to improve your understanding and clarify requirements, but solutions to assignment questions must be done on your own. _ You must not copy from anyone, including tutors and fellow students, nor allow others to copy your work. _ All Assignments will be checked using collusion monitoring tools to ensure that each assignment is the original work of the submitting student. Assignments that do not adhere to this requirement will be deemed as being the result of collusion or plagiarism. This may lead to severe academic penalties as outlined in USQ Policy Library: Academic Integrity Policy and Procedure. It is your own responsibility to ensure the integrity of your work. Refer to the USQ Policy Library for more details: Academic Integrity Policy Academic Integrity Procedure COURSE EXPECTATIONS _ This assignment is quite complex, and exposes you to many different components in Microsoft Excel (any version from Excel 2007 to current), 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, the Beskeen et al text, the Practical Skills sections at the end of each Module in the Study Materials, the SAM online tutorial activities, the course discussion forums, appendices and practical related videos, as well as exploring and experimenting on your own. _ It is important to make sure you have backups of all assignments you create. This is especially true if you store documents on a flash (thumb) drive. Flash drives can become corrupted or lost very easily. There are many options available (Dropbox, or Google Drive) and USQ students already have access to Google Drive via uConnect – UDrive. Another good idea is to Email your assignment file to yourself every-time you finish working on it, that way you have multiple copies of your progress in case you need to revert to a previous stage or are looking for a piece of information you have mistakenly deleted. _ Note: Lost or corrupted assignment files is not grounds for an extension. It is your responsibility to ensure you have backup copies of your files. LATE SUBMISSION POLICY _ USQs Assessment Assignment (Late Submission) and Compassionate and Compelling Circumstances procedures relate to Extensions and Late Assignments. They can be found under the following links: Assessment Assignment (Late Submission) Procedure Assessment of Compassionate and Compelling Circumstances Procedures _ An Assignment submitted after the due date without an approved extension of time will be penalised. The penalty for late submission is a reduction by five percent (5%) of the maximum mark applicable for the assignment, for each University Business Day or part Business Day that the assignment is late. An assignment submitted more than ten (10) University Business Days after the due date will have a mark of zero recorded for that Assignment. P a g e 2 | 17 CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 REFERENCING REQUIREMENTS _ References are required and the Harvard AGPS standard of referencing must be used, including in-text referencing. Ensure that your essay is fully referenced, including any references to the text book. Your essay should include a List of References using the Harvard AGPS standards. A useful link on referencing is from USQs Library site: _ Any direct and paraphrased quotes must be referenced using Harvard AGPS style and listed in the List of References. Do not repeat verbatim large portions of information from other sources such as the text or video you must put the ideas/information in your own words. Only use small direct quotes where possible. ASSIGNMENT 2 REQUIREMENTS LEARNING OBJECTIVES Applicable course objective: apply information systems concepts to identify and resolve problems within a business context apply information systems skills to develop practical solutions within a business context 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 firms Clara Osbourne Technologies development of the Whovian Pandoricas Database Management System (DBMS) utilising Microsoft Access (Assignment 1). The business has contracted our firm to assist in setting up more of the businesss various computer-based information systems. The next computer-based information system that the business is interested in is a Decision Support System (DSS) utilising Microsoft Excel (any version from Excel 2007 to current). The DSS will analyse 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 summarised 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 favourable exchange rate at the time. The Doctor has noted that a number of the businesss 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 userfriendliness in mind. P a g e 3 | 17 CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 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 http://www.bbc.co.uk/doctorwho/medialibrary/images/misc/logos/new_logo_512.jpg, Michael Sauers,11th Doctor Who Logo. . P a g e 4 | 17 CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 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. _ Note: You should consider the colours from a professional perspective. 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. _ Note: If you use Excel Tables to change the background colours of a worksheet anywhere in your Spreadsheet, you must perform this Convert to Range otherwise marks will be deducted. 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. Repeat the above 11 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 P a g e 5 | 17 CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 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. _ Note: A copy of the Assignment 1 Access Database file (containing the four (4) Database Tables) will be made available for download after Assignment 1 results are released to allow students to restart Task 1 – this can be used if you discover the data imported in any of your four (4) table worksheets contains missing or inaccurate values. If you use the copy of the Assignment 1 Access Database file provided on the Study Desk you must change the data for Customer ID 112 to conform to the requirements for Assignment 1 Task 5. 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. _ Note: Modify the layout of the data to a professional level of presentation. Use bold, italics, font size, font colours, shading, lines and borders. P a g e 6 | 17 CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 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. _ Note: Modify the layout of the data to a professional level of presentation. Use bold, italics, font size, font colours, shading, lines and borders. 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 Store MarkUp % here] 7 Recommended Exchange Rate Type [Insert Exchange Rate Type here] 8 Exchange Rate (IE to AU as at xx/xx/xx) [Lookup & Insert IE to AU Exchange Rate here] 2 9 Exchange Rate (NZ to AU as at xx/xx/xx) [Lookup & Insert NZ to AU Exchange Rate here] 10 11 Half Year Income: 12 Total Sales [Insert Cell Reference here] 13 14 Half Year 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 Half Year Fixed Expenses [Insert Cell Reference here] 23 24 Half Year Variable Expenses: 25 Total Purchases Expenses [Insert Cell Reference here] 26 Total Freight Outwards Expenses [Insert Cell Reference here] 27 Percentage Freight Outwards of Total Sales: [Insert Cell Reference here] 28 29 Total Half Year Fixed & Variable Expenses [Insert Cell Reference here] 30 31 Total Profit: [Insert Cell Reference here] 32 Total Discount for Orders: [Insert Cell Reference here] 33 Percentage Discount of Total Sales: [Insert Cell Reference here] 34 No. Orders Discount Applied: [Insert Cell Reference here] _ Note: This template will be modified with correct number, formula and function in tasks 10 onwards. 1 The row and column details are included above to establish exact cell references only. These are not to be included within your final DataInput worksheet. 2 IE is the country code for Ireland (their currency is the Euro) and NZ is the country code for New Zealand (their currency is the New Zealand Dollar) P a g e 7 | 17 CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 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. _ Note: Modify the layout of the data to a professional level of presentation. Use bold, italics, font size, font colours, shading, lines and borders. A B C D 3 Half Year Income 4 Total Sales [Insert Formula here] 5 6 Half Year Fixed Expenses 7 Total Half Year Fixed Expenses [Insert Formula here] 8 9 Half Year Variable Expenses 10 Total Purchases Expenses [Insert Formula here] 11 Total Freight Outward Expenses [Insert Formula here] 12 Percentage Freight Outwards of Total Sales [Insert Formula here] 13 14 Total Half Year Fixed & Variable Expenses [Insert Formula here] 15 16 Total Profit [Insert Formula here] 17 18 Total Discount for Orders [Insert Formula here] 19 Percentage Discount of Total Sales [Insert Formula here] 20 No. Orders Discount Applied [Insert Formula here] _ Note: This template will be modified with correct number, formula and function in tasks 10 onwards. 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. 3 The row and column details are included above to establish exact cell references only. These are not to be included within your final Calculations worksheet. P a g e 8 | 17 CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 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, Item Discount, Purchases, Sales, Freight Cost, Order Discount. _ Note: 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 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. _ 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. 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 discuss with your fellow students nor post formulas to the StudyDesk Forums; it may result in academic misconduct. _ Note: Modify the layout of the data to a professional level of presentation. Use bold, italics, font size, font colours, shading, lines and borders. 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. _ Note: All VLOOKUP functions must use the Cell Range Names created in Task 6. _ 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. Customers Worksheet: o Title, Family Name, Given Names, Address, City, State, Postcode, Freight Distance (km) Items Worksheet: o Type, Description, Size, Freight Weight (kg), Supplier ID, Cost Price (IE), Cost Price (NZ) Suppliers Worksheet: o Supplier Name, Recommended Markup (%) Do not discuss with your fellow students nor post formulas to the StudyDesk Forums; it may result in academic misconduct. See the Videos provided in the Course Content section for more details on creating VLOOKUP functions P a g e 9 | 17 CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 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], _ Note: Look up Exchange Rate IE to AU and Exchange Rate NZ to AU: www.xe.com _ Note: Ensure you use the full Exchange Rate as per the XE website, do not truncate to two decimal places. 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. 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 discuss with your fellow students nor post formulas to the StudyDesk Forums; it may result in academic misconduct. 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. _ 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 Chapter 17). _ 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 and those needed for rounding, 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. P a g e 10 | 17 CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 See the Beskeen Excel Chapter 18 and the Videos provided in the Course Content section for more details on creating IF functions 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 Stores 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 by the Suppliers Recommended MarkUp Hint: Cost Price (AU) + Cost Price (AU) x Supplier Recommended MarkUp Do not discuss with your fellow students nor post formulas to the StudyDesk Forums; it may result in academic misconduct. 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. _ 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 Chapter 17). _ 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 and those needed for rounding, 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 Chapter 18 and the Videos provided in the Course Content section for more details on creating IF functions P a g e 11 | 17 CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 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. Hint: IF (AND (Selling Price > $25.00, OrderQty >= 5), Selling Price x 7.25%, 0) _ Note: You must either convert 7.25% to a decimal or include the percentage symbol % in the formula. Do not discuss with your fellow students nor post formulas to the StudyDesk Forums; it may result in academic misconduct. 2. Ensure that you include rounding (to 2 decimal places Beskeen Excel Chapter 17) into the calculation in your IF / AND function. _ 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 Chapter 17). _ 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 and those needed for rounding, 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. See the Beskeen Excel Chapter 18 and the Videos provided in the Course Content section for more details on creating IF functions 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. P a g e 12 | 17 CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 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). Hint: Cost Price (AU) x Order Qty 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. Hint: Item Discount x Order Qty Do not discuss with your fellow students nor 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 Chapter 17). _ 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 and those needed for rounding, 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. 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 items 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). Hint: ($2.99 x Item Weight + IF(Distance > 250 km, $0.0025 x Distance, 0)) x OrderQty 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 items freight weight over the first three-quarters of a kilograms (0.75 kg) per item ordered (Order Qty). Hint: ($1.75 + IF(Item Weight > 0.75 kg, (Item Weight 0.75 kg) x $2.75, 0)) x OrderQty Do not discuss with your fellow students nor post formulas to the StudyDesk Forums; it may result in academic misconduct. P a g e 13 | 17 CIS1000 ASSIGNMENT 2 QUESTION FILE SEMESTER 3, 2019 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. _ 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 Chapter 17). _ 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 and those needed for rounding, 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 redu

Custom Paper Help
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.