Excel Skills for Business: Advanced Coursera Quiz Answers

All Weeks Excel Skills for Business: Advanced Coursera Quiz Answers

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 AssessmentXLSX 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 AssessmentXLSX 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 UnicodeXLSX FileDownload file

If you use Excel for Windows 2010 or 2007, use this version:C4 W3 Final AssessmentXLSX 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:

1. Some / characters
2. The product type sold, with the prefix PR:
3. Some “_” characters
4. The Sales Person’s name, with the prefix SP:
5. The date of the sales, in the format yyyymmd. All sales are from October 2017
6. Some non-breaking space characters
7. The amount of sales, with the prefix $. All sales amounts are below $10000
8. Some space characters
9. 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:

1. the start point of extraction, and
2. 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 AssessmentXLSX 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 AssessmentXLSX 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

1. the year in cell J5 to specify the worksheet and
2. 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 v2XLSX 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