Question 1 (20 pts.)

In Question1 sheet write the year in E5 and the month in E6. Calculate the monthly calendar dynamically. Use date functions and Conditional Formatting for hiding your other Reference cells.

Question 2 (5 pts.)

Name the cell E5 as MyYear and the cell E6 MyMonth.

Question 3 (5 pts.)

Use data Validation for cell E6 based on a list between 1 and 12 with proper notifications.

Question 4 (5 pts.)

Protect all the cells within the calendar table. Check to see if they are protects. Prevent the user from selecting these cells.

Move to Question2 Sheet.

Question 5 (10 pts.)

Use Advanced Filter to retrieve all the rows where the software name begins with the letter F or P. Use copy to another location. The Criteria range with begin in cell L1, The output range will begin in cell L10. Retrieve all the columns.

Move to Question3 Sheet.

Question 6 (5 pts.)

Calculate in cell C4, the FV for the new government, kids program - 50 NIS savings per month until age of 18. The interest rate is 4% per year!

Question 7 (10 pts.)

Create a 2-dimension data table with dynamic range of: Interest between 3% to 5%, increase by 0.1% as start Years between 18 to 21 years program. The table will start in cell D10.

Question 8 (5 pts.)

Create scenarios with the following data. Create a report in a new sheet. Name the sheet Report. Use the first row as the scenario name.

Question 9 (15 pts.)

Record a macro to create the above scenario report. Add a button Scenario Report and assign the macro. Write a Macro that enters a new row between every second row (InsertRows).

Question 10 (5 pts.)

You have 2 options for your new office:

Buy for 200,000 NIS

Rent for 3,000 NIS per month

Use goal seek to find the number of month where the 2 options have the same PV. The interest rate is 0.7% per month.

Save the file and close it ! Close Excel as well and now open it again.

Create a new Excel file and save it by your ID and PQ. Example 2421369PQ.

Question 11 (8 pts.)

Use Power Query for all the next questions.

Open the NorthWind.accdb Access Data base.

For the Employee table:

a. Merge Last name and First name into Full Name b. DeletethecolumnPhoto c. Check the data type for each field d. Load it into excel, (Not to data model) into Sheet Question4.

3. For the Products table:

Delete all the products where the UnitPrice is higher than 50.

ChangetheproductnameformattoUPPERCASE.

Duplicate the column Category.

Change its name to NewCategory.

Add a custom column with the next calculation: UnitPrice *

Unit in Stock. Name the column StockValue

Check the data type for each field

Load it into excel, (Not to data model) into Sheet Question5.

Question 12 (17 pts.)

Open the NorthWind.accdb Access Data base.

Load into the Data Model only the minimum tables for the next Pivot

table.

Create the proper One to Many connections between the tables.

CreatethePivotinQuestion6sheet.

In the pivot the first column will be the Employee name.

The second column will his total sales (Dont forget to calculate to

total line in OrderDetails).

Thethirdtheaveragetotalsales.

Question 13

1. What will be the output of the next macro:

Sub exam() a = 2468

For i = 1 To 4 Cells(i, 1) = a - Int(a / 10) * 10 a = Int(a / 10)

Next i End Sub 2. What will be the output if a will be 7531 ?