## All Weeks Excel Skills for Business: Advanced Coursera Quiz Answers

## Table of Contents

### Excel Skills for Business: Advanced Week 01 Quiz Answers

#### Quiz : Spreadsheet Design Principles

Q1. Which of the following are recommended as good practice?

*(Multiple answers possible, partial credit awarded). *

**Dual entry of data should be avoided.****Use consistent naming conventions for table and ranges.**- Input and calculations should be kept in separate workbooks.
- Hard-code assumption values into your formulas.

Q2. Which of the following are good strategies to keep your workbooks flexible and responsive?

*(Multiple answers possible, partial credit awarded).*

- Use volatile functions.
**Convert data into tables.**- Use macros to automate workflows.
**Used named ranges in your calculations.**

Q3. Which of the following are advisable strategies when working with datasets?

*(Multiple answers possible, partial credit awarded).*

- Don’t leave any blank cells in your dataset. Example: If a value in a
**Sale Price**column is not yet available, type in**N/A**. - Merge similar headings into one cell.
**Modularise your data so that only one single type of data occupies one column.****Use data validation techniques for data that is manually entered.**

#### Quiz : Calculations

Q1. We want to use the values in Price column in one of our formulas. What is the best way to select those values?

- Select your data as shown below:
**Select your data as shown below:**- Select the the cell as below and then press CTRL + A on your keyboard.

Q2. Which of the following is a volatile function in Excel?

- MATCH
**INDIRECT**- INDEX
- IF

Q3. Which of the options below is a valid and good practice example for a named range? The example for this range is a column of data containing the sale prices for houses in 2017.

- slphouses17
- Sale-Price-Houses 2017
**SalePriceHouses_2017**- SPH2017

#### Quiz : Formatting

Q1. Which of the statements about styles in Excel below are true?

*(Multiple answers possible, partial credit awarded)*

- The same styles can have a different appearance on each worksheet.
**Once applied, a style can be changed in one location and will affect all occurrences of that style in other parts of the workbook.****Styles control different types of format, including number format and protection.**

Q2. When a new row is added to a table, the formatting from the previous row needs to be re-applied.

- Yes, that statement is true.
**No, that statement is not true.**

Q3. We would like a custom format for our date and have entered the following into the Type field in the Format Cells dialog.

d mmmm yy

What will our date output for the ninth of January 2017 look like?

**9 January 17**- 9 01 17
- 09 01 2017
- 09 January 2017

#### Quiz : Documentation

Q1. It is possible to change the name of a named range after it has been created.

**Yes, that’s true.**- No, that’s not true.

Q2. Which of the following are considered advantages of data validation?

*(Multiple answers possible, partial credit awarded)*

- Data validation in cells is automatically marked as such by Excel, making it easier for users to understand where to enter what data.
**Data validation can increase the speed and accuracy of manual data entry through the use of drop-down lists.**- Data validation can help to prevent errors when users paste content into the spreadsheet.
**Adding data validation can help to prevent manual data entry errors.**

Q3. Which of the following are good strategies to document your spreadsheet?

*(Multiple answers possible, partial credit awarded)*

- Using the company’s style guide.
**Naming tables and named ranges.**- Applying a theme to your data set.
**Using the features in the Review tab (e.g., comments)**

#### Quiz : Week 1 Final Assignment

Q1. Your task is to build a simple financial model using some of the design techniques that you have just learned about. Download the workbook, open it and get ready for your task instructions.

**Important:**

*Remember to save your workbook frequently as you progress through the exam. ***C4 W1 Final Assessment**XLSX FileDownload file

The provided workbook includes the **Inputs** worksheet. You will need to build calculation worksheets in order to model the forecasted **Revenue**, **Cost of Goods Sold**, **Expenses** and overall **Net Income** for each month from **Jan 2018** to **Dec 2022**. The calculations will need to be flexible enough to allow changes to the user-variable input values without the need to re-write any calculation formulas.

Start by adding a new worksheet and name it **Calcs_Monthly**. Have a look at the supplied image. Note that “1.000” is “1”, with three decimal places.

You can also download the image:

We suggest that you keep this open in another browser tab or window so that you can refer back to it easily. This is the look of the worksheet we are going to build, except it is going to extend out to column **BN**, not just column **L**. As you build it you will be asked questions about certain calculated values.

Begin by adjusting the column widths to match the image. Adjust the widths so that

**A**and**B**are width 4,**C**is 30,**D**and**F**are 9,**E**is 12, and- columns
**G:BN**are 12.

How do you go about changing the column width?**1 point**

- Select the column you want to adjust, go to the
**Home**tab, click on**Format**. - Select the column you want to adjust, go to the
**Page Layout**tab, and select**Width**from the**Scale to Fit**area. - Select the column you want to adjust, right-click and choose
**Format cells**.

Q2. Still on the **Calcs_Monthly** sheet, freeze the area from cell **A1** through to **F8**. Which cell do you need to select before you click on **Freeze Panes**?

Q3. Type in the headers, row labels and units labels in columns **B**, **C** and **D** as shown in the image. Apply font colors, borders, bold font and other formatting as shown in the image (this doesn’t have to be exact). Also, type values of **1** in cells **F11** and **F12**, and format them as fixed assumptions (blue font) to 3 decimal places.

Display the **Period Start Date** by using the named range **Model_Start_Date** (already preset in the workbook) in **G5**. What value is displayed in the cell?

Q4. You need to change the result from the previous question to the correct date format. The format needs to be **01-Jan-18**. Which of the below will allow you to do that?

- Select the cell and use the keyboard shortcut
**CTRL + ;** - Select the cell, go to the
**Home**tab, click on**Format**,**Format Cells**… - Select the cell, go to the
**Home**tab, select**Long Date**from the**Number**group.

Q5. Now let’s complete rows 5, 6, 7 and 8.

**Instructions:** In cell **G6**, use the **EOMONTH** function. It requires two arguments. The start date is located in cell **G5**, and we want zero months after the start date for the second argument.

Next, in cell **H5**, write **=G6+1**.

Now apply the same date format as in **G5** to the cells **G6** and **H5**. Use the fill handle to drag the formula from **G6** to **BN6** and from **H5** to **BN5**.

What does it read in cell **Z5**?

Q6. Where possible we want to use a single consistent formula in each row. Where we don’t do that, such as cell **G5** which is different from the rest of row 5, we want to format the unique cell in red font, to indicate to the user that it contains a different formula. Change the font color in cell **G5** to red.

Next, in cells **G7:BN7**, use the **MONTH** function with a reference to row 5 to calculate the value. Do the same in cells **G8:BN8** but use the **YEAR** function instead.

That’s our timing headers complete! We are going to use these row 7 and 8 header values in subsequent calculations.

To double-check your progress, type the value in **AN7** below:

Q7. Now to calculate the Growth Factors. To follow good design practices, we need a single formula in cell **G11** that can be copied across cells **G11:BN11**. It will escalate in value each year according to the assumptions on the **Inputs** tab. Remember it also needs to be flexible enough to allow for changes to the inputs, such as a change in the month of growth at **Inputs!F32**.

For this assessment, we will provide a way of writing the formula, since the emphasis right now is on model design and not the use of particular functions.

Try and make sure you understand what each part of the formula is doing though, and why it is structured the way it is.

Enter the formula below in **G11** – you can use copy and paste.

**=F11*(1+IF(G$7=Inputs!$F$32,INDEX(Inputs!$F$26:$F$30,MATCH(G$8,Inputs!$E$26:$E$30,0)),0))**

Then apply the formula across **G11:BN11**.

Finally, Format row **11** to three decimal places. What is the value in **BH11**?

Q8. The formula from the previous question is quite hard to read and understand – let’s name some of the components in the formula to improve this.

Go to your workbook and name the following ranges — **make sure to be exact!**

**Inputs** worksheet:

**E26:E30**: **GrowthRevYR**

**F26:F30**: **GrowthRevPCT**

**F32**: **GrowthRevMth**

Take a look at the original formula:

**=F11*(1+IF(G$7=Inputs!$F$32,INDEX(Inputs!$F$26:$F$30,MATCH(G$8,Inputs!$E$26:$E$30,0)),0))**

Q9. We will use the same formula construction to write the formula for cells **G12:BN12**, but making reference to the growth rate for expenses this time.

Name your ranges again first on the **Inputs** worksheet:

**E36:****E40**: **GrowthExpYR**

**F36:F40**: **GrowthExpPCT**

**F42**: **GrowthExpMth**

Type below what the formula will look like now.

(*Hint: Once you get the formula to work in Excel, use copy and paste to enter your answer below rather than typing it in manually.*)

Q10. Format row 12 to three decimal places. Drag the formula to complete the row through to column **BN**.

What is the value in cell **AZ12**? (The October 2021 expenses growth factor.)

Q11. Next we will build the five rows in the **Income** section. These are more straight-forward formulas.

First though, we need to format rows **16:21** to display numbers the way we are seeing in the image.

Select all of rows **16:21**, right click and select **Format Cells**. Select the **Number** tab, then the **Custom** category, and then click in the **Type** box. This lets us type in our own custom number format. Delete anything that may already be in the **Type** box and then type:

#,##0;(#,##0)

if your local number system uses a comma as the thousands separator or

#.##0;(#.##0)

if you use a period as the thousands separator.

* Note: *We are not going to explain all the details of custom number formatting here. The short explanation to this particular format is that it will display numbers to 0 decimal places, with a comma or period as a thousands separator, and with negative numbers shown in brackets.

Press OK when you are done.

In row **16** (columns **G:BN**) we want a formula that multiplies the **Revenue** (from **Initial Values**, **Inputs**) with the **Growth Factors** we calculated in row **11** on our calculations sheet.

Define the name for cell **F19** on the **Inputs** sheet as **ValueRev**.

Q12. Drag the formula you wrote in **G16** to complete the whole row.

The next step is to calculate the row sum at cell **E16**.

What is the sum in **E16**?

Q13. In **G17**, write a formula that calculates the **Cost of Goods Sold** (this is the **Revenues** amount multiplied by the value in **F20** on the **Inputs** sheet).

Make the process easier by naming **F20** (**ValueCost**) on the **Inputs** sheet first. Then enter the formula into **G17**, drag it to complete the row through to column **BN**. Note that **Cost** should be negative.

When you are done, calculate the sum of the **Costs of Goods Sold**.

In **G18**, calculate the **Gross Margin** as the sum of the two rows above it, then drag the formula across to complete the row through to column **BN**.

What is the value of the **Gross Margin** in the month of December 2022 (column **BN**)?

Q14. In row **20**, calculate the expenses. Remember they should be a negative value. Make reference to the **Initial Expenses $ / month** value on the **Inputs** sheet, and to the Expenses growth factor at row 12 on the **Calcs_Monthly** sheet. Complete the row through to **BN**.

Calculate the row sum at cell **E20**. What is the value in **E20**?

Q15. Finally, calculate the **Net Income** as the sum of the **Gross Margin** and **Expenses**. What is the value of **Net Income** in the month of April 2020?

### Excel Skills for Business: Advanced Week 02 Quiz Answers

#### Quiz : Tables and Structured Referencing

Q1. What does **@** symbol indicate when used in a formula with structured references?

- That the reference refers to the entire selected column.
- That the reference refers to the first row of the selected column.
**That the reference refers to the current row of the selected column.**- That the reference refers to the last row of the selected column.

Q2. What kind of brackets are typically used within a formula to reference columns from a Table that contains Structured References?

- Round Brackets
**( )**. **Square Brackets [ ].**- Any of the three bracket types will work, they all behave the same.
- Curly Brackets
**{ }**.

Q3. Which of these describes an advantage of setting up an array of data with the name **Sales** as a structured reference that DOES NOT also apply to the method of just using a basic named range to reference the array of data** ?**

- If we select the named array, copy it with Ctrl+C, and paste a copy on a new sheet with Ctrl+V, the new copy will also have a name applied to it.
- We can use the formula
**=ROWS(Sales)**to return how many rows of data we have. - When writing worksheet formulas we can refer to the entire array by a single name within the formula.
**If we add a new row of data immediately below the original array, the reference will automatically update to include the new row.**

#### Quiz : Using Functions to Sort Data

Q1. Live data that is imported into Excel refreshes by default.

- Yes, that’s true.
**No, that’s not true.**

Q2. Which of the following functions is used in the videos to rank a list of abbreviations?

**COUNTIFS**- LARGE
- RANK
- SMALL

Q3. Which of the following functions did we need in order to sort our data in the video?

*Multiple options are possible and partial credit will be given.*

- RANK
**INDEX****COUNTIFS****ROW****MATCH**- SORT

#### Quiz : Introduction to Array Formulas

Q1. The presence of which type of punctuation mark in an Excel formula indicates that we are working with an array?

- Square Brackets
**[ ]** - Parentheses
**( )** **Curly Braces { }**

Q2. What will be the result of this formula entered into a cell?

**=LARGE({2,4,6,8,10,12},4)**

- 12
- An array of the values
**{12,10,8,6}** **6**- An error value

Q3. Which of the following is a valid array formula?

*(Multiple answers possible, partial credit awarded)*

- ={C4:C36*H4:H20}
**{=MAX(G5:G8-H5:H8)}**- ={MAX(G5:G8-H5:H8)}
**{=C4:C36*H4}**

#### Quiz : Week 2 Final Assignment

Q1. In this week’s assignment, you will be doing some analysis of basic weather data. Download and open the workbook:**C4 W2 Final Assessment**XLSX FileDownload file

**Important notes before you get started:**

- Avoid inserting or deleting rows or columns in any of the worksheets, or moving the location of any existing cells. Doing so may cause the Check Sums to give different values, and then you won’t be able to submit the correct answer.
- Make sure to save your progress on the workbook frequently.

———————————————-

The workbook contains annualised average high and low temperatures for 51 cities from the USA, in both Celsius and Fahrenheit. This is contained in the sheet **Raw_Data** in a random order. A subset of this data for the cities beginning with the letters A to D is also in the sheet **Table_Data** as an Excel Table with the name **City_Subset**.

To begin, go to the worksheet **Table_Data**.

We will start with filling in some simple formulas to make sure you are comfortable with using structured references.

In cell **C22**, use the **COUNTA** function to get a count of the number of cities in this table. Your formula must contain a structured reference.

Enter the formula below.

*Note: Don’t add any spaces in your answer below, you must use the English name for the COUNTA function. *

Q2. Using similar structured references, but with a different function and different columns of the table, complete cells **C23** and **C24** with formulas to find the average High Fahrenheit temperature, and the Minimum Low Fahrenheit temperature.

In cell **C26**, calculate the product of **C22:C24**.

Submit your answer from cell **C26**.

Q3. Now we are going to write a formula with structured references to convert the temperatures from Fahrenheit to Celsius. The way to do this is given in the yellow box at cell **F2**. For example, 104 F converted to Celsius is (104 – 32) * (5/9) = 40. Write a formula in cell **F6** that can be dragged across and down the rest of the green cells. Start by typing **=(City_Subset[** and then try and use the appearing menus to help with the rest of the syntax. Remember to use the **@** symbol to refer to the current row.

Once you have your formula in cell **F6**, apply it to the rest of the range of cells **F6:G19**.

Submit the answer from the **Check_Sum** in **G22**.

Q4. Now let’s move on to the larger data set in the sheet **Raw_Data**. This has a larger list of 51 cities, but they are in a random order. The first thing we are going to do is use formulas to generate a list of the locations in alphabetical order.

In column **K**, write a formula at cell **K4** using the **COUNTIFS** function that finds the relative ranking alphabetically of each of the locations. As a way to check your answer, San Antonio (row 4) should be ranked 43. When you are done, apply the formula to cells **K4:K54** and, submit the value of the checksum from cell **K1**.

Q5. In column **L**, write a formula at cell **L4** using the **MATCH** function to find the location of the helper index value (column **I**) within the ranks you found in column **K**. For example, the number “1” (row **4** in column **I**) appears in the 6th cell in column **K**, and so the value in cell **L4** should be 6.

When you are done, apply the formula to cells **L4:L54** and submit the value of the Check Sum from cell **L1**.

Q6. Now we can get our sorted location list. In cell **M4**, use an **INDEX** function that refers to columns **B** and **L** to return the sorted Location list, and apply it to cells **M4:M54**. When you are done, submit the value of the Check Sum from cell **M1**.

Q7. For the next question, we are going to use a multi-cell array formula. To do this, we will start by selecting ALL of the cells that our formula will be entered into, and then we will type our formula followed by Ctrl+Shift+Enter. Our formula is going to return 5 values, and so we will be entering it into 5 cells.

Start by entering the numbers 1 to 5 in the yellow cells **O4:O8**. You can do this the conventional way or as an array. Then, in cells **P4:P8**, write a single multicell array formula using the **LARGE** function and references to columns **C** and **O** so that it returns the 5 largest values in the **High_F** data. When you are done, sum these 5 values at cell **P10** and submit the value in **P10**.

Q8. Let’s try a single cell array formula now. We will focus on the Celsius values in columns **E** and **F**. If we were asked to find the sum of all **Low_C** (column **F**) values that are less than 10, we could do this pretty easily by using a regular **SUMIFS** function. But we could also do it with an array formula using the **SUM** and **IF** functions separately, and the syntax would be **=SUM(IF(F4:F54<10,F4:F54,0))** then Ctrl+Shift+Enter. There’s not much advantage here to using one or the other, but the array option gives us more flexibility to modify the calculation, as we will see in this question which cannot be solved with a single regular **SUMIFS** function.

Write a single cell array formula in **P15** that sums the value of **[High_C * Low_C]** for all locations that have a **Low_C** value less than 10. When you are done, submit your answer.

Q9. For the last part of this assignment, go to the **Distances** worksheet. Here we are going to complete a cross-table of distances for 5 fictional towns. We are provided with the distance between each town pairing at cells **B14:D38**, but we would like these presented in a 5*5 grid, with only the upper-right diagonal half of the grid activated. (Since the distance from Alphaville to Betaburg is the same as the distance from Betaburg to Alphaville, there is no need to list it twice!)

We will have three tasks.

First, we will use array formulas and the **TRANSPOSE** function to fill in the table headers in the yellow cells. Then, we will write a formula to fill in the green cells of the top table. Finally, we will write an array formula to fill in the bottom table so that all of the grey cells are zero.

Select cells **H6:H10**, and type **=Towns** and then Ctrl+Shift+Enter. You have filled in the row headers. Now, select cells **I5:M5** and write an array formula using the **TRANSPOSE** function to fill in the column headers. When you are done, submit the value from the first of the 3 checksums at cell **H14**.

Q10. Now we will populate the green cells at **I6:M10**. You will need to write a formula that refers to the distance table for the relevant town-pairing. This is probably best done by writing a regular formula in cell **I6** with a mix of absolute and relative references, and then dragging it across and down. There are many ways to do this, use whatever you prefer. If you are stuck, try using the **SUMIFS** function, with the criteria that your row headers must match Town 1 (column **B**) and your column headers must match Town 2 (column **C**). When you are done, submit the value from the second checksum at cell **H15**.

Q11. For the final question, we will use a multi-cell array formula to populate the final table. Row and column counters have been provided for us (the numbers 1 to 5 to the left and above the table). We can use these to help us fill in only the green cells by writing a condition that returns the value from the first table only when the row counter (**G21:G25**) is less than or equal to the column counter (**I19:M19**).

Select all of the cells **I21:M25**, write a single array formula that refers to the top table and employs this counter-condition that will successfully accomplish the task, and press Ctrl+Shift+Enter. The values in the grey cells should be 0. When you are done, submit the value from the third checksum at cell **H16**.

HINT: Don’t be alarmed if the main diagonal of the table gives values of zero. This is what we expect since the distance from a town to itself is zero.

Save your work. Well done.

### Excel Skills for Business: Advanced Week 03 Quiz Answers

#### Quiz : Replace blanks with repeating values

Q1. The **Go To Special** command is a useful way of selecting the blank cells in a region, and can be accessed from the **Find & Select** menu. Where in the Ribbon is the **Find & Select** menu located?

**Home tab**- View tab
- Formulas tab
- Insert tab

Q2. What was the quick method used in the video for making a copy of the existing worksheet?

**Holding down Ctrl and then clicking, holding, dragging and releasing the worksheet tab name with the left mouse button.**- Clicking, holding, dragging and releasing the worksheet tab name with the left mouse button.
- Ctrl + C, then choosing
**Paste worksheet**from the**Paste Special**dialog box. - Right-clicking on the worksheet tab name and then selecting
**Copy**from the menu that appears.

Q3. Which of these statements describes the behaviour of the **ISBLANK** function?

- Checks whether a value is a number, and returns
**TRUE**or**FALSE**. - Checks whether a value is not text, and returns
**TRUE**or**FALSE**. **Checks whether a reference is to an empty cell, and returns TRUE or FALSE.**- Checks whether a value is text, and returns
**TRUE**or**FALSE**.

#### Quiz : Fix Dates (DATE, MONTH, YEAR, DAY, TEXT)

Q1. If cell **A1** contains the formula **=DATE(2027,3,6)** what value will be returned in cell **A1**?

- A value corresponding either to 6 March 2027 or 3 June 2027, depending upon Excel’s regional settings.
- A value corresponding to the date of 3 June 2027.
**A value corresponding to the date of 6 March 2027.**- An error value because the year 2027 is outside of Excel’s range for working with dates.

Q2. If cell **A1** contains the formula **=DATE(2017,12,1)** and cell **B1** contains the formula **=TEXT(A1,”ddd”)** what value will be returned in cell **B1**?

- Friday.
- 1, stored as text rather than stored as a value.
**Fri**- 01, stored as text rather than stored as a value.

Q3. In the video, cell **F2** contained the text string **2014.01.21** and the cell **G2** formula **=TEXT(F2,”mmmm”)** was seen to return **2014.01.21** and not the value **January**. Why did the formula at cell **G2** behave this way?

**Because if the cell being referred to by the TEXT function is recognized by Excel as containing text, a number format of “mmmm” specified in the TEXT function has no effect.**- Because
**“mmmm”**is not a valid formatting argument to use within the**TEXT**function. - Because cell
**G2**had been formatted as a text cell before we typed the formula in there. - Because the
**TEXT**function only works when the cell it refers to is recognized by Excel as containing a number or a date. It will give an error otherwise.

#### Quiz : Remove Unwanted Spaces (TRIM, CLEAN)

Q1. What of these statements best describes what the **TRIM** function does to a text string?

- Replaces duplicated space characters with a single space character, but leaves the rest of the text unchanged.
**Removes all spaces from text except for single spaces between words.**- Removes all space characters within the specified text string.
- Removes any leading or trailing spaces from the start or end of a text string, but leaves spaces between words unchanged.

Q2. When might we choose to use the **CLEAN** function on a text string?

- When we want to remove excess spaces from a text string.
- When we want to replace part of a text string with a different text string.
- When we want to convert the text string into a number.
**When we want to remove some non-printable characters from a text string.**

Q3. In the video, we saw in cell **B2** the formula **=TRIM(CLEAN(Data!B2))** being typed and then copied down the column. What would have happened if instead the formula **=CLEAN(TRIM(Data!B2))** was typed and copied instead?

**There would have been no difference to the results. These two formulas would produce identical values in column B.**- An error would have occurred because the
**CLEAN**function needs a direct cell reference as its argument, and cannot take the result of another function as its argument. - The formula would calculate but the results would have been different, since a different set of characters would have been removed.
- An error would have occurred, because the
**CLEAN**function cannot be applied to a text string that has already had the**TRIM**function act on it.

#### Quiz : Diagnostic Tools (ISNUMBER, LEN, CODE)

Q1. Which of these statements best describes the behaviour of the **ISNUMBER** function?

- Checks whether a value is text, and returns
**TRUE**or**FALSE**. **Checks whether a value is a number, and returns TRUE or FALSE.**- Checks whether a reference is to an empty cell, and returns
**TRUE**or**FALSE**. - Checks whether a value is not text, and returns
**TRUE**or**FALSE**.

Q2. In the video, the **CODE** function was applied to a string exactly 1 character in length, since the string was the result of a **RIGHT([cell reference],1)** function. What would happen if we applied the **CODE** function to a string of more than 1 character in length?

**We would get the code result of the first character in the string.**- We would get an error every time.
- We would get an error except when the entire string was the same character, in which case we would get the code result of that character.
- We would get the code result of the final character in the string.

Q3. If cell **A1** contains a text string between 10 and 15 characters long, and we wish to write a formula that returns an identical text string except with the first two and final two characters removed, which of the following formulas will do this?

**=MID(A1,3,LEN(A1)-4)**- =LEFT(RIGHT(A1,LEN(A1)-2),LEN(A1)-2)
- =RIGHT(LEFT(A1,LEN(A1)-2),2)
- =MID(A1,3,99)-RIGHT(A1,2)

#### Quiz : Remove Unwanted Characters (SUBSTITUTE, CHAR, VALUE)

Q1. Now that we know we can remove spaces from a text string in cell **A1** with **=SUBSTITUTE(A1,” “,””)**, why would we still sometimes wish to use the **TRIM** function instead of the **SUBSTITUTE** function for cleaning up excess spaces in cell **A1**?

- We don’t have a reason for needing the
**TRIM**function. Anything the**TRIM**function can do the**SUBSTITUTE**function can do just as easily. It’s only a matter of personal preference. - The
**TRIM**function will remove both excess spaces (ASCII 32) and excess non-breaking spaces (ASCII 160) with a single evaluation, whereas**SUBSTITUTE**would have to be applied twice to eliminate both types of space. **=SUBSTITUTE(A1,” “,””) will remove spaces between words which we may want to keep, whereas the TRIM function will maintain single spaces between words.****TRIM**gives us more flexibility than**SUBSTITUTE**because it can be used to clean up other special characters too besides spaces.

Q2. What will be the difference in results between **=VALUE(A1)** and **=VALUE(VALUE(A1))**?

**=VALUE(A1)**will give a number when cell**A1**contains a value that Excel can interpret as a number, but**=VALUE(VALUE(A1))**will give an error.- These two formulas will return the same results when cell
**A1**already contains a valid number but will return different results otherwise. **=VALUE(VALUE(A1))**will return 0 in situations where**=VALUE(A1)**returns an error.**There will be no difference, these two formulas will always produce the same results.**

Q3. The video described the **CODE** and **CHAR** functions as being like the inverse of each other. If cell **A1** contains a value (text or a number), what will be the result of **=CHAR(CODE(A1))** typically be?

**The first character of the value in A1.**- The value in
**A1**. - Zero.
- An error value.

#### Quiz : Week 3 Final Assignment

Q1. You have received some sales data from your company’s database. The data is in a dirty format and you are required to clean it up in order to perform some analysis on it. Since your company will need to perform this analysis each month with new data, you are to use formulas to clean the data so that the workbook can be easily updated each month with new records from the database. Follow the instructions carefully and answer the questions as you go.

Download and open the assignment workbook. There are 2 versions of the workbook, depending on which version of Excel you are using. If you use Excel for Windows Office 365, 2016 or 2013, or Excel for Mac Office 365, 2016 or 2011, use the Unicode version:**C4 W3 Final Assessment Unicode**XLSX FileDownload file

If you use Excel for Windows 2010 or 2007, use this version:**C4 W3 Final Assessment**XLSX FileDownload file

Look at the **Data** worksheet and **Clean** worksheet. Make sure the **Check Sum** value in **Data** cell **B2** is 95858 (90898 on Excel for Mac 2011). Do not insert any rows or columns into the Data sheet during your work or the Check Sums may give different results than programmed.

Our goal is to populate the columns of the **Clean** worksheet with the data in a form that we can easily analyse. This may look daunting, but we are told by the Data Manager that the raw data always follows a certain pattern. That pattern is:

- Some
**/**characters - The product type sold, with the prefix
**PR:** - Some “
**_**” characters - The Sales Person’s name, with the prefix
**SP:** - The date of the sales, in the format
**yyyymmd**. All sales are from October 2017 - Some non-breaking space characters
- The amount of sales, with the prefix
**$**. All sales amounts are below $10000 - Some space characters
- A final random character that has an ASCII value between 28 and 31.

We will clean up the data one item at a time, by using formulas to make copies of the data to the right of column **B**, with each copy being slightly cleaner than the previous. When we are done we will separate the data into 4 components on the **Clean** worksheet tab, and perform some basic analysis.

Go to the **Data** worksheet. To begin, we want to verify what special characters might be at the end of each data entry. In column **C** (cells **C5:C500**) write a formula using the **RIGHT** and **CODE** functions to return the ASCII code of the final character in each record.

How many records have the character 30 as the final character? (Hint: After writing your formula in column **C**, you can write a formula in cell **C3** that uses the **COUNTIFS** function to count how many times the value in column **C** is 30.)

Q2. Next, we want to remove some of these final characters with the **CLEAN** function. In column **D**, write a formula that applies the **CLEAN** function to the column **B** data. To check if this has been done correctly, enter the value of the Question 2 checksum from cell **D2**.

Enter just the 5 digits (6 on a Mac), without any commas or other thousands separator.

Q3. Now we want to verify that the **CLEAN** function did, in fact, remove all of the unwanted final characters. In column **E**, write a formula just like column **C** that returns the ASCII code of the final character from each record in column **D**. How many records have the character 32 as the final character?

Q4. Our next plan is to get rid of the non-breaking spaces between the dates and the sales amounts by replacing those characters with regular spaces. Which function can we use to change all of the instances of one character from a text string into a different character? (Type only the function name)

Q5. To use the **SUBSTITUTE** function to replace non-breaking spaces with regular spaces, we need a way to refer to both of these character types in the arguments of the **SUBSTITUTE** function. For the space character, we can easily do this by typing a space in between quotation marks like this: **” “**. For the non-breaking space character, we can use the **CHAR** function to return a non-breaking space. If you are using the Unicode workbook, then you can use the **UNICHAR** function instead.

What number is used as the argument of the **CHAR** function to return a non-breaking space?

Q6. In column **F**, use the **SUBSTITUTE** function (with a **CHAR** function in the second argument) to change all of the non-breaking spaces in column **D** into regular spaces. What is the value of the Check Sum in cell **F2** after you do this?

Q7. We could use the **TRIM** function now to get rid of the trailing spaces and the duplicate spaces between the date and price, but think ahead. We still have some “**/**” characters at the start of each record that we don’t want, and we have some “**_**” characters between the product and sales person that we don’t want. It makes sense to wait until after we remove these characters before we use the **TRIM** function, otherwise we may end up just needing to use it twice. Let’s focus on the “**/**” characters at the start of each record next.

In column **G**, use the **SUBSTITUTE** function to remove all of the “**/**” characters from the records in column **F**. However this time, instead of replacing the “**/**” characters with a space, replace them with nothing at all, so that each record will begin with **PR:** once you are done. (HINT: remember we can refer to blank text by typing two sets of quotation marks with nothing in between.)

What is the value of the Check Sum in cell **G2** after you do this?

Q8. Now in column **H**, replace all of the “**_**” characters in our column **G** data with spaces, and then we will be ready to use the **TRIM** function. The reason we want to replace “**_**” with spaces instead of blank text is because having a space character between the Product data and the Sales Person data will make it much easier for us to extract those individual bits of data than if there was no space between them, as we will see later.

Perform this replacement and provide the value of the Check Sum from cell **H2**.

In column **I**, apply the **TRIM** function to our data.

Q9. When you are done, instead of using the Check Sum to see if this has been done correctly, we’re going to use the **LEN** function. In cells **J5:J500**, use the **LEN** function to find the length of each now-trimmed record from column **I**. Cell **J2** contains a formula that is summing all of these lengths. What is the value in cell **J2**?

Q10. In cell **K5**, write a formula to find the ASCII code of the final character of each record and apply it to cells **K5:K500**. If we look at the results we will see that all values range between 48 and 57, which correspond to the digits 0 through 9. That’s good news, since we expect every record to be ending with the sales amount at this point. The digits 0 through 9 for the final character is exactly what we would expect. Performing checks like this as we go can be helpful to make sure we are on the right track.

Now it is time to get rid of the of the prefixes in front of the product names and the Sales Person names. In column **L**, write a formula that refers to our records in column **I** but substitutes the text **PR:** for blank text (remember the double quotation marks). When you are done, enter the value of the Check Sum from cell **L2**.

Q11. In column **M**, further clean the data by substituting **SP:** with blank text. After you do, what is the value of the check sum in cell **M2**?

Q12. Now we need to insert a space between the end of the Sales Person name and the start of the Date. Notice how every date begins with the text **201710**. So we can substitute “**201710**” with ” **201710**” which will place a space in front of the start of the date. Now this particular formula would need to be updated each month, since if we were to use our workbook for data from November 2017, the dates would begin with **201711**. But that is a problem for another day. For now, go ahead and perform this substitution in column **N**. Finally, note that we don’t just want to substitute **2** or **201** because that might alter some of the sales amounts which could contain those numbers. But since we know that all sales amounts are less than $10000, substituting the string **201710** will leave the sales amounts untouched.

If you have done this correctly, you should have the Check Sum value 65831 in cell **N2**.

We now have clean data in column **N**. The only problem is, each record is still a continuous text string. Our next challenge is to separate the 4 pieces of information from each individual record (**Product**, **Sales person**, **Date** and **Sales Amount**). To do this, we are going to use the **LEFT** and **MID** functions to extract the desired pieces from each record, and the **FIND** function to help us enter the arguments for the **LEFT** and **MID** functions. We didn’t include the **FIND** function in the videos. We will use it to locate where a specified text string (in our case, a single space character) occurs within another text string (the complete clean record).

In cell **O5**, write the following formula and then apply it to cells **O5:O500**.

**=FIND(” “,N5,1)**. The 1 at the end of this formula is an optional argument, and it tells the **FIND** function to begin searching for the text string ” ” starting at the 1st character within **N5**. When you are done, write down the value in cell **O2**.

Q13. In cell **P5**, write a formula to find the location of the second space character within column **N**, and apply it to cells **P5:P500**. Hint: you can use the location of the first space that we found earlier as the place to start our search. You will need to add 1 to the previous location so that it doesn’t find the same space again.

What is the value in cell **P2**?

Q14. There is one more space character we need the location of. The third space character in the string is between the date and the Sales Amount. In cell **Q5** we can write **=FIND(” “,N5,P5+1)**, or we can write **=FIND(“$”,N5,1)-1**. They should give equivalent results. Do you see why? The second of these formulas is looking for the location of the first dollar sign, and then subtracting one from that value. Since our data is structured in a way that the third space character is immediately before the **$** character, either formula should work. Apply one of these formulas in cells **Q5:Q500**, and then enter the value from cell **Q2**.

Q15. In column **R** we are going to extract the product names. Since they are at the beginning of the record, we can use the **LEFT** function. The number of characters we want returned by the **LEFT** function will be one less than the location of the first space. Column **O** tells us the location of the first space. Write a formula in cell **R5** to extract the product names and apply it to cells **R5:R500**.

What is the value in cell **R2**?

Q16. In column **S** we will extract the Sales Person. For this, we need to use the **MID** function, and the location of our space characters to determine:

- the start point of extraction, and
- how many characters to extract.

Write a formula in **S5** to extract the Sales Person and apply this down column **S**. What is the value in cell **S2**?

Q17. In column **T** we will extract the date, as text. We can convert it into a value later. Using the same type of formula construction as we did in column **S**, but with references to the second and third space locations instead of the first and second space locations, write a formula in cells **T5:T500** that will return the date as a text string. There is no need to use the **TEXT** function at this point. When you are done, enter the value from cell **T2**.

Q18. We’re almost done! In column **U**, we want the Sales Amount, without the dollar sign. This formula is actually more simple since we do not need to worry about getting precisely the correct value for the length of the string to extract. If we enter a large number in the third argument of the **MID** function, say 99, and there are fewer than 99 characters remaining in the string, then the function will just return what is remaining in the string. This is useful when we want to extract text from the end of a string. Also, since we do not want the $ character returned, we will start our extraction 2 characters after the location of the final space. We would also like the Sales Amount to be returned as a numeric value, rather than a text string. Therefore we will also wrap our **MID** function inside a **VALUE** function.

Q19. In cell **U5**, write a formula to extract the Sales Amount as a numeric value and apply this down column U. What is the value of the sum in cell **U2**?

The final thing we will do on this sheet is to convert the dates in column **T** from text strings to date values recognised by Excel in column **V**. We can do this with the formula **=DATE(2017,10,??)** where the **??** represent the day of the month, which will be either the 7th, or the 7th and 8th characters from column **T**. Use a **MID** function to extract these day characters from column T, and embed that **MID** function within the **DATE** function in place of the **??**. So, the formula at column **V5** will look like **=DATE(2017,10,MID(…))**

You will need to write appropriate arguments inside the **MID** function in place of “**…**“. When you are done, enter the value from the Check Sum at cell **V2**.

We have now finished cleaning our data! If you navigate over to the **Clean** worksheet tab, you should see the columns **B** to **E** filled in, ready for analysis to be performed. Save your work. Well done for completing the assignment for this week.

### Excel Skills for Business: Advanced Week 04 Quiz Answers

#### Quiz : Working with Dates (EOMONTH, EDATE, WORKDAY.INTL)

Q1. If cell **A1** contains a valid date, what will the formula **=EOMONTH(A1,0)** do?

**Return a date equal to the last day of the month in which cell A1 falls, no matter what date is in cell A1.**- Return an error, because
**0**is not a valid second argument for the**EOMONTH**function. - Return a value of zero.
- Return the date from
**A1**, no matter what date is in cell**A1**.

Q2. If cell **A1** contains a date, cell **B1** has the formula **=A1+14** and cell **C1** has the formula **=WORKDAY.INTL(A1,10)**, which of these statements best describes the difference between the formulas in cells **B1** and **C1**?

**B1 will sometimes equal C1 and sometimes give a date later than C1.****B1**and**C1**will always give the same result as each other.**B1**will sometimes equal**C1**and sometimes give a date earlier than**C1**.**B1**and**C1**will always give different results to each other.

Q3. If cell **A1** contains a valid date from the month of August 2017, what will the formula **=EDATE(A1, -1)** do?

- Return an error, because
**-1**is not a valid second argument for the**EDATE**function. **Return a date that is 31 days earlier than the date in cell A1.**- Return the date
**31 July 2017**. - Return the date from
**A1**.

#### Quiz : Loan Schedule (PMT, EDATE)

Q1. A loan for $1000 accrues 10% interest per annum and is paid back over 5 years with 5 equal payments at the end of each year. The total amount of repayments will be **=PMT(10%,5,1000)*5**. If instead the loan is paid back with equal payments at the end of each month instead of each year, which formula could give the total amount of repayments?

- =PMT(10%,5*12,1000)*5*12
- =PMT(10%/12,5,1000)*5
**=PMT(10%/12,5*12,1000)*5*12**- =PMT(10%/12,5*12,1000)*5

Q2. What does the optional **[FV]** argument do in the **PMT** function?

**Allows us to specify the balance to be attained after the last payment is made**- Allows us to specify if payments are made at the start or end of each period
- Allows us to specify the total amount that the series of future payments is worth now
- Calculates the future value of an investment with constant periodic payments or a single lump sum payment and a constant interest rate

Q3. We have a loan for $1000 at 5% interest per annum and a 5 year term that we repay with constant payments each month. If we reduce the term of our loan schedule but leave the other variables the same, we will pay more per month. Will our total projected interest paid over the life of the loan go up, go down or stay the same?

- It depends on what day of the month payments are made.
- It will go up.
- It will stay the same.
**It will go down.**

Q4. Which of the situations would be best suited to use with the **PMT** function?

**We want to calculate the fixed monthly repayments of a car loan.**- We want to calculate how much to deposit now in to a bank account so that it will grow to $1000 in 5 years due to interest alone.
- We want to calculate the interest due in the specified month of a 10 year loan that has fixed monthly repayments
- We want to calculate the value of a bank account in 5 years that we make constant monthly deposits in to.

#### Quiz : Net Present Value and Internal Rate of Return (NPV, IRR)

Q1. Cell **A1** contains a discount rate of 5% per annum, cell **A2** contains an initial investment of -$1,000 and cells **A3:A5** contain positive cashflows from the end of years 1, 2 and 3 of $400 each. Which of these statements best describes what the value returned by the following formula represents?

**=NPV(A1,A3:A5)**

- The net present value at the start of year 1 of all of our cashflows (in and out) related to this investment.
**The net present value at the start of year 1 of the three future payments of $400.**- The net present value at the end of year 1 of the three future payments of $400.
- The extra amount we could pay in addition to the initial $1000 for these future cashflows whilst still earning a rate of return equal to or greater than our discount rate

Q2. If cell **A1** contains a negative cashflow and cells **A2:A4** contain positive cashflows, what can be said about the result of this formula?

**=IRR(A1:A4)**

**The result will be a value greater than 0 only when the sum of cells A1:A4 is greater than 0.**- The result will always be a value greater than 0%.
- The result will be an error if the sum of cells
**A1:A4**is less than 0. - The result will be an error since the
**IRR**function also needs an interest rate argument.

Q3. If cell **A1** contains a discount rate of 10% per annum, and cells **A2:A121** contain a stream of 120 consecutive monthly payments received over a 10 year period, how could we most easily and accurately find the **Net Present Value** of these payments from the options below?

- Calculate
**=NPV(A1*12, A2:A121)** **Calculate =NPV(A1/12, A2:A121)**- Calculate
**=NPV(A1, A2:A121) / 12** - Calculate
**=NPV(A1, A2:A121) * 12**

#### Quiz : Depreciation Functions (SLN, SYD, DDB)

Q1. An asset worth $10,000 is depreciated over 4 years using the **Sum of Years Digits** method, with no salvage value. What is the amount of depreciation in the first year?

- $5,000
**$4,000**- $3,000
- $2,500

Q2. Which of the three functions (**SLN**, **SYD**, **DDB**) only requires 3 arguments?

**SLN**- SYD
- They all require 4 or more arguments.
- DDB

Q3. An asset with a salvage value of $1000 is depreciated using the **DDB** function. If the **DDB** function returns a value of zero for a specified period number, what does this mean?

- That the original cost input for the asset was also $1000.
- The period number is equal to the specified life of the asset.
- The period number is larger than the specified life of the asset.
**Depreciation from past periods has already brought the asset value down to the salvage value, so there can be no more depreciation.**

#### Quiz : Week 4 Final Assignment

Q1. This week’s assignment will have you reproducing some of the calculations and schedules you studied in the videos. Follow the instructions carefully, write your formulas in the green cells and answer the questions as you go. Do not insert or delete any rows or columns because then the references in the instructions may not align with your worksheet.

Download and open the assignment workbook.**C4 W4 Final Assessment**XLSX FileDownload file

Look at the **Calcs** worksheet. Work through the sheet from top to bottom, answering the questions as you go.

## Section 1 – Dates

In this section, we will focus on using formulas to take a given input date and return an end of quarter date. This is a useful skill when building models that are based on quarterly time periods.

Cells **F5:T5** contain dates from the years 2010 to 2020. Your task is to write a formula in cells **F6:T6** that will return the end-of-quarter date for the calendar quarter in which the date from row 5 falls. For example, if cell **F5** contained **11-Jan-2018**, the correct end-of-quarter date would be **31-Mar-2018**. Space for helper cells is provided if you need it, but it can certainly be done neatly without helper cells. Use the **EOMONTH** function, so that **F6** will have the formula **=EOMONTH(F5,[??])**. You will need to write an expression in place of **[??]** that returns either 0, 1 or 2 depending on what is an appropriate value to get to the end of the quarter.

When you are done, submit the value of the Check Sum from cell **D6**.

Q2. In cells **F18:T18**, write a formula that returns the previous end of quarter date for each of the dates in cells **F5:T5**. For example, if cell **F5** contained **11-Jan-2018**, **F18** should give the value **31-Dec-2017**. When you are done, submit the value of the Check Sum from cell **D18**.

## Section 2 – Loan Schedules

Q3. In this section, we are going to build a loan schedule that has some flexibility for changing input values. To begin, we are going to model a 3-year loan with constant monthly scheduled payments. The outline of the schedule has been provided at cells **A25:H62**. The loan amount is for $50,000 and the interest rate is 9.75% per annum. Interest is calculated each month, with the monthly rate of 1/12th of the annual rate applied to the monthly opening balance. Scheduled Payments are made on the last day of each month. The loan must be paid down to a balance of $0 at the end of 3 years.

To begin, write a formula in cell **F24** using the **PMT** function to calculate the monthly scheduled payment amount necessary for this loan. Assume for now that **Additional Payments** will be zero. What is the monthly payment amount?

- -1,607.50
- -1,668.11
- -1,594.54
- -5,052.39

Q4. Next, complete the modelling of the green cells **C27:H62**, using the scheduled payment you calculated at cell **F24** in each of cells **F27:F62**. Assume all **Additional Payment** cells in column **G** are zero and assume all **Drawdown** cells in column **D** after the provided 50,000 are zero. The **Closing Balance** formula has been filled in for you. If you have done this correctly, the **Closing Balance** at cell **H62** should be zero. What is the closing balance as of **20 Feb 2019**? Submit your answer without a dollar sign or thousands separator, rounded to two decimal places.

HINT: The **Interest** formula in column **E** should be the **Opening Balance** multiplied by the interest rate divided by 12, and the **Opening Balance** should be the previous period’s **Closing Balance**.

Q5. What is the total interest paid on this loan? (This should be the value in cell **E64**). Submit your answer without a dollar sign or thousands separator, rounded to two decimal places.

Q6. Now, let’s assume that we had some extra funds available at the end of 2018, and decided to make an additional payment of $10,000 on 20 Jan 2019. Enter **-10000** at cell **G36**, including the negative sign. Because of this additional payment, our loan is now paid off before the end of period 36. To compensate, we are going to adjust the scheduled payment amount that is used for all months AFTER January 2019, so that the closing balance still becomes zero on 20 March 2021.

In cell **F37**, write a new formula using the **PMT** function to calculate a new constant payment amount for periods 11 to 36, and apply this amount to cells **F37:F62**. What is this new monthly payment amount? Submit your answer including the negative sign, rounded to two decimal places. As a hint, the answer is between -$1100 and -$1200.

Q7. What is the new total interest amount shown at cell **E64**, after we make the additional payment in January 2019 and after we adjust the scheduled payments for February 2019 to March 2021? Submit your answer without a dollar sign, rounded to two decimal places.

Q8. Section 3 – Net Present Values and Internal Rates of Return

For this section, look at rows **67:107**. The schedule at rows **72:99** shows a 24-month loan from the point of view of the bank making the loan. The interest column has not been completed. For this loan, the bank lends (invests) $10,000 at the end of period 0 (20 March 2018), and receives payments of $450.00 on the 20th of each month for the next 24 months.

In cell **E101**, use the **IRR** function to find the internal rate of return to the bank for the cashflows in cells **E73:E97**. In cell **E102** express this answer as an annual rate (calculate the annual rate as 12 times the monthly rate). What is the annual internal rate of return?**1 point**

- 7.50%
- 7.75%
- 8.00%
- 8.25%

Q9. If the bank was to charge interest on this loan using an interest rate equal to the calculated internal rate of return, the closing balance should come out to be $0.00. Try it and see, by filling in the **Interest** cells at **D74:D97** using the monthly internal rate of return as the interest rate, and calculating the interest amount as the **Opening Balance** for the month multiplied by this monthly rate.

Now let’s assume that the bank is a little more precise in their interest calculations. Use the **Annualised IRR** result in cell **E102** as the annual interest rate, but now calculate the interest each month as:

(Opening Balance) * (Annual Interest Rate) * (Days in Period) / 365

The **Days in Period** can be calculated as the **Period Payment Date** minus the previous **Period Payment Date**. Update your formula in cells **D74:D97** to use this new approach. The **Closing Balance** after period 24 should now be an amount between -2.00 and -3.00 dollars. What is the final **Closing Balance**? Submit your answer without a dollar sign, rounded to two decimal places.

Q10. The last thing we will do in this section is to calculate the Net Present Value of this loan to the bank. Ignore the interest calculations and just focus on the cashflows to the bank presented at cells **E73:E97**.

The bank is able to borrow funds on the wholesale lending market at a cost of 4.00% per annum. If the bank is able to make loans to customers that have an IRR of above 4.00% per annum (such as this one), then that should be a positive NPV situation for the bank.

In cell **E105**, calculate the NPV as at the end of Period 0 of the cashflows in cells **E73:E97** at a 4.00% per annum discount rate. Submit your answer without a dollar sign, rounded to two decimal places.

HINT: Remember to convert the discount rate to a monthly amount, since the periods between cashflows are monthly periods.

Q11. Section 4 – Depreciation

In this section, we are going to calculate a depreciation schedule using the Double Declining Balance method, but we are going to calculate it two different ways.

The first way will use the **DDB** function we learned about, and the second way will recreate the same schedule using a first-principles approach. The reason why a first-principles approach can be useful in modelling is because it more easily allows for additions to the Asset Base after depreciation of the Asset Base has already begun.

In cells **F122:T122**, write a formula using the **DDB** function and the provided assumptions to calculate the depreciation amount each period. Make sure there is a minus sign at the front of the formula so that the result each period is a negative value.

What is the sum of all the depreciation from years 10 to 15 inclusive? Submit your answer without a dollar sign, rounded to two decimal places. Your answer should be a negative value between -800 and -1000.

Q12. Now we will look at how to reproduce these results without the **DDB** function. Ignore the **Additions in Year** at row 136 for the moment, and focus on writing a formula for cells **F137:T137**. Each year, if the Salvage Value is 0 as per this example the depreciation amount should be

-1 * (Opening Balance) * (DDB Factor) / (Life of Assets)

Write this formula in cells **F137:T137** and verify that it produces the same results as method 1 in row 122.

Now, assume that at the end of year 5, new assets worth $1500 are acquired and to be depreciated using the same assumptions as the original $5000 of assets. Enter this addition to the asset base in the relevant column of row 136. This addition will mean more depreciation in all of the years after year 5.

What is the new value for the sum of depreciation in years 10 to 15? Submit your answer without a dollar sign, rounded to two decimal places.

Save your work. Well done.

### Excel Skills for Business: Advanced Week 05 Quiz Answers

#### Quiz : INDIRECT

Q1. What style of referencing does the **INDIRECT** function use by default?

**A1**- Absolute
- R1C1
- Relative

Q2. The reference text for the **INDIRECT** function can contain which of the following elements?

*(Multiple answers possible, partial credit awarded)*

**A formula****A1-style reference****A reference to a cell as a text string****R1C1-style reference****A name defined as a reference**

Q3. What would Excel return if we typed **=INDIRECT(G14)** in cell **C11**?

- #DIV/0!
**#REF!**- Kin Tiv

Q4. What would Excel return if we type **=INDIRECT(C14)** in cell **C11**?

**Kin Tiv**- #REF!
- #DIV/0!

Q5. The formula **=SUM(INDIRECT(C5))** has been entered in cell **C6**, where **August** is a named range for all the prices of properties sold in August. What other formula would return the same value (although it might not be as flexible)?

- =SUM(INDIRECT(August))
- =INDIRECT(SUM(August))
- =INDIRECT(August)
**=SUM(August)**

#### Quiz : ADDRESS

Q1. What will the formula **=ADDRESS(4,6)** return?

**$F$4**- F4
- £225,949,657.00
- Chiltern

Q2. What will the formula **=ADDRESS(4,6,4)** return?

- $F$4
- Chiltern
**F4**- £225,949,657.00

#### Quiz : Introduction to OFFSET

Q1. What will Excel return if we type **=OFFSET(B10,2,2)**?

- 0.88%
- 0.93%
**1.06%**- 1.30%
- 1.46%

Q2. What will Excel return if we type **=OFFSET(B10,-2,2)**?

**0.66%**- 0.73%
- 0.88%
- 0.93%
- 1.06%

Q3. What would Excel return if we type the formula **=OFFSET(B5,ROUNDDOWN(D2-80.01,0),2)**?**1 **

- 83.01 – 84%
- 0.73%
- Big Bankz
**0.66%**- 84.01 – 85%

#### Quiz : Week 5 Final Assignment

Q1. This week’s assignment will test your ability to use the **INDIRECT**, **ADDRESS**, **OFFSET** and **INDEX** functions in ways focussed on extracting values from tables of data. Follow the instructions carefully and apply what you have seen in the videos and you should do fine. Write your formulas in the yellow and green cells and answer the questions as you go. Do not insert or delete any rows or columns because then the references in the instructions may not align with your worksheet, and the Check Sum calculations may not give the intended results.

#### Part 1

Download and open the assignment workbook.**C4 W5 Final Assessment**XLSX FileDownload file

Go to the **Part1** worksheet. This section will focus on extracting exchange rates from a provided year, month and currency. Look at the sheets **2012** through **2016** to see where the data is and the layout of the data. Note that the sheets are designed identically and that cells **B2:K13** of each sheet has been given a defined name called **Rates_XXXX** where **XXXX** is the year. When you are done, go back to the sheet **Part1**.

The blue cells **J5**, **J7** and **J9** contain dropdown lists where we can select which value we want to look up. The existing setting should be **2016**, **December**, **SGD**. If it isn’t, change those cells to those values. Now we are going to write formulas in the green cells, ultimately arriving at 3 different ways of using **INDIRECT** to extract the exchange rate. Our formulas need to be flexible so that if the blue cell input values change, our answer will change too.

We will start with a few helper cells – intermediate calculations that will be used by other calculations. In cells **L7** and **L9**, write a formula (using the **MATCH** function is recommended) to return the position / number of the chosen Month (**J7**) and Currency (**J9**) from the list of Months and Currencies. You should get **12** for **December** and **10** for **SGD**.

In cell **L11**, use the **ADDRESS** function and references to the helper values in cells **L7** and **L9** to return the cell address (don’t worry about the sheet name, that will come later) of the chosen month/currency combination from one of the yearly data sheets. Because our data starts at cell **B2**, meaning the first month in the list is in the 2nd row, and the first currency in the list is in the second column, we will need to add 1 to each of the first two arguments in our **ADDRESS** function. When you are done, you should have the value **$K$13** in cell **L11**. We will use this in the next formula we write.

In cell **J14**, write a formula using the **INDIRECT** function, plus references to

- the year in cell
**J5**to specify the worksheet and - the address in cell
**L11**to specify the cell address

that will return the chosen exchange rate. Remember to put an exclamation point in between the two references. (HINT: you will need to use the string **&”!”&** somewhere in the formula)

Next, in cell **J15**, let’s achieve the same thing but using an **INDEX** function instead of our **ADDRESS** function helper cell. Here we are going to take advantage of the name **Rates_2016** that has been applied to cells **B2:K13** of the **2016** sheet (and similarly for the other years). If the first argument of our **INDEX** function is **INDIRECT(“Rates_”&J5)** that will reference cells **B2:K13** of our chosen year as the array to index in to. We can then refer to the helper cells at **L7** and **L9** to specify the row and column we want within that array. With this knowledge, write a formula beginning with **=INDEX(INDIRECT(** to return the exchange rate for the chosen dropdown values.

For the last method in cell **J16**, we will use **OFFSET** and **INDIRECT**. If the first argument of our **OFFSET** function is **INDIRECT(J5&”!A1″)** that will reference cell **A1** of our chosen year sheet, and we can then offset that cell by the desired number of rows and columns specified in cells **L7** and **L9**. Write a formula beginning with **=OFFSET** that will return the exchange rate.

When you are done, you should have the same value in all of the cells **J14:J16**, and they should be dynamic. Change the blue cell inputs to be **2015**, **March**, **CAD** and check that your calculated exchange rates update to new values (they should still all be the same). Submit the answer from the Check Sum at cell **J17** when **2015**, **March**, **CAD** are the inputs.

Q2. Our next challenge is to write a formula in cell **J23** that can be dragged across and down **J23:M25** to return the exchange rate for the desired currency (**J20**), year (column **I**) and month (row **22**), and that will update when a new currency is chosen at **J20**. Be careful though – the years and months needed will change depending on what currency is selected!

Start by filling in the helper cell at **L20**, and then use whichever **INDIRECT** method you most prefer in cells **J23:M25** to fill in the table. Make sure it updates when the currency at cell **J20** changes. Use this table to answer the next four questions.

Set cell **J20** to **AUD**. What is the value of the check sum in cell **J27**?

Q3. Set cell **J20** to **EUR**. What is the value of the check sum in cell **J27**?

Q4. Set cell **J20** to **GBP**. What is the value of the check sum in cell **J27**?

Q5. Set cell **J20** to **JPY**. What is the value of the check sum in cell **J27**?

Q6. Part 2

Q7. You’re done with **Part 1**! Now navigate to the worksheet **Part2**. In columns **B:D** you will see NASDAQ data for Microsoft from the years 2013 to 2016, with the date, price and volume traded.

This data is presented with the most recent dates at the top. The first thing we want to do is flip this around, so the oldest date is at the top and the most recent at the bottom. This is a pretty common problem when working with dated data sets in Excel. Because we already know that it is sorted newest to oldest and not completely random, we can use a simpler set of formulas to sort this oldest to newest than if it was completely random. We will start by filling in some helper cells in column **F**, and the rest should easily fall into place.

In cell **F5**, write **=COUNT(B5:B1012)**. This will tell us how many entries there are. Then in **F6**, write **=F5-1** and apply that formula down to cell **F1012**. We should have a list of numbers from 1008 to 1 in decreasing order.

In columns **G**, **H** and **I**, use either the **INDEX** function or **OFFSET** function to write a formula that references columns **B**, **C** and **D** respectively, plus the helper value in column **F**, that will return the data sorted in an oldest to newest format. When you are done, submit the value of the Check Sum at cell **H3**.

For the next 5 questions, we are interested in monthly summaries of the daily data, organised by calendar month. We will make reference to our newly sorted oldest to newest data. Beginning at column **K**, rows **5:52** contain 48 rows (1 for each calendar month) that we will fill in with aggregate data from that particular month. There are some clever ways we can find this using **INDEX** or **OFFSET**. In both cases, we are going to benefit from helper cells (columns **M** and **N**) that tell us the position (from 1st spot to 1008th spot) of the start and end of each month in the daily data. To begin, fill in the helper cells as follows: In cell **M5**, type 1. In cell **N5**, type **=MATCH(L5,$G$5:$G$1012,1)** and apply this down the column. In cell **M6**, type **=N5+1** and apply this down the column. Can you see what the **MATCH** formula is doing and why this works? Cells **M7:N7** should contain 41 and 60. This means that the first March 2013 data point is the 41st in the list of **G5:****G1012**, and the final March 2013 data point is the 60th in the list of **G5:G1012**. We will need these position numbers for the next step to make our formulas easier.

In column **O**, write a formula using **OFFSET** that returns the sum of prices for the designated month. Remember that **OFFSET** can return a range of cells. If that **OFFSET** function is then wrapped by a **SUM** function, we will get the sum of the entire range that was returned by **OFFSET**. Start by writing in cell **O5** “**=SUM(OFFSET(H$5,**” and fill in the rest of the arguments and drag the formula down. Use the helper columns and **M** and **N** for the second and fourth arguments. The third argument will be zero. When you are done, submit the value of the check sum at cell **O3**.

Q8. Before we move on, consider this. We could have performed our sum-by-month calculation in column **O** using two **SUMIFS** functions, with one subtracted from the other (e.g. the sum of prices from March 2013 is the sum of all prices <= 31 March 2013 minus the sum of all prices < 1 March 2013. However, the advantage of doing it the way we did with **OFFSET**, or the way we will do next with **INDEX**, is that it is much faster to calculate than **SUMIFS** over large data sets, and gives us the flexibility to calculate other items like averages and not just sums. Finding an average value over a month, especially when we don’t know how many items are in each month, would be much more complex without using our **OFFSET** or **INDEX** approach. Now let’s move on…

In column **P**, we are going to do a similar thing, but this time finding the **AVERAGE**. We will also use **INDEX** instead of **OFFSET**. We are going to take advantage of the fact that if an **INDEX** function returns a single cell, we can put that **INDEX** function on either side of a colon “**:**” and Excel will treat the **INDEX** function like a cell address rather than a cell value. In other words, the construction **=AVERAGE(INDEX(…):INDEX(…))** will give us the average of the range that starts and ends at the cells designated by the two **INDEX** functions. This is just another way of achieving what we did in the previous column with **OFFSET**.

In cell **P5**, write a formula with the form **=AVERAGE(INDEX(…):INDEX(…))** and apply it down the column to calculate the average price for each month. Remember that a formula **=INDEX([column of cells],[position])** will return a single cell from the column, in the designated position. When you are done, submit the value of the check sum from cell **P3**.

Q9. In column **Q**, write a formula (using either **OFFSET** or **INDEX** as you prefer) to calculate the average volume of each month. When you are done, submit the value of the check sum from cell **Q3**.

Q10. In cell **T6**, use a **MAX** function to find the largest monthly average volume, and then in **T7** write a formula (we suggest using **INDEX** and **MATCH**) to find the month that this maximum average volume occurs in. Submit as your answer the month and year, typed as “month year”. For example, if the maximum value was in cell **Q7**, you would type “March 2013” (without quotations). Use the English name for the month.

Q11. In cell **T9**, use a **LARGE** function with 20 as the second argument to find the 20th highest average monthly price. In cell **T10**, write a formula to identify what month this occurs in. Submit as your answer the month and year, typed in the format of “March 2013” just like the previous question.

Save your work. Well done.

### Excel Skills for Business: Advanced Week 06 Quiz Answers

#### Quiz : Dashboard Design

Q1. Why is it important to use segmentation in your dashboard?

- It adds to the visual appeal of the dashboard.
- It allows us to include more information on the dashboard.
**It allows us to display information in groupings.**

Q2. Which of the following options are useful when laying out the dashboard initially?

*(Multiple answers possible, partial credit awarded)*

- Styles.
**Custom shapes.****Background fill.**- Tables.

Q3. Which of the options below will select a group of objects?

*(Multiple answers possible, partial credit awarded)*

**Hold down the Ctrl key and click on each object.****Hold down the Shift key and right-click on each object.**- Hold down the
**Shift**key and click on the first and then the last object. **Go to Find & Select, click on Select Objects to activate object selection.**

#### Quiz : Final Assessment

Q1. The final assignment contains two parts.

**Part 1**

Download and open the workbook and go the **Data1** worksheet. **C4 Final Assignment v2**XLSX FileDownload file

Here you have some weather data for 50 different U.S. cities covering 12 months from January to December. Columns **C** to **N** contain the temperature information where:

- ‘M’ stands for Month, and the M is followed by a number between 1 and 12 denoting the month of January to December. These are always in order of 1 to 12.
- ‘H’ stands for High, and the H is followed by a number that is either 2 or 3 digits, giving the average high temperature (Fahrenheit) for that city for that month.
- ‘L’ stands for Low, and the L is followed by a number that is either 1 or 2 digits, giving the average low temperature (Fahrenheit) for that city for that month.

The data is presented to you in alphabetical order by City.

In the **SummaryHigh** sheet extract the monthly high figure data (all the digits between the ‘H’ and the ‘L’) using appropriate Excel functions.

For your workings and calculations, you may use the remainder of the **Data1** worksheet, the **Workings_1** worksheet, or add new worksheets as you need.

**Question**: Which of the following combination of functions could be used to achieve this task (although you may have used a different solution)?

- SUBSTITUTE, LEN, RIGHT
- FIND, MID
- RIGHT, LEN
- LEFT, FIND

Q2. Observe that the numbers are aligned to the left, this is because they are still text values. Alter the function used so the results are numeric (you will get some errors, ignore them for now.)

**Question:** Which function converts numbers stored as text to numeric values (without losing decimal place information)?

*Type in the function name only.*

Q3. Converting the values to numeric caused several #Value errors. If you look carefully you will notice that the data has used a Ø instead of a 0. Adjust the formula to replace the Øs with 0s.

In row 55 calculate the highest temperatures for each month.

**Question:** What is the highest temperature for March?

*Type in your number without spaces and special characters.*

Q4. There are still two #VALUE errors for Portland. Identify what is causing the errors and adjust your formulas to correct them.

**Question:** Which function did you need to add?

- TRIM
- SUBSTITUTE
- CLEAN
- TEXT

Q5. If you have successfully cleaned the data, you should now see the completed checksum values in row 57 of the **SummaryHigh** sheet. To help you self-assess if you have done the work correctly, the **SummaryHigh Check Sum** for January should be 259045.

**Question: **What is the CheckSum for June?

*Type in your number without spaces and special characters.*

Q6. In the **SummaryLow** sheet extract the monthly low figure data (all the digits after the ‘L’ using appropriate Excel functions.

For your workings and calculations, you may use the remainder of the **Data1** worksheet, the **Workings_1** worksheet, or add new worksheets as you need.

**Question:** Which of the following combination of functions could be used to achieve this task (although you may have used a different solution)?

- LEFT, FIND
- FIND, SUBSTITUTE
- RIGHT, LEN, FIND
- MID, LEN, CHAR

Q7. As you did with the SummaryHigh figures, adjust your calculations so that they remove any unwanted characters, replace the Øs with 0s and convert the results to numerics, to get a completely clean data set.

To help you self-assess if you have done the work correctly, the SummaryLow Check Sum for January should be 172862.

**Question:** What is the CheckSum for August?

Q8. Now select the **MonthSorted** worksheet. Your task for the next few questions is to write some formulas so that when a month is selected from the drop down box at cell **C3**, the green cells in columns **B** and **C** will show each city and its High temperature for that month, but automatically sorted from largest value temperature to the lowest value temperature. So, for example, when February is selected Miami should be at the top and Minneapolis should be at the bottom.

If two or more cities have the same temperature, sort those equal cities by alphabetical order (A at the top, Z at the bottom).

Use columns **K-N** of the **MonthSorted** worksheet for your workings, and report the final answers in columns **B** and **C**.

In **K2** use a function to get the appropriate column number from which to select the data for the selected month. For example, if the selected month is January, the formula should return 1.

**Question:** Which function is best suited to this task?

- VLOOKUP
- INDEX
- MATCH
- OFFSET

Q9. In column **K** use a formula (with the help of the value in **K2**) to retrieve the high temperature for the corresponding city in column **J** and the selected month.

**Question:** Which function would be best suited to this task?

- INDEX
- VALUE
- INDIRECT
- MATCH

Q10. Where the temperatures are the same we need to sort by City alphabetically, we can take advantage of the fact the cities are currently correctly sorted so the higher the row number the further down they should go. In column **L** calculate an adjusted value by subtracting 1000^{th} of the row number from the temperature (i.e. temperature – row number/1000).

Change the selected month to March, then in a blank cell calculate the sum of the adjusted values.

**Question:** What is the sum to 3 decimal places?

*Enter the answer below using a dot for the decimal point – e.g., 1234.123*

Q11. In column **M** use an appropriate function to rank each adjusted value against the rest of the set.

**Question:** What is the sum of the correct rankings for Las Vegas, Los Angeles & Louisville (for March)?

- 47
- 48
- 49
- 50

Q12. In column **N** calculate the sort order using the numbers in column **A** and the rankings in column **M**. In other words in **N5** you should end up with the value 24 as the 24^{th} value in the list (Miami) has the highest temperature for March.

**Question:** What is the sum of the rankings for Memphis to Portland (**N27:N39**)?

Q13. Use the calculated sort order to populate columns **B** and **C** with the cities and temperatures in the correct sort order.

**Question:** Which of the following functions would correctly retrieve the city with the highest temperature:

- =INDEX(J$5:J$54,$N$5)
- =INDEX(J$5:J$54,MATCH(A5,$N$5:$N$54,0))
- =INDEX(J$5:J$54,MATCH(A5,$N5:$N54,0))
- =INDEX(J$5:J$54,$N5)

Q14. What is the Check Sum for the **City** column (in **B56**)?

Q15. Congratulations, that is the end of Part 1.

**Part 2.**

Select the **Scenarios** worksheet. The information about the task is presented in the worksheet. Read it, and when you are ready, write the formulas you need in order to populate the green cells at **F54:G83**.

When you are done, answer the questions below.

Make sure to submit all of your answers rounded to the nearest dollar (which should be how they are displayed by Excel), but without any dollar signs or other punctuation.

For example, if you were asked for the value in cell **G54**, which is $13,171.74, you would write “13172”.

(Hint: This is indeed the correct value for cell **G54**. Use it to check your work.)

**Question:** What is the value of the **Initial Deposit** for Account Number **2**?

Q16. What is the value of the **Initial Deposit** for Account Number **5**?

Q17. What is the value of the **Final Account Balance** for Account Number **11**?

Q18. What is the value of the **Final Account Balance** for Account Number **20**?

Q19. What is the sum of the **Initial Deposits** for all of the accounts? (i.e. the value of the Check Sum at cell **F85**)

Enter whole numbers only, without a currency symbol or thousands separator.

Q20. What is the sum of the **Final Account Balance** for all of the accounts? (i.e. the value of the Check Sum at cell **G85**)

Enter whole numbers only, without a currency symbol or thousands separator.

#### Get All Course Quiz Answers of Excel Skills for Business Specialization

Excel Skills for Business: Essentials Coursera Quiz Answers

Excel Skills for Business: Intermediate I Coursera Quiz Answers

Excel Skills for Business: Intermediate II Coursera Quiz Answers

Excel Skills for Business: Advanced Coursera Quiz Answers