Excel and Business Modeling - Project Planning - 1

We can business model with the excel; which supports us with various financial functions to understand the business results

 

The steps involved are;

 

Operational Models

1.       Staffing model

2.       Revenue model

3.       Cost of goods sold model

4.       Cost of sales and marketing model

5.       Cost of product development model

6.       Cost of operating expenses model

7.       Capital expenditure model

 

Financial Model

1.       Statement of profit and loss

2.       Statement of cash flows

3.       Investment and valuation model

4.       Balance Sheet

 

Operational Project Planning

 

1.       Define the purpose of the project (company):

 

 

2.       Define the Scope:

 

 

3.       Define the names

 

 

4.       Define assumptions and risks

 

 

5.       Define roles and responsibilities

 

 

6.       Define the master schedule and milestones

 

 

On the basis of the above business planning; we have to plan the financials with the business potential and demand analysis data.

 

Will continue,……………..

Nested IF statements in a Cell of a worksheet

One of my friend asked me; how many IF statements can I nest in a cell?

 

Interesting, It is 64 IF statements in  a cell (version 2007 and above)

 

In excel version 2007, a cell can contain up to 32,000 characters.

 

 

Lookup Functions in Excel

When we generate reports and list, we can look up values from master sheet;

 

The advantages of lookup function are

 

1.  will eliminate the repeated typing of same data

2.  will eliminate the copy paste error; i.e. accidently pasting the irrelevant data to the record

3.  dynamic data update with reference / lookup value

4.  also will link multiple workbook

5.  easy for retrieving the respective records

 

The function syntax is as follows

 

VLOOKUP(lookup value, table range, column index, [range lookup])

 

Ø  Lookup value is the reference value that computer will match and pull the row data with respective to the value

Ø  Table range, master table data to look up value; in this the first column will be used as reference value and other columns will be the value result for the look up function

Ø  Column Index is the number i.e. n^th column value for the result

Ø  Range lookup is the optional argument (True / false) ie. Exact / approximate match

 

 

Note : Excel will result the first match instance; if the master table have more than one value.  Hence use the unique id / reference for vlooup formula.

Excel formula - Relative and Absolute references

In Excel there are two references used in the formula to refer the
input value for calculations

At times we have to use the relative and absolute references to meet
our requirement of copy and paste formula with all records in the
table / database

Press the F4 key as you are entering a formula to toggle a reference
through the four possible reference types.

Press the F4 key again. Excel changes the reference from an absolute
reference to a mixed reference, with the row portion of the reference
locked

Press the F4 key again. Excel changes the reference to a mixed reference,
with the column portion of the reference locked

Net working days - calculations in Excel

Last week I have received a mail from my team members  stating that he was wasting his time to find the working days between two days to calculate the mandays calculations for the project.

I have given the below suggtions to make his job easier

Excel has a date function called NETWORKDAYS; which is a function to find out the working day i.e. Monday to Friday in between two given dates.

For example;

Let us assume - Project start date : 25 Oct 2013  
Project completion date : 31 Jan 2014

and the following are the closed holidays - 5 & 6 Nov; 25 Dec; 1, 14,15,26 Jan

The simple formula would be {=NETWORKDAYS(Startdate, enddate, [holidays])}

The formula will give you the result 65 working days.

There are 71 weekdays in that 6 closed holidays given in the range. Hence the result is 65 working days. 

The same formula may be applied to set of records in the excel database; it eliminates the cumbersome calculations and formulas for the recquired result.