Excel Project

Question 1

Excel Project 1

Please read the information below carefully

This project has 2 parts:

Part 1: Excel Formulas: 

1. Click here to download your data.

2. You must read the tutorial from here. The tutorial explains the Nested IF function in Excel which is necessary for this project.

3. Once you completed all the calculations in Excel, save your workbook with an .xlsx extension and upload it below.

4. If you forget to upload your excel file or accidentally submit the project, you will receive 0 for the excel section of the project (you cannot send the excel file manually).

5. You must use Nested IF function to solve this project.

6. Use relative and absolute references properly inside the Nested IF:

- If you choose a relative reference where an absolute reference is necessary, your answer will be wrong.

7.  Pay close attention to inequality signs you use in yourNested IF (If the correct answer is "<" and you use "<=" or vice versa, you will not receive Excel marks).

8. This project is meant to help you get familiar with Nested IF, absolute and relative references. If you avoid using a Nested IF statement, and calculate the answers manually or with your calculator, you won't receive Excel marks.

Part 2: Answer the questions:

Answer questions 1 to 10 based on the results you obtained below. Round your answers to 2 decimal places (if the answer is 0 enter 0.00).

Project:

Consider a hypothetical country (population of 100 people), with a very simple taxation system. Citizens are charged no tax if their income is less than $24000. If they make between $24000 and $44000, they are charged 14% on their income in excess of $24000. People making over $44000 are charged 14% on the $20000  of their income between $24000  and $44000, plus 24% on their income in excess of $44000. 

You must use absolute reference for the numbers in red colour. These 4 numbers will be part of your Excel worksheet.

1- Calculate tax for all the people in the country! Enter your formulas in the cells D2 to D101. (10 Marks - 0.1 mark each)

2- Calculate the minimum tax amount. Enter your formula in the cell E2. (1 Mark)

3- Calculate the maximum tax amount. Enter your formula in the cell F2. (1 Mark)

4- Calculate the total tax amount. Enter your formula in the cell G2. (1 Mark)

5- Calculate the average tax amount. Enter your formula in the cell H2. (1 Mark)

6- Answer the questions below based on your excel results. (10 Marks)

Except these cells: D2 to D101, E2, F2, G2 and H2, do NOT alter other cells of the worksheet as that will negatively affect your mark.

Question 1: What is the tax amount for row number 81?

Question 2: What is the tax amount for row number 45?

Question 3: What is the tax amount for row number 58?

Question 4: What is the tax amount for row number 30?

Question 5: What is the tax amount for row number 60?

Question 6: What is the tax amount for row number 97?

Question 7: What is the tax amount for row number 17?

Question 8: What is the tax amount for row number 51?

Question 9: What is the tax amount for row number 47?

Question 10: What is the tax amount for row number 29?