Advanced Excel Exam Exercises
Uploaded by
ScribdTranslationsAdvanced Excel Exam Exercises
Uploaded by
ScribdTranslationsADVANCED EXCEL EXAM
Exercise 1 -(6 points)
1) In a new Excel workbook, create the following data sheet:
INVOICE
ARTICLE QUANTITY UNIT PRICE SUBTOTAL IGV (18%) TOTAL
Goma 10 S/. 5.50
Pencil 20 S/. 8.10
Portfolio 145 S/. 12.00
Notebook 15 S/. 5.40
2) Fill in the Subtotal column, which is calculated as: Quantity * Unit Price.
3) Fill in the IGV column which is calculated: Subtotal * 18%.
4) Fill in the Total column, which is calculated as: Subtotal + VAT
Exercise 2(5 points)
1) Open the Excel book provided by the teacher. Subsequently, carry out the points detailed below.
2)Insertar como título de la misma (en una primera línea) el siguiente texto “Empresa el Trébol S.A” en fuente Comic
Without 18 points and red.
3) Make the following appearance improvements to this spreadsheet:
a) Apply the font Courier 12 pt (or similar) and blue color to the header titles. Center them in their cells.
labels of the same.
b) Apply borders and shading to enhance the presentation of the spreadsheet.
c) Apply date cell format to the data in the F_Nac column (e.g., 12-12-75).
d) Apply currency cell format to the data in the Salary column, with thousands separator and 2 decimal places
decimals.
4) At the end of the Salary column, total the values and in one empty cell below, calculate their average.
rounding the result to one decimal place.
5) Swap the order of the columns Name and Last Name to Last Name and Name.
6) At the end of this spreadsheet, insert a column labeled as Prize, calculate 5% of the Salary for each of the
employees. Subsequently copy the format of the data from the Salaries column to this one.
7) Rename Sheet2 to Employees.
8) Complete the missing data using the appropriate functions
Exercise 3(5 points)
1) On sheet 3, carry out the points detailed below.
Sales Control Sheet IGV 18%
Price
Seller Date Model Quantity Total Discount
Unitary
Ana Shoe 3 850
Peter 13/07/2006 Deportivo 1 1400
Peter 18/07/2006 Sandalia 2 350
Ana 07/08/2006 Shoe 2 790
Ana 08/07/2006 Deportivo 3 990
Jose 08/13/2006 Sandal 4 455
Ana 18/07/2006 Zapato 2 1320
Jose 25/08/2006 Deportivo 2 1170
Jose 08/07/2006 Sandal 5 290
2) Calculate the Discount column, taking into account that the "Sandal" model will have a discount on the price.
unitary at 10%, while for the rest it will be 5%.
3) Insert a column between Discount and Total labeled as IGV Amount and calculate it for each sale made.
The price with the corresponding discount and the percentage of VAT = 18% defined in the part must be considered.
superior of the sheet.
4) The Total column must show the result of applying the corresponding discount and VAT to the price for all
the units sold. At the end of this column totalize.
At the end of the sales sheet, insert a column labeled as Points, the generated points must be displayed.
per seller in each sale made. For this, it must be taken into account that sales that exceed 2
units generate 2 points; while those that do not exceed this amount generate only 1 point.
6) In a new sheet named Sellers, calculate how many sales (records in the table) each has made.
seller, how much has each of them raised in total and totalize.
7) In the generated sheet in the Sellers tab, insert a column labeled as Points, it should display the
total points obtained from the sales made by each seller.
At the end of this same sheet, in a column labeled as Prize, a special bonus must be calculated for the
vendedores que hayan acumulado más de 10 puntos. El valor de la prima será del 5% sobre el total vendido por cada
one
Cantidad de Ventas Total de ventas Puntos Premio
Ana
Peter
José
Total
Finally insert a chart showing the total sales by each salesperson
Exercise 4(5 points)
1) On sheet 4, carry out the points detailed below
List of students Institute
Inter-American
Name Course Schedule Court Note
Carlos Peña Word Tomorrow 44
Celeste Perez Word Tomorrow 39
Cecilia Sosa Excel Tomorrow 75
Diana Martinez Excel Afternoon 95
Daniela Saravia Windows Tomorrow 85
Elena Sosa Windows Tomorrow 80
Esteban Da Silva Word Afternoon 76
Fernanda Arriola Windows Tomorrow 88
Fernanda Castro Windows Afternoon 87
Jose Pioli Windows Afternoon 76
Jose Rosas Excel Afternoon 35
Determine the judgment that corresponds to each student, taking into account that it should show 'Passed' if the grade
reaches or exceeds 70 points, otherwise it must show 'Fail'.
3) Format appropriately, and complete the requested data (the number of students), create three graphs.
different ways to show the number of students by course, schedule, and judgment. Do not show legends and add a title.
chord.
4) Insert two new columns labeled as Price and Discounted Price.
5) The Price column must display the cost of the courses taking into consideration the
6) Next: Windows type courses cost S/. 250 while the remaining courses cost
$300
7) In the Discounted Price column, the cost of the courses should be shown but taking into account that the
Students attending in the morning schedule receive a 30% discount on the established price for the type.
of course.
Examples of graphs
SUCCESS..