OVERVIEW

This first workshop is designed to introduce you to some useful features and formulas in Microsoft Excel.

Some of the features and formulas you may have seen before. Others may be new to you. The guided exercises point you towards how these different elements can be combined to produce different types of analysis.

You should also pay attention to workbook design and think about how workbook design and layout can influence the likelihood of errors and the ease of understanding of how a spreadsheet operates.

For this week’s task you will work through the exercises in the workshop and share your ideas, questions, and solutions with each other. You are encouraged to seek out solutions through the Excel help function and formula builder tools. You are also encouraged to explore trial and error. You will also notice that, in Excel, there is often more than one way to solve a problem!

 OBJECTIVES

By the end of this tutorial you should be able to:

.       Describe and apply key ideas in Excel worksheet design

.       Distinguish between and apply relative and absolute formulas

.       Create cell names and explain their benefit for worksheet design and error reduction

.       Create data tables and describe their benefits for worksheet design

.       Create formulas that address a range of calculation and analysis needs

.       Apply graphical techniques to data

.       Create a pivot table

显 KEY EXCEL CONCEPTS AND FORMULAS

① DATA AND FORMULAS

The data in the Q1 tab of the worksheet contains sales data for a number of retail outlets of a business during the first week of July.

You need to complete the following:

a)    Enter the GST rate of 10% in B3

b)    Create a formula in G11 that will calculate the GST that needs to be added to the sale price.

c)    Create a formula in H11 that will calculate the total amount of the transaction

d)    Copy the formulas in G11 and H11 down to the bottom of the sales data range

e)    The business would like to be able to analyse sales based on the day of the week they occur. In column I, use a formula that will return a number for the day of the week (e.g. 1 = Sunday, 2 = Monday…)

The business would like to know some summary information about the sales performance for the week. In particular, they would like the following pieces of information:

.       Total $ of sales (before GST)

.       Number of sales transactions completed in each store

.       Sales revenue for each store

.       Sales revenue for each store on each day

.       Average sales $ value for each store

.       Highest sales $ value for each store

TO DO:

The following formulas may be useful:

.     SUMIF – sums items that meet ONE specified criterion

.     SUMIFS – Sums items that meet ONE OR MORE criteria

.     AVERAGE – Returns the average of a set data block

.     AVERAGEIF – Returns the average of a data block that meets ONE specified criterion

.     AVERAGEIFS – Returns the average of a data block that meets ONE OR MORE specified criteria .     COUNTIF – Counts the number of values that meet a specified criterion.

a CREATING AND USING TABLES

In this question we will use the data from Q1 but reformat it into a table.

Create the table

a)    Select the range of cells from A10:H60

b)    From the INSERT menu select the TABLE button

c)    Check that the popup box is completed as shown below

d)    Click on “OK”

e)    Select the table (A10:H60) and in the space indicated type Sales

This gives the table the name of Sales. This table name can be used as a reference point when building formulas.

   What happens to the data in the range once you click on “OK” in step (d) above?

Naming Cells

We can also make formula construction and cell referencing easier by giving cells or ranges of cells a name. For

example, instead of referring to the GST rate in B3 by its cell reference in formulas we could give it a more intuitive and user friendly name. To do this we need to:

   Select cell B3

From the FORMULAS menu click on “Define Name”

When you do that the following pop-up appears

The GST_RATE is the name given to the item in B3. This is taken from the adjacent cell, A3. There can be no spaces in a cell name, so the underscore is inserted between words. You can include comments that describe the range / named   item. These can be useful for users / other developers to understand what the range refers to.

Using Named Cells

       We will now see how the named cells make things easier.

a)    Go to cell G11

b)    We will recalculate the GST amount, so start entering the formula by pressing ‘=’

c)    Click on F11 (the sale amount) then press * for the multiplication

Notice How Excel uses the column name [SALE AMOUNT] instead of F11. This is because we have formatted   the block as a table and Excel uses the label of columns in the formulas. Again, this makes formula construction more intuitive.

d)    Start typing the name of the GST_Rate cell

e)    The pop-up item ‘GST_RATE’ appears and by clicking on this it will be included in the formula.

f)     Your formula should look like the following: =[@[SALE AMOUNT]]*GST_RATE, which consists of the column in the table for sale amount and the item it is being multiplied by (GST_Rate)

g)    When you press “Enter” the formula is automatically applied to all rows in the table.

h)    Repeat the process for the calculation of the Total Amount. The formula should look like: =[@[SALE AMOUNT]]+[@GST]

Summarising the Data

If we wanted to calculate the total dollar value of all sales, we can use the defined table name and column headings in our table to make formula construction easier. The SUM formula can be used as follows:

=SUM(Sales[SALE AMOUNT])

SUM refers to the function being calculated

Sales refers to the name of the table being used in the calculation

[SALE AMOUNT] refers to the column in the Sales table that is being added up.

  TO DO:

1.   Calculate the Number of sales, Highest sale, Lowest sale, and Average sale amount using all of the Sales Amount data in the Sales table.

2.   Calculate the total sales for each store using the Sales table as the source data

Again, the use of the names makes the formula easier to understand and follow what is being included in the calculation. 3.   Calculate the total sales in store S1001 on 1/7/2022

Adding data to the table

Another benefit of the table is that adding a new row of data to the bottom of the table does not require any formula recalculation.

For example:

   Go to the bottom of the table and record the following sales details:

4/7/22; S10061; C61442; S1003; EMP6420; $900.00

When you have entered these the GST and TOTAL AMOUNT formulas are automatically applied.

The TOTAL SALES that we calculated in the previous step is also updated. This would not happen if we added the new sale to the data in Q1 because we did not establish a table. As a result, all the formulas we created would need to be

updated to include the new row of data. This could be quite time consuming and present a chance of an error in a formula.

POINT TO NOTE: What happens when you first enter a formula in G11 and H11? Excel takes the formula and applies it to all rows in the table. This has advantages for spreadsheet design, since it makes sure that all items in the column are calculated in the same manner.

This is an important spreadsheet design idea – each column in a table should refer to ONE thing only and should be consistent in how it is treated / calculated.

It is features like this that can make tables beneficial for managing data in a spreadsheet.

Creating a graph

   Let’s say we want a graph of sales by store.

a.    Select G3: H6 (this is the summary table of sales for each store)

b.     In the INSERT menu click on the column chart icon

This will create a three column chart showing total sales for each store.

Again, because the data is based on the specified data in the table we created, the headings and axes labels are formatted based on the table.

③ FINANCIAL FUNCTIONS

Silverware Sales has $10,000 that they would like to invest. They are trying to work out the best option for their investment, with the options being:

.     Investing in a simple interest term deposit that pays 10% per year for 2 years, or

.      Investing in a compound interest term deposit that pays 8% per annum with interest received each quarter for two years.

REQUIRED:

Help Silverware Sales decide which investment option is the best choice.

Simple Interest

Simple interest is paid as a fixed amount on the initial investment at set time periods. It is calculated as Interest = Principal x Rate x Time

Where

Principal = amount invested

Rate = interest rate

Time = time period of investment

Remember to make sure that the time period and interest rate and payment frequency are consistent. For example, if an investment is for 18 months and interest is paid every 3 months then the annual interest rate will need to be converted to  a quarterly rate and there will be 6 payments taking place (4 per year for 1 ½ years).

a)    Enter the input values for Interest rate, payments per year, and number of years

b)    Enter a formula in B10 that will calculate the number of interest payments to occur over the life of the investment.

c)    Calculate the effective interest rate that will apply to each interest payment.

d)    Set up a formula in B18 that returns the initial investment amount.

e)    Enter a formula in C19 that will calculate the interest received in year 1 of the investment. Copy the formula across to D19 so the amount is calculated for year 2.

Specify input cells for each of the three variables and an output cell that contains the formula for the calculation

Compound Interest

Compound interest calculates interest for each period based on the initial principal and any interest earned since the investment began. The value of an investment at the end of its life is Value = Principal x (1 + rate)time

a)    Input the values for the Interest rate, payments per year, and number of years

b)   Enter a formula in B10 that will calculate the number of interest payments to occur over the life of the investment.

c)    Calculate the effective interest rate that will apply to each interest payment.

d)    Set up a formula in B23 that returns the initial investment amount.

e)    Set up a formula in B25 that calculates the closing balance after interest has been received

f)     Enter a formula in C24 that calculates the interest earned in the first quarter. Copy this across to the 8th  quarter.

COMPARING OPTIONS

a)    Use formulas to complete the summary totals for each investment option (B31:C33)

b)    The FV formula could also have been used to calculate the closing value of the compound interest loan option.

=FV(Rate,NPer,Pmt,PV,Type)

Where:

Rate = the interest rate that is applied to each payment period

NPer = the number of payment periods in the life of the investment

PMT = the payment made in each period (for example if the principal was increased by a capital contribution of 500 per period then the pmt amount would be 500. This would be the situation where there is a recurring annuity being received and the amount is constant for the life of the investment.

PV = the Present Value of the investment (optional)

Type = timing of payment (0 for end of period, 1 for start of period) (optional – default of 0)

In cell C35 set up the FV formula to calculate the future value of the $10000 once the compound interest investment is complete. Compare the amount to what you calculated in C33 – they should be the same.

a)    In C36, use the formula FV=Principle x (1+rate)time  and calculate the future value of the investment.

b)    Silverware Sales would like to know how much they would need to invest as the principal today in order to have $15,000 after two years.

In this example we have been given the amount of the investment at the end of the investment term and need to work out how much we needed to invest at the start in order to achieve this target.

We can use the PV formula to determine this amount. This takes a future value and discounts it back to today’s dollars. =PV(rate,nper,pmt,FV,Type) where

Rate = the interest rate that is applied to each payment period

NPer = the number of payment periods in the life of the investment

PMT = the payment made in each period (for example if the principal was increased by a capital contribution of 500 per period then the pmt amount would be 500. This would be the situation where there is a recurring annuity being received and the amount is constant for the life of the investment.

FV = the Future Value of the investment you want to attain (optional)

Type = timing of payment (0 for end of period, 1 for start of period) (optional – default of 0)

c)    Build a formula that makes a recommendation on which investment option to choose.

In parts a and b we calculated the value of two investment alternatives. We now need to decide which investment to choose.

This can be set up using a combination of formulas and text.

i. What decision rule would you use to choose between the two investments? Write down your rule and the

logic of how it works. Keep it as simple as possible for now.

ii. Map out the rule in relation to the cells in your spreadsheet

iii. What two cells are you comparing?

iv. How are you deciding which is the better option?

v. Now we can build a formula to carry out this test. Essentially we are comparing the two and based on the result pursuing a particular course of action. We can build this logic with the IF formula.

You may find the IF function useful for this.

=IF(Logical test, Value if true, Value if false)

where

Logical test = the condition that you want to test

Value if true = the output you want displayed if the test is true (can be a hardcoded value or a cell reference)    Value if false = the output you want displayed if the test is false (can be a hardcoded value or a cell reference)

Combining text and formulas

Suppose you wanted to combine text and formula together. For example, if we wanted Excel to show a message in E30 that read: “The interest in quarter one is $200.00” we can combine the text and numerical aspects as follows:

=“The interest in quarter one is $”&C24&”.”

The text within the “ “ is a string that is attached to the cell value in C24. The & provides the connection between the text and the cell reference.

Based on this, enter a formula in B40 that provides the best investment option from the two loans. The message should read as:

Since the COMPOUND interest option yields the largest closing balance it should be selected.

The finished worksheet is shown below.