Please review the information in sheet 1 of the excel file. Column A mentions the number of items sold on each day by ABC corporation from 1st January 2020

Assignment 1: Solve the questions in excel file and write the answers in word document.

  1. Please review the information in sheet 1 of the excel file. Column A mentions the number of items sold on each day by ABC corporation from 1st January 2020 (The sales happen daily). Insert a new column before column A mentioning the dates. The selling price per unit is $45.3256.
  2. Find the total income earned on each day in column C (No. of units * Price). All find the total income for the full period.
  3. Find the percentage income of total income on each day and mention it in column D. (Please check that the total percentage is equal to 100)
  4. On which day, did the company make highest and second highest income (Round the total income to 2 decimal points)? What were the income? State the excel function used
  5. On which day, did the company make lowest and second lowest income? What were the income (Round the total income to 2 decimal points)? State the excel function used
  6. Name the first sheet as ABC_sales
  7. Copy only the column with total income of all days to sheet two and sort it from highest income to lowest income.
  8. A new company wants to predict the monthly profit they make in next 2 years. They plan to sell 10 units in first month- January 2021, which keeps increasing every month by 2 units. How much units will they sell in December 2022.

Lets assume they plan to sell one unit at $750. Cost of production is $555 per unit. Fixed expense is $750. Interest on loan is $  500 and tax at 30%. Calculate EAT or net income for all the month Calculate the monthly net income for all the months from 2021 to 2022. (rename the sheet as Net Income.

  1. Please review the information in sheet 3 of the excel file. Column A mentions the number of items sold on each day by XYZ corporation from 15th June 2020 (The sales happen only from Monday to Friday). Insert a new column before column A mentioning the dates. Please be careful while mentioning the dates as you should not include dates on Saturday and Sunday. The selling price per unit is $5. Rename the sheet as XYZ sales.
  2. Find the total income earned on each day in column C (No. of units * Price). All find the total income for the full period.
  3. On which day, did the company make highest and second highest income? What were the income? State the excel function used

 

  1. Sheet 5 mentions the names of students of a class.
  2. Use flash fill to separate First name and second name.
  3. Use Flash fill to generate email address of the students in the following format lastname@scbta.edu

 

  1. Generate a roll number column as A001001, A001002……

 

  1. Use concatenate function to generate a series which has roll number and name. For example A001001 Firstname Lastname.

 

  1. Sheet 6 mentions the name of employees and area of specialization. However, the data is extracted from a report and not organized properly. Reformat the data with name of employees in first column and the specialization in second column. The sheet should not have any other details or should not have statements like “The area of specialization is ”
  2. From the data given in sheet 7, fill in the details in sheet 8 small dataset using vlookup.