I need someone who is good in excel, it is for my data and computing course I will add an excel sheet for you to work also I will add the questions with files
Page 1 Excel Assignment 1 - Customer & Staff Skills • Using Number Formatting • Using Relative/Absolute/Mixed cell referencing • Applying arithmetic operators and basic math functions • Applying logical functions Assignment - Customer Apply the necessary formulas/functions to complete the tasks shown on the table 1. This is the name of the cell/cell range. Task Point B15 – find the number of holidays customers bought. [1] B16 - find the number of City Breaks (CB) [2] B17 - find the number of Beach Holidays (BE) [2] D5:D13 - apply Azerbaijani manat (₼) as currency type [1] E5:E13 - apply Azerbaijani manat (₼) as currency type [1] F5:F13 - find Total Hotel Price [2] G5:G13 - find cost include s flight and total hotel price [2] H5 – check the tax rate available in cell I18 . Find VAT by multiplying the tax rate with each row of Hotel and Flight (G5:G13 ). You are supposed to refer the cell I18 while calculating VAT. [2] H5:H13 - in order to find VAT for each holiday package use AutoFill [2] I5:I13 - find Total Price for each holiday package includ ing VAT [2] I14 - calculate total price for all holiday packages [2] I15 - find highest price from the T otal Price [2] I16 - find lowest price from the T otal Price [2] I17 - find average price of the T otal Price [2] Page 2 B19 - calculate total price for City Break holidays [2] B20 - calculate total price for Beach holidays [2] J5 - define whether it's a weekend holiday or week by using the IF statement. If the Night is equal to or less than 3 then it will be a weekend holiday. If the Night is greater than 3 then it will be week's holiday. [6] J5:J13 - in order to define for each holiday package whether it's weekend or week holiday use AutoFill. [1] K5 - The ABC Holidays company gives a discount for the next holiday packages. Depending on the total cost of the current holiday package, a discount for the next holiday package can be different. The company will give a 5% discount for the next holiday package if the total cost of the current holiday package is more than or equal to ₼2000. If it's less than ₼2000 but more than or equal to ₼1500, clients will get a 10% discount next time. If it's less than ₼1500 but more than or equal to ₼1250, the company will g ive a 15% discount. If it's less than ₼1250 but more than or equal to ₼1000, the company will give a 25% discount. The company will not give a discount to its clients if their total cost for the current holiday package is less than a thousand manat. Define discount type for each client by using NESTED IF. [10] Assignment - Staff Apply the necessary formulas/functions to complete the tasks shown on the table 2. Task Point B15 - find number of staff that ABC Holidays Company has [1] B16 - find the number of Salesperson that works at ABC Holidays [2] B17 - find the number of Cleaners that works at ABC Holidays [2] C5:C13 - define the position of employees at ABC Holidays by applying the IF statement. Using appropriate cell referencing is supposed from you while applying IF. (E15:F19 cell range is a hint for you). [5] D5:D13 - center the data in column Hours Work ed . [1] E5:E13 - apply Azerbaijani manat (₼) as accounting type [1] F5:F13 - find Weekly Pay ment for each employee . (Hours Worked * Payment Per Hour) [2] Page 3 G5:G13 - find Monthly Pay ment for each employee . [2] H5 - The Payroll taxes for the non -oil -gas industries in Azerbaijan includes Mandatory Social Insurance Contribution, Mandatory Unemployment insurance, Mandatory Medical insurance.Check the payroll rate available in cell I18. Find Payroll by multiplying the payroll rate with each row of Monthly Payment (G5:G13). You are supposed to refer the cell I18 while calculating Payroll. [2] H5:H13 - In order to find Payroll for each employee use AutoFill. [2] I5:I13 - find Total Pay ment for each employee includ ing Payroll . [2] I14 - calculate the total pay ment for all workers. [2] I15 – find the highest cost in Total Pay ment [2] I16 - find the lowest cost in Total Pay ment [2] I17 – find the average cost of Total Pay ment [2] B19 - calculate the total pay ment for Sales person [2] B20 – calculate the total pay ment for Cleaners [2] B21 – calculate the total pay ment for Deputy [2] B22 – calculate the total payment for all employees excluding Cleaners [2] J5 - define that the employee is a Full time or Part time worker by using the IF statement. If hours worked are less than 25 hours, then the employee is Part time. [5] J5:J13 - in order to define for each employee whether full time or part time worker use AutoFill. [1] K5:K13 - The ABC Holidays company gives discounts to its employees for holiday packages. Depending on employees' worked hours discounts for holiday packages can be differ ent. Company will give a 25% discount to its employees if total worked hours is at least 35 hours. If it's less than 35 but more than or equal to 25 hours, the employee will get a 20% discount. If it's less than 25 but more than or equal to 15 hours, the c ompany will give a 10% discount. If it’s less than 15 but more than or equal to 10 hours, the employee will get a 5% discount. Company will not give a discount to its employees if theirs work hours is less than 10 hours. Define discount type for each emplo yee by using NESTED IF. [10] Ensure that the data is all visible within the column boundaries .
