REGRESSION ANALYSIS: EXPLAINING OVERHEAD COST

 REGRESSION ANALYSIS: EXPLAINING OVERHEAD COST

This is my Business Analytics topic on Scatterplots Graphing Relationships.

The Finance manager of a factory wants to get a better understanding of overhead costs. These overhead costs include supervision, indirect labor, supplies, payroll taxes, overtime, depreciation, and a number of miscellaneous items such as insurance, utilities, and janitorial and maintenance expenses.  Some of these overhead costs are fixed whereas others are variable and do vary directly with the volume of work.

The Finance Manager has tracked total overhead costs for the past 36 months, To help explain these, he has also collected data on two variables that are related to the amount of work done at the factory. These variables are:

·         Machine Hours: number of machine hours used during the month

·         Production Runs: the number of separate production runs during the month.

Objective: To use scatter plots of Tableau to examine the relationships among overhead, machine hours, and production runs.

The data is available at   https://github.com/alcadelina/business-analytics-excel-data

The output:

 Step 1.

Step 2.

Step 3.

Step 4.

 Step 5.

 Conclusion: The Finance Manager should continue to explore the positive relationship between Overhead and each of the Machine Hours and Production Runs. However, none of the variables appears to have any time series behavior, and the two powerful explanatory variables do not appear to be related to each other.

ESTIMATING THE MEAN OF ACCOUNTS RECEIVABLE

 ESTIMATING THE MEAN OF ACCOUNTS RECEIVABLE

A Financial Manager wants to estimate the average of all accounts receivable, where this average is taken over the population of all customer accounts. Because the company has approximately 10,000 accounts, an exhaustive enumeration of all accounts receivable is IMPRACTICAL. Therefore, the Financial Manager randomly samples 100 of the accounts. The data from the sample appear in https://github.com/alcadelina/business-analytics-excel-data as file Auditing Receivables.xlsx.

         What can the Financial Manager conclude from this sample?

Objective:   To illustrate the meaning of standard error of the mean in a sample of accounts receivable?

The data:

Step 1. Type the required data.

Step 2. Compute the Sample Mean using the formula.

Step 3. Compute the Standard Deviation using the formula.

Step 4. Compute the Std Error of the Mean using the formula.

Step 5. Compute the first population count using the formula.

Conclusion:

1.      The Sample Mean P279 is the best guess for the average of the receivables from all 10,000 accounts.

2.      The Standard Error P42 provides a measure of the accuracy of the P279 ESTIMATE.

3.      There is a 95% chance that the estimate differs by no more than two standard errors (about P84) from the true but unknown population mean.

4.      The Finance Manager can approximately say that the mean from all 10,000 accounts is within the interval of P279 +/- P84 or between P195 and P363.

Arnel Lopez Cadeliña, MBA, REA, CFA (Investment Foundation Certificate)

SAMPLING: SAMPLING ACCOUNTS RECEIVABLE

 This Business Analytics lecture tackles Random Sampling. The data Random Accounts Receivable IN PESO.xlsx is available at https://github.com/alcadelina/business-analytics-excel-data.

We want to choose a random size of 25 and do it 25 random sample times in the 280 accounts.

How can this be possible? And how do we calculate the average amount owed in each random sample and create the histogram using Tableau.

Objective: Demonstrate how sample means are distributed.

Excel Solution:

Step 1. Random numbers next to a copy.  Copy the original data to columns F.G.H.I. Then enter the formula: =RAND() in cell J1 and copy it down column J.

Step 2. Replace with values. To enable sorting, you must first “freeze” the random numbers ----that is replace their formulas with values. To do this, copy the range J1:J280 and select Paste values from the Paste dropdown menu on the Home Ribbon.

Step 3. Sort. Sort on Column J in ASCENDING ORDER.  Then the 25 smallest random numbers are the ones in the sample.

 Step 4. Repeat Steps 2 and 3,  25 TIMES. Copy the values in Cell I in a new worksheet.

Step 5. Make a Table similar below. Use the Average Cost for Histogram.

Step 6. Then do the Histogram Using Tableau.

Step 7. Create Annotation for your Insights. 

Arnel Lopez Cadeliña, MBA, REA, CFA (Investment Foundation Certificate)

BUSINESS INTELLIGENCE TOOL FOR DATA ANALYSIS

The file Baseball Salaries with Team Info3. xlsx contains a sheet for each year from 2012 to 2018 listing each player, his team, his position, and his salary for that year. It also contains a Teams sheet that lists each team, the division and league of the team, and the team's city, state/province, and country.

The file can be downloaded at https://github.com/alcadelina/business-analytics-excel-data. 

Objective: To learn the mechanics of Tableau public for creating a viz of baseball team salaries.

The output should be similar below:

Step 1. Upload the Team Info3. xlsx to Tableau Desktop. 

Step 2. Drag "Year" to Columns Pill, Make sure it is Discrete and in color blue. Drag "Salary" to Rows pill. Drag "Team" to Color and into "Detail" in the Marks Card. Explore how to highlight Los Angeles Dodgers. Name your Sheet "Team salaries per Year".

Step 3. In a new Sheet, Drag "Year" to Columns Pill, Make sure it is Discrete and in color blue. Drag "Salary" to Rows pill.Drag "Team" to Color and into "Detail" in the Marks Card. Filter the "Division" choose only "National West", filter the "Position" choose only "Pitcher".Explore how to highlight Los Angeles Dodgers. Name your Sheet "National West Teams salaries of Pitchers per Year".

Step 4. In a new sheet, drag Longitude to "Columns" pill and Latitude to "Rows" pill. Drag " Country", "State,Province" and " City" to Colors in the Marks Card. Drag "Salary" to size. Adjust if necessary. rag " Country", "State,Province" , " City" and "Salary" to Detail in the Marks card. Explore how to highlight Los Angeles Dodgers. Name your Sheet "Team Salary Map".

Step 5. Create a dashboard using your sheets. Adjust margins and padding. The output should be similar below:

Arnel Lopez Cadeliña, MBA, REA, CFA (Investment Foundation Certificate)

BINOMIAL DISTRIBUTION USING EXCEL AND TABLEAU

 

The point of this example is that if each mutual fund has a 50-50 chance of beating the market in any given week, it is very unlikely for a particular mutual fund to beat the market in at least 37 out of 52 weeks. However, if you have a lot of such mutual funds, then it is fairly likely that at least one of them will beat the market this often.  You can see this from the data table. For example, if there are 600 mutual funds, the chance of at least one beating the market at least 37 out of 52 weeks is greater than 60%.

An analogy might help. If a person flips a coin 100 times, it's not very likely that she will get a streak of, say, 10 heads in a row. But if hundreds of people flip a coin 100 times, it's almost certain that at least one of them will get such a streak.

An Investment broker at Vizee Analytics claims that he has found a real winner. He has tracked a mutual fund that has beaten a standard market index in 37 of the past 52 weeks. Could this be due to chance, or has he really found a winner?

Objective: To determine the probability of a mutual fund outperforming a standard market index at least 37 out of 52 weeks.

The broker is no doubt tracking many mutual funds and he is only reporting only the vest if these. Rgere is a 50-50 chance of beating the market index, independently of performance in other weeks. In other words, rge number of weeks where the given fund outperforms the market index is binomially distributed with n=52 and p=0.5. The probability is P(X >  37) = 1 -P (x < 36) the relevant formula is:

=1-BINOM.DIST(B3-1,B4,0.5,TRUE)

To see whether any of the 400 funds beat the market at least 37 of 52 weeks , calculate P( Y >  1) = 1 -P (Y=0) in cell B9 with the formula:

=1-BINOM.DIST(0,B8,B6,TRUE)

The data shows beating the market 37 times out of 52 is no big deal with 400 funds, but beating it 40 times out of 52 even with 600 funds. The probability of this happening purely by chance is only 0.038 or less than 1 out of 25.

To see how the probability in cell B9 depends on the level of success on the reported fund (the value in cell B3) and the number of mutual funds being tracked 9ib cell B8), a two-way data table has been created in the range B13:G18. The formula in cell B13 is =B9, the row input cell is B3 and the column input cell is B8).

The Excel data for  Tableau:

SAMPLING: SAMPLING FAMILIES TO ANALYZE ANNUAL INCOMES

 In this Business Analytics lecture tackles about Random Sampling. The data Random Sampling.xlsm is available at https://github.com/alcadelina/business-analytics-excel-data. The extension is .xlsm because the file contains macro hence when you open it you have to enable it. 

We want to choose a simple random sample size of 10 out of the 40 population.

How can this be possible? And how do the summary statistics of the chosen families compare to the corresponding summary statistics of the population?

Objective: To illustrate how Excel’s random number function, RAND, can be used to generate simple random samples.

Excel Solution:

Step 1. Random numbers next to a copy.  Copy the original data to columns D and E. Then enter the formula: =RAND() in cell F10 and copy it down column F.

Step 2. Replace with values. To enable sorting, you must first “freeze” the random numbers ----that is replace their formulas with values. To do this, copy the range F10:F49 and select Paste values from the Paste dropdown menu on the Home Ribbon.

Step 3. Sort. Sort on Column F in ASCENDING ORDER.  Then the 10 families with the 10 smallest random numbers are the ones in the sample.

Step 4. Use the AVERAGE, MEDIAN, AND STDEV.S Functions in Row 6 to calculate summary statistics of the first 10 incomes in Column E. Similar summary statistics for the population have already been calculated in Row 5. (cell D5 uses STDEV.P function because this is the POPULATION standard Deviation)

For the Population Mean:

For the Population Median:

For the Population Standard Deviation:

For Sample Mean:

For Sample Median:

For sample Standard Deviation:

So the complete Excel data is:

You now have 10 sample size out of the 40 population using  simple random sampling.

Practice set.

Use the data “Practice Random Sampling” from https://github.com/alcadelina/business-analytics-excel-data . Generate 20 sample size out of 204 population.

Arnel Lopez Cadeliña, MBA, REA, CFA (Investment Foundation Certificate)