BM69001 – Spreadsheet Modelling and Programming for Business

Week Objective

1 Starting with excel and understanding the basic data handling

2 Visualization in Excel; To understand data manipulation through count and sum functions in Excel

3 To understand text, date and time functions in Excel

4 To understand Array functions and data validation in Excel

5 To understand lookup and financial functions in Excel

6 To understand summarisation and analysis

7 To understand the process of decision making using What-if analysis.

8 To understand statistical functions in Excel

9-10 To predict the value using time series data

11 To understand the process of optimization and find the optimal solution for operational problems in Excel

12 To find the optimal solution for financial problems in Excel

13 Introduction to simulation

Evaluation Components
S. No Evaluation Components Weightage
1 Research paper presentation (Group presentation) 20
2 Mid term 30
3 End term 50
Total 100
 Introduction

 Excel environment

 Exploring data types

 Data entry (Text, Value, Date, Time)

 Working with rows and columns

 Working with cells and ranges

 Working with tables

 Filtering data and removing duplicates

 Relative, absolute, and mixed References

 Circular references
 Introduction

 Excel environment

 Exploring data types

 Data entry (Text, Value, Date, Time)

 Working with rows and columns

 Working with cells and ranges

 Working with tables

 Filtering data and removing duplicates

 Relative, absolute, and mixed References

 Circular references
• Excel Ribbon – where is located in the excel
sheet

• Excel formula bar – where is it located

 Introduction

 Excel environment  A numeric value

 Exploring data types  Text

 Data entry (Text, Value, Date, Time)  A formula

 Working with rows and columns

 Working with cells and ranges

 Working with tables

 Filtering data and removing duplicates

 Relative, absolute, and mixed References

 Circular references
• An Excel workbook file can hold any number of
worksheets, and each worksheet is made up of
more than 17 billion cells.

• A cell can hold any of four basic types of data:

• A numeric value
• Text
• A formula
• An error
Numeric value
• A cell can hold upto 15 digits. Any digit after
15th digit is taken as zero and not considered

• This creates a problem, for e.g. when you are

storing credit card number, which is 16 digits
• e.g. type a 16 digit number in excel, press
‘Enter’ and see what is being displayed

• How to save credit card or other important

data in such a situation?
• First, Format the cell as text
• Then enter the number
• Or, put an ‘apostrophe’ before the number
(without formatting the cell as text)
• Exercise: Format a series of cells
• Type in 16 or 20 digit data in 3 cells one below
the other
• Sum them
• Could you do it? Why?
• Text entries
• You can enter text in the cells; a cell can take
about 32000 characters

• For example: Employee names

• Once you enter text in a cell, any number entry

after that is also considered as text
• E.g. “EMP_12” …. is a text, rather than a number
• Formula Entries
• Formulas are what makes spreadsheet a
spreadsheet

• We will do more of these later

 Introduction

 Excel environment

 Exploring data types

 Data entry (Text, Value, Date, Time)

 Working with rows and columns

 Working with cells and ranges

 Working with tables

 Filtering data and removing duplicates

 Relative, absolute, and mixed References

 Circular references
• Entering Numbers
• Go to ‘format cells’, then ‘Number’ on the top left
• General format – takes any number format
• Number format – you can specify the number of
decimal points etc. If you don’t specify the
number of decimal points, it will not take any
decimal point
• Currency format – automatically places the
currency nomination; there will be no problem
with adding etc.
• For a ‘minus’ sign, just add a minus sign, either
with or without a bracket
• Let us do a few in the excel sheet
• Text Entry

• As mentioned, a cell that has text and

numbers E.g. “EMP_12” is also a text, and not
considered as number
• If you write too much of text in a cell, expand
the cell, or use the ‘wrap text’ function
Entering Date and Time
• Excel handles dates by using a serial number
system.
• The earliest date that Excel understands is
January 1, 1900.
• This date has a serial number of 1. January 2,
1900, has a serial number of 2, and so on.
• You don’t need to bother about the
calculations that excel is doing at the backend,
You just enter the date

• For example, if you type June 1, 2019, Excel

interprets your entry and stores the value
43617, which is the serial number for that date
(starting January 1, 1900)
• Different date formats in excel

• Date formats that begin with * adjusts for

regional times (as given in your computer OS
settings)
• Time format

• Different formats as given under ‘format cells’

• Exercise: type 8 am and 8 pm in two

consecutive cells. Then convert them to 24-hr
format
• ‘Enter’ as a command
• By default, Excel automatically selects the next
cell down when you press the Enter key after
entering data into a cell.
• To change this setting, choose File ⇨ Options and
click the Advanced tab
• The check box that controls this behavior is
labeled
• After pressing Enter, move selection.
• If you enable this option, you can choose the
direction in which the selection moves (down,
left, up, or right).
 Introduction

 Excel environment  Insert

 Exploring data types  Delete

 Data entry (Text, Value, Date, Time)  Hide/Unhide

 Working with rows and columns  Freeze

 Working with cells and ranges  Format

 Working with tables

 Filtering data and removing duplicates

 Relative, absolute, and mixed References

 Circular references
 When you want to increase the
column width for multiple columns
• Select multiple columns. Drag the right-column border with
the mouse until the column is the desired width.
• Choose Click on Column Header ⇨ Format ⇨ Column Width
and enter a value in the Column Width dialog box.
• Choose Home ⇨ Cells ⇨ Format ⇨ AutoFit Column Width to
adjust the width of the selected column so that the widest
entry in the column fits. Instead of selecting an entire column,
you can just select cells in the column, and the column is
adjusted based on the widest entry in your selection.
• Double-click the right border of a column header to set the
column width automatically to the widest entry in the
column.
• Insert, delete, hide, unhide

• Exercise: type small digits in 5 rows, mentally

sum them. Then hide row 3 and 4. Then use
the sum function. Is the sum that you
mentally did matching after the two rows have
been hidden?
• FREEZE pane

• I want to see the first row heading even if I go

down to the 1500th row

• Decide on the row that you want to see

• Go to the next row and click
• In the Ribbon, go to ‘view’ and then ‘freeze pane’
 Exercises

 How to get the average a single number?

 Find the number of days between 16th August 2024 and 20th August
2024

 Find the number of days between 1st January 2024 and 31st December
2024 (2024 was a leap year)

 Find the number of days between 1st January 2025 and 31st December
2025
 Introduction

 Selecting range
 Excel environment  Contiguous range
 Non-contiguous ranges
 Exploring data types  Find

 Data entry (Text, Value, Date, Time)  Copying/Moving range

 Working with rows and columns  Naming the range

 Contiguous range
 Working with cells and ranges  Non-contiguous ranges
 Name manager
 Working with tables o Filter, New,
o Edit, Delete
 Filtering data and removing duplicates
 Comments
 Relative, absolute, and mixed References  Insert, Edit, Delete,
 Show/hide, format
 Circular references
Example of Range
• Press F8 to enter Extend Selection mode (Extend
Selection appears in the status bar). In this mode,
click the lower-right cell of the range or use the
navigation keys to extend the range.

• Press F8 again to exit Extend Selection mode.

• Generally a range is one row or column – one
after the other. This is called a contiguous range
• Click Home ⇨ Find & Select ⇨ Go To (or press
F5 or Ctrl+G) and enter a range's address
manually in the Go To dialog box. When you
click OK, Excel selects the cells in the range
that you specified.
• Selecting noncontiguous ranges
• Noncontiguous – scattered – not side-by-side
• Two ways:
(1) hold down the Ctrl key while you click and
drag
(2) Click Home ⇨ Find & Select ⇨ Go To (or
press F5 or Ctrl+G) E.g. typing A1:A10, C5:C6 will
select those two noncontiguous ranges.
• FIND – Go to Special Options

• E.g. Formulas: Selects cells that contain

formulas.
• Copying data from one sheet to many sheets
• (all data can be copied, or portions of data
can be copied)
• Go to the sheet (sheet 1) that has data
• Then select the sheet or sheets where you want
to copy the data (sheet 2, 3, 4 or only one sheet –
e.g. sheet 2) by pressing Ctrl and clicking the
sheets at the bottom
• Go to sheet 1 and select the data
• Go to ribbon – ‘Home’, just below the summation
sign, there is an ‘arrow down’ tab. It is in the
home tab
• Click on ‘across worksheets’
 Introduction

 Excel environment  Table design

 Exploring data types  Move a table

 Data entry (Text, Value, Date, Time)  Select (row/column/table)

 Working with rows and columns  Add (row/column)

 Working with cells and ranges  Delete (row/column)

 Working with tables  Remove duplicates

 Filtering data and removing duplicates  Total row

 Relative, absolute, and mixed References  Sorting and filtering

 Circular references  Converting table back to a range

 Let’s do it in class
• Creating a table

• Create a table with the following headers:

• Emp ID; Name; Dept; Hire Date; Salary; Days Employed
• Create data for 5 employees
• The hire dates will be 1st, 2nd, 3rd, 4th and 5th July 2025
respectively
• Apply Filter on the headers (right click on the cell,
filters)
• Calculate the days employed (from today..)
• Calculate the total payment made to the employees
• Calculate the days employed (from today..)

• =NOW()-hire date (the cell that has the date

of hire

• Scroll down to 100 employees data

• Use ‘freeze pane’ as a revision
• Alternate way:

• Type the data

• Go to ‘tables’ in the ribbon
• Click your way through
• Removing duplicate rows

• Select columns (select as many as possible, or

you may delete a row that you don’t want to
delete)
• Go to ‘data’
• In the ribbon, you will get a small box with a red
colour ‘x’. Click the box
• Check – ‘my data has headers’
• It will remove duplicate rows
• If you are creating a table using the Insert –
table function, when the table is created, you
can remove the duplicate rows
• Removing a value that has been typed twice

• This is done through ‘conditional formatting’

and will be taught later
• Sorting a table:

• Refer to exercise given in class

• sort the list by salesperson; and within each
salesperson's group, sort the rows by sq foot
(area). Then within each area, sort the rows by
list price
• How to go about it?
• first sort by the List Price column, then sort by
the Area column, and then sort by the
salesperson column

• ALTERNATIVELY: (EASY WAY OUT)

• Home – sort & filter – custom sort – add rows
– and go serially – (salesperson, area, list
price) – add three rows before starting
• Slicing a Table

• TABLE – INSERT SLICER

 Exercises

1. Compute total sales using the below formulas

 West_Bengal+Tamil_Nadu+Andhra_Pradesh+Madhya_Pradesh+Uttar_
Pradesh

 WB+TN+AP+MP+UP

 Sum(sales)

States Sales
West Bengal 500
Tamil Nadu 700
Andhra Pradesh 800
Madhya Pradesh 600
Uttar Pradesh 900
 Exercises

2. Open a worksheet and name the range containing the cells A1:B3 and
A6:B8 as data.

3. How can I identify number of records in Washington state whose tables

sales are more than 2000[Data set : File name: SMPB-Data-1.xlsx; sheet
name: Data 1]. What management decisions will you take

4. How can I identify number of records in Pennsylvania state which sold

tables or chairs or bookcases [Data set : File name: SMPB-Data-1.xlsx;
sheet name: Data 1]. Count the number of tables using any suitable
formula. What management decisions will you take
 Introduction

 Excel environment

 Exploring data types

 Data entry (Text, Value, Date, Time)

 Working with rows and columns

 Working with cells and ranges

 Working with tables

 Filtering data and removing duplicates

 Relative, absolute, and mixed References

 Circular references
• Absolute Cell Reference:

• When you do not want the cell value to

change

• E.g. $B$4 – value of Column B, Row 4 will

remain fixed
• Relative Cell reference:

• The formula of the cells can be copied etc

• Mixed cell Reference: e.g. $A4 (column A is

fixed, but it will take any changing value for
row 4)
• A circular reference in Excel occurs when a
formula refers to its own cell, either directly or
indirectly through other cells. This creates a
loop where the cell's value depends on itself,
causing calculation errors.
• for e.g. go to Cell A1 and type = A1+2
• Or go to Cell A1 and type = B1+2 and then go
to Cell B1 and type = A1-1
• Or Cell A6 has the formula =sum(A1:A6)
 Introduction

 Excel environment

 Exploring data types

 Data entry (Text, Value, Date, Time)

 Working with rows and columns

 Working with cells and ranges

 Working with tables

 Filtering data and removing duplicates

 Relative, absolute, and mixed References

 Circular references
A simple problem: NOT RELATED TO CIRCULAR REFERENCING)

A small company earns $1,500 in revenues and incurs $1,000 in costs. They
want to give 10 percent of their after-tax profits to charity. Their tax rate is 40
percent. How much money should they give to charity?