Wright Analytics

Microsoft Excel Projects

Our third Microsoft Excel Project is "Online Grocery Store Orders Project" and can be downloaded. In this project, we analyze customer order data which we obtained here. The project is designed to show the visitor how Calculus can be applied to business data in order to gain a deeper understanding of store performance and customer traffic. The dashboard "Quatric Regression Dashboard" contains an interactive nonlinear regression model that captures order costs, profit, and revenue generated on an hourly basis. Also contained in this worksheet are drop-down lists that you can interact with. The "Running Totals Dashboard" contains an interactive logistic growth model that models the running totals of order cost, profit, and revenue. Usually, a logistic growth model is used to model population dynamics of organisms. However, the data we obtained exhibit logistic growth properties making logistic growth models a suitable choice.

Below you will find our second Microsoft Excel project titled "BCHC Project." A detailed description of the data can be found here. In short, the data set contains information about various health conditions in some major United States cities and their corresponding population statistics. The main part of the workbook we want visitors to explore is the worksheet titled "US Map Dashboard" which contains a dynamic map. The goal of this project is to show how lookup functions such as INDEX, MATCH, and OFFSET can be used to output data based on your selection. Additionally, how these functions can be used to create dynamic ranges. Dynamic ranges are used to populate the map with data that matches the criteria the user has selected.

The drop-down lists on the "US Map Dashboard" are Indicator, Year, Race/Ethnicity, and Sex. After you make a selection in one list, the other lists update automatically. Note, Excel does have some limitations and so do we. It is rare, but some of the choices that you may make may produce an error message that looks like this. This does not mean that the workbook has broken. If this happens, click OK and make a different selection until the message disappears. We at Wright Analytics worked very hard on this workbook, made many revisions to the workbook to help ensure it is compatible with later releases of MS Excel, and tried to eliminate as many functionality issues as possible. In other words, we did our very very best to provide you with an exceptional Wright Analytics experience! Thank you so much for your continued support! Enjoy!

Below you will find a Microsoft Excel workbook that contains our first Microsoft Excel project on the Wright Analytics project website. We would have liked to present to you projects that we have completed for Greenspring Media. Unfortunately, we are not able to for privacy reasons. The goal of this workbook is to demonstrate our skills with specific functionalities of this Microsoft Office program. For example, combinging data validation with lookup lists to create drop-down lists, conditional aggregate functions, computations across worksheets, lookup functions (VLOOKUP,HLOOKUP, INDEX & MATCH, nested IF functions,etc.), and interactive dashboards. Note, this workbook is similar to analytics projects that the owner of Wright Analytics has completed for Greenspring Media.

The original data set used in this project can be found here. There are a few important things to note about some of the calculated variables in this workbook. A description of them are listed below.

The variable Time Category has been constructed in the following way. Operating hours between 10 am and 12 pm is morning, 12 pm to 4pm if afternoon, 4 pm to 7 pm is evening, and 7 pm to 9 pm is night. Customer Experience Rating contains three levels. These levels are based on the raw rating scores in the variable Rating. When the raw rating score is between 4.00 and 6.00, the supermarket concludes that the customer had a bad experience. When Rating lies between 6.01 and 8.00, the supermarket concludes that the service the customer received during their visit was satisfactory. Ratings that are 8.01, and above, the supermarket concludes that the service the customer received was superb. Customers are categorized as either "normal" or "members". The supermaket runs a rewards program. If the customer spends less than $200.00 they do not receive any reward. If their purchase is $200.00 but less than $500.00, members receive a 2.5% discount on their purchase and normal customers get a $5.00 gift card. When a member spends $500.00 but less than $700.00 they get a 5% discount and normal customers get a $10.00 gift card. When a member spends $700.00 but less than $900.00 they get a 7.5% discount and normal customers get a $15.00 gift card. When a member spends $900.00 or more they get a 10% discount and normal customers get a $20.00 gift card.