Introduction

Business Modeling

Everyone uses models to make decisions. For example, if you are arranging the furniture in your house, you mentally model your room space and create a model in your mind to keep the furniture in a certain way. Similarly, a road map, which is a visual model, helps you in reaching your destination. Miniature model of a structure helps in visualizing the structure once it is complete.

Refer class ppt

A Data Model allows the building of a “model” where data from different sources are combined by creating “relationships” between the data sources.

What is Microsoft Excel?

Excel is a spreadsheet program in the Microsoft Office system. You can use Excel to create and format workbooks (a collection of spreadsheets) in order to analyze data and make more informed business decisions. Specifically, you can use Excel to track data, build models for analyzing data, write formulas to perform calculations on that data, pivot the data in numerous ways, and present data in a variety of professional looking charts.

·         Accounting:  You can use the powerful calculation features of Excel in many financial accounting statements—for example, a cash flow statement, income statement or profit and loss statement.

·         Budgeting:  Whether your needs are personal or business related, you can create any type of budget in Excel—for example, a marketing budget plan, an event budget, or a retirement budget.

·         Billing and sales:  Excel is also useful for managing billing and sales data, and you can easily create the forms that you need—for example, sales invoices, packing slips, or purchase orders.

·         Reporting: You can create various types of reports in Excel that reflect your data analysis or summarize your data—for example, reports that measure project performance, show variance between projected and actual results, or reports that you can use to forecast data.

·         Planning: Excel is a great tool for creating professional plans or useful planners—for example, a weekly class plan, a marketing research plan, a year-end tax plan, or planners that help you organize weekly meals, parties, or vacations.

·         Tracking: You can use Excel to keep track of data in a time sheet or list—for example, a time sheet for tracking work, or an inventory list that keeps track of various equipment.

Data Entry 

Enter data in a worksheet

1.    Click the cell where you want to enter the data.

2.    Type the data in the cell – You can type numbers, texts or dates

3.    Press enter or tab to move to the next cell.

Formatting Data

1.    Select the cells that you want to format.

2.    On the Home tab, in the Number group, click the Dialog Box Launcher next to Number (or just press CTRL+1).

3.    In the Category list, click the format that you want to use, and then adjust settings, if necessary. For example, if you are using number format, you can select different options for representing numbers – decimal points, -ve numbers. If you’re using the Currency format, you can select a different currency symbol, show more or fewer decimal places, or change the way negative numbers are displayed.

Create a formula

1.    In a cell, type an equal sign (=) to start the formula.

2.    Type a combination of numbers and operators; for example, 3+5*7.

3.    Use the mouse to select other cells (inserting an operator between them). For example, select B1 and then type a plus sign (+), select C1 and type +, and then select D1.

4.    Press ENTER when you finish typing to complete the formula.

What are Excel Functions?

Excel functions are the set of formulas that are built-in inside the Excel application to do calculations and provide only the desired values in a cell.

The MS Excel application supports several functions which are classified into the following categories:

·         Math and trigonometry – Supports mathematical calculations such as calculating cosines, tangents, logarithms, roots, etc.

·         Financial – Facilitates the calculation and generation of financial models for the calculation of interest, depreciation, payments, etc.

·         Statistics – To facilitate the calculation of statistical models to calculate the mean, variance, distributions, hypothesis testing, etc.

·         Database - Functions to manage and calculate list of data that are in our Excel spreadsheets.

·         Engineering - To perform calculations relating to engineering-based models

·         Text - Functions to manipulate, convert and calculate strings

·         Logic – Excel functions to establish logical conditions for calculations (such as AND, OR, IF, etc.)

·         User Defined – You can create your own functions called macros that can be incorporated into your spreadsheets

Function: AVERAGE

Returns the average (arithmetic mean) of the arguments. For example, if the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) B2:B12 contains numbers, the formula =AVERAGE(B2:B12) returns the average of those numbers.

Syntax

The AVERAGE function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Number1: Required. The first number, cell reference, or range for which you want the average

Number2: Optional: Additional number, cell references or ranges

Step 1: In cell B13, enter = AVERAGE(B2:B12)

Step 2: Hit enter

Function: MAX

Returns the largest value in a set of values

Syntax

The MAX function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Number1, number2…

·         Number1, number2, ...  Number1 is required, subsequent numbers are optional.

Example:

Step 1: To find maximum number from a series of values, B2 to B12, enter =MAX(B2:B12) in cell B13

Step 2: Hit enter

 

The answer is 20

To find the maximum number of B2 to B12 and 25:

Step 1: =MAX(B2:B12,25)

Function: MIN

MIN function Returns the smallest number in a set of values.

The MIN function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Number1, number2…

·         Number1, number2, ...  Number1 is required, subsequent numbers are optional.

Function: SUM

The SUM function adds all the numbers that you specify. Each argument can be a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.), a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.), an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.).

SUM(B2:B12) adds all the numbers that are contained in cells B2 through B12.

For another example, SUM(B2, B3, B7) adds the numbers that are contained in cells B2, B3, and B7.

Function: IF

The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(B2>17,"Greater than 10", "Less than 10") returns "Greater than 10" if B2 is greater than 10, and "Less than 10" if B2 is less than or equal to 10.

The IF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

·         logical_test (Required): Any value or expression that can be evaluated to TRUE or FALSE.

·         value_if_true  (Optional): The value that you want to be returned if the logical_test argument evaluates to TRUE.

·         value_if_false  (Optional): The value that you want to be returned if the logical_test argument evaluates to FALSE.

Example:

1.    Step 1: B2 = 15

2.    Step 2: Enter in C3 =IF(B2>=17,"greater than 17”, “Less than 17”)

3.    If you want to assign grade for cell B2 > 15 = A+, if less than 15 is B

COUNTIF Function

Function COUNTIF

Counts the number of cells within a range that meet the given criteria.

COUNTIF(range,criteria)

Range  is the number of cells to count – in this example, B2 to B9

Criteria  is the criteria we define to count the number of cells – here, we specify the criteria as “>17” i.e,  any cell containing a number greater than 17.

The answer therefore is 5.

Another example of the Countif function using text – the criteria being “B+” where the cells containing B+ are being counted. The answer for this is 3.

SUMIF Function

Function SUMIF

Adds the cells specified by a given criteria.

 SUMIF(range,criteria, sum_range)

Range  is the number of cells to count – in this example, C2 to C22

Criteria  is the criteria we specify to define which cells will be added – here, we specify the criteria as “>125000” i.e,  any cell containing a number greater than 125000 are added to get the sum.

Sum_range are the actual cells whose data is being summed up only if their data matches with the criteria specified. If this is not mentioned, the range specified in the “range” is considered and their data is summed up if the criterion is matched. Here the sum_range is F2 to F22

The answer therefore is 2129508.30

Another example of the SUMIF function using text – the criteria being “Attorney” where the cells containing the data “Attorney” are being considered and their salaries are summed up. The answer for this is 1252372.15. Notice that the range has changed from “C” cells to “B” cells.

 .