Advanced Microsoft Excel Training at OpenCastLabs in Kenya, Uganda, Tanzania, Rwanda and Burundi

This is a unique post. It is a promo, it is an info source for you my dear reader. OpenCastLabs Consulting is a specialized training company operating in all the 5 Eastern African Countries commonly referred to as Eastern African Community. We specialize in Microsoft Excel as a productivity tool. Our classes are either monthly or a re conducted in your premise. Contact us here () for us to send you a no-obligation quotation for persons not less than 5 per group. You can also check our course listing here or a sample course on Advanced Excel for Finance Professionals () and Budgeting and Forecasting with Microsoft Excel () Hit me up anytime on info(@)opencastlabs.co.ke or call/SMS +254 722 831 259 Any time go to

How to develop a Simple Time Tracking Sheet in Microsoft Excel

The process of tracking time is unique to every employee or position, so there’s no one-size fits-all sheet that will do the job. Still, certain principles and features will generally play into most timesheet models. For instance, before you start formatting cells and entering formulas, you need to decide why you’re tracking time, who’s going to use that information, and how:

1. Decide on a time period. (Management will probably make this decision for you.) Most of us track hours weekly, biweekly, or monthly.
2. Tracking just time isn’t always enough. Many companies bill your hours back to clients. Therefore, you must allocate your time to specific projects. This article will show you how to create a weekly timesheet you can easily adjust to track biweekly and monthly times and projects. As is, this sheet doesn’t document breaks or allow for flexibility in scheduling. However, the basic structure and formulas are present, so you can easily customize the sheet to fit your needs.

This article will show you how to create a weekly timesheet you can easily adjust to track biweekly and monthly times and projects. As is, this sheet doesn’t document breaks or allow for flexibility in scheduling. However, the basic structure and formulas are present, so you can easily customize the sheet to fit your needs.
Read more: http://opencast-labs.com/downloads/How_to_Build_a_simple_timesheet_in_Excel.pdf and the sample files here http://opencast-labs.com/downloads/TimeSheetExcel.xls

Adopted from TechRepublic

How to use the awesome FORECAST Function in Excel

The FORECAST function is a rare function. In my few years of teaching Excel, none - even the most seasoned Excel users have never asked me how to use this rather mysterious function. I have always included a sub-topic on forecasting in my classes but end up showing professionals how to insert a graph, then a linear trend line and finally how to show the R coefficient and the Linear equation which is commonly used to build Forecasts.
Though FORECAST is a powerful tool, it cannot independently be used to generate forecasts. A good forecast system involves dealing with seasonality, noise, trends and events. So what does this mean?
Linear regression is a form of regression analysis and can be used to calculate a mathematical relationship between two (or more) sets of data. In forecasting, you would use this if you thought one set of data could be used to predict another set of data. For example, if you sold building supplies, you may find that changes in interest rates can be used to predict sales of your products. This is a classic example of using regression to calculate a relationship between an external variable (interest rates) and an internal variable (your sales). However, as we will see later, you can also use regression to calculate a relationship within the same set of data.
The FORECAST(x, known_y's,known_x's) function returns the predicted value of the dependent variable (represented in the data by known_y's) for the specific value, x, of the independent variable (represented in the data by known_x's) by using a best fit (least squares) linear regression to predict y values from x values.
If you assume that data pairs are plotted in a scatter plot with x values that are measured on the horizontal axis and with y values that are measured on the vertical axis, FORECAST returns the height of the best fit regression line at the specific value x on the horizontal axis. FORECAST is the value of y that would be predicted based on both the value of x and the regression line (characterized by its slope and intercept that can be found by using Excel's SLOPE and INTERCEPT functions).
In the figure below, we have a spreadsheet that includes the average interest rate over the previous 6 years and unit sales during that same 6-year period. We also show a predicted interest rate for the 7th year. We can see in the example that our unit sales go up as interest rates come down, and go down as interest rates go up. Just looking at the example, we can probably guess that our sales for year 5 would be somewhere between 4,000 and 5,000 based on the observed relationship between interest rates and sales during the previous periods. We can use the Forecast Function to more precisely quantify this relationship and apply it to the 7th year.

In the figure below, you can see the Forecast Function being applied. In this case, the formula in cell H3 is =FORECAST(H2,B3:G3,B2:G2) . What we have within the parenthesis is known as an “argument”. An argument is really just a means of passing parameters on to the function being used (in this case, the Forecast function ). Each parameter is separated by a comma. In order for the Forecast Function to work, it needs to know the value we are using to predict our output (our year 6 sales). In our case, the parameter (our Year 6 interest rate) is in cell H2, so the first element of our argument is H2. Next, it needs to know where it can find the existing values it will use to determine the relationship to apply to H2. First we need to enter the cells that represent the values of our dependent variable. In our case, this would be our units sold during the previous 6 years, therefore we enter B3:H3. Then we need to enter the cells that represent the values of our predictor variable. In our case, this would be the interest rates during the previous 6 years, therefore we enter B2:H2) .
The Forecast Function can now compare the units sold during years 1 through 6 to the interest rates in those same years, and then apply that relationship to our predicted Year 6 interest rate to get our forecasted sales for Year 7 of 4,523 units.
Download Excel Workbook

Using Microsoft Excel to Compute Confidence Interval of a population mean

CONFIDENCE: Confidence interval for a population mean

Classification: Microsoft Excel Data Analysis

The CONFIDENCE function calculates a value that you can use to create a confidence interval for the population mean based on the sample mean. This definition amounts to a mouthful, but in practice what the CONFIDENCE function does is straightforward.

Suppose that, based on a sample, you calculate that the mean salary for a chief financial officer for a particular industry equals $100,000. You might wonder how close this sample mean is to the actual population mean.

Specifically, you might want to know what range of salaries, working at a 95-percent confidence level, includes the population mean.

The CONFIDENCE function calculates the number that you use to create this interval using the syntax

=CONFIDENCE(alpha,standard_dev,size)

where alpha equals 1 minus the confidence level, standard_dev equals the standard deviation of the population, and size equals the number of values in your sample.

If the standard deviation for the population equals $20,000 and the sample size equals 100, use the formula

=CONFIDENCE(1-.95,20000,100)

The function returns the value $3920 (rounded to the nearest dollar). This interval suggests that if the average chief financial officer’s salary in your sample equals $100,000, there’s a 95-percent chance that the population mean of the chief financial officers’ salaries falls within the range $96,080 to $103,920.

Microsoft unveils new revamped logo - 25 years later

Microsoft has a new logo. Microsoft, founded in 1975 by Bill Gates and Paul Allen, is a veteran software company, best known for its Microsoft Windows operating system and the Microsoft Office suite of productivity software.

In the new look logo, a 4 pane window with four colors looks just like the layout found in Microsoft products. The logo is a simple font similar to Myriad Pro and might be the same used by Apple in its products.

Microsoft seems is ready to take on rivals Apple, as seen in the new designed Surface Tablet - a departure in design and tradition.

How to Analyze Multiple Responses in SPSS

How to analyze multiple responses in SPSS

Generating New Variables in SPSS: The Multiple Response Command

(Download SPSS File)

The table below shows part of a Health Issues survey questionnaire.

Question:

Thinking about health related matters, did any of the following happen to you in the last 1 Month?

	Response
Response	Yes	No
I was ill enough to go to the doctor
I sought counseling for mental problems
I had problems with infertility
I suffered from a drinking problem
I used illegal drugs
My child had to go to hospital
My partner had to go to hospital
A close friend died
My child suffered from drug or alcohol problems

The answers to such a set of questions are regarded as multiple responses, since the answer to each does not preclude an answer for the others; the responses are not mutually exclusive.

The answer to each of these questions is regarded, for the purpose of SPSS coding and data entry, as. a separate variable. That is, a column is set up for each of the items to which a respondent can answer Yes or No. In this instance, therefore, there are 9 columns of data; one containing either a 1 (= Yes) or 2 (= No) for each case according to whether they were ill enough to go to the doctor, another column containing 1 or 2 for each case indicating whether they had sought counseling for mental problems, and so on.

To code this in SPSS we use the code hlth1, hlth2, hlth3…hlth9

After coding and typing the respective labels, SPSS will look like the figure below:

Figure 1 - Variable View

The data view will look like the figure 2 below after data entry

Figure 2 - data view for multiple responses

With the data entered in this way, if we wanted to see the number of Yes responses for each of these variables we would have to generate 9 separate frequency tables and note the number of Yes responses in each.

An alternative, which also allows us to do further analysis, is to use the Multiple Response command. The Multiple Response command allows us to analyze a number of separate variables at the same time, and is best used in situations where the responses to a number of separate variables that have a similar coding scheme all ‘point to’ a single underlying variable.

In this example, we can consider each of the items in the question as all pointing to the state of a respondent’s health. They are particular operationalizations, each of which captures just one dimension of this complex variable. It is therefore interesting to summarize the responses to these items at once, and to be able to use the pattern of responses across these items in further analysis with other variables, which is exactly what the Multiple Response command allows us to do.

To use the Multiple Response command we initially have to set up a Multiple Response

Set. This procedure instructs SPSS to group together the responses across a range of variables.

Before doing this it is important to have noted the coding scheme for the items that will make up the Multiple Response Set. In this instance, the coding is 1  - 9 for the various health conditions. We note this because we need to tell SPSS which value (or range of values) is of interest to us. Here we are interested in all the Yes responses to each item.

Figure 3 - Define the Multiple Response variable sets

Transfer the Multiple variables to the right

Figure 4 - defining the variable categories

Since we have 2 responses (Yes, 1 and No, 2) we select categories 1 to 2.

Now that we have defined the multiple responses set we can analyze it. The ‘new’ variable that we have just created and called hlth_con does not appear on the Data Editor window with the existing variables, but is stored in SPSS’s memory, and is accessed only through the

Analyze/Multiple Response/Frequencies command. It won’t appear in the normal dialog boxes we are familiar with which present the variables in the data file in a source variable list.

It is also not saved with the data file and will disappear when the file is closed, so it is a good idea to perform all the analysis you plan to undertake using the multiple response set before finishing your current SPSS session.

The simplest analysis we can undertake on a multiple response set is to run a frequency  (Figure 5) on the new variable (Though you can also perform crosstabs

To analyze multiple frequencies

Figure 5 - Analyze Frequencies

The SPSS multiple frequencies command. Then Press OK