How is this possible? I provide all of the files. Even though free, it includes all of the features of the standard course: downloadable files, closed captions and quizzes, to help you learn and make it stick.
This course is designed for people who are preparing for an interview. Starting a new office job. Looking to jump-start their career. And even for those people who are thinking about a longer Excel course, but who aren't sure that Excel is right for them. This course is a starting point, the first step on your journey to learn business analytics. It may be as far as you need to go. Note that sample Excel worksheets are available with the data updated after each lesson.
Starting from a blank worksheet, I show you how to enter data to make a table. I show you how to format the data and enter formulas as well. The data is based on the sales from a small flower shop.
Let's get going! Once all of the data's entered, I'll show you how to add a basic filter to sort and filter the results. I'll also show you how to sum up the sales of the bouquets. It's easy! In this quiz, you'll answer some questions about the bouquets sold.
- Microsoft Excel - Wikipedia;
- The Complete Guide to Power Query.
- The Complete Guide to Power Query.
Please download the file from the previous lecture. In this lecture, we move to a more authentic, realistic case study, showing the sales data for an entire year for a printing company. The lesson introduces some basic business concepts, like how sales data from individual invoices are mapped to Excel, and critical elements of sales analysis, like identifying the top customers, the top products, trends in the data, and reporting by product line and by region. I've provided a file of the raw data that you can use to practice. It will be updated for each of the lessons in the section.
I encourage you to download the file and practice! We'll create pivot tables to summarize the sales by customer, by product, and by product line by month, to answer some of the key questions. A picture is worth a thousand words! I'll show you how to make charts from the pivot tables, including line charts, column charts, and a 3D column chart. As financial analysts using Excel, we become adept at using convoluted formulas to bend the technology to our will. However, by using Power Pivot, we can throw much of that out the window. To demonstrate this functionality, I created a small reference table in which I assigned each Category to a Type.
I also created a date table to use with our dataset see Creating a Date Table below. Power Pivot for Excel makes it easy to create a date table quickly in order to consolidate by months, quarters, and days of the week. The user can also create a more custom date table to analyze by weeks, fiscal years, or any organization-specific groupings. Besides measures, there is another type of calculation: calculated columns.
Excel users will be comfortable writing these formulas, as they are very similar to writing formulas in data tables. I have created a new calculated column below see Creating a Calculated Column below which sorts the Accounting Data table by Amount. Or, we can create a chart of Average Sales for each day of the week using the new Calendar table. While this chart looks simple, it is impressive that it took less than ten seconds to create a consolidation over two million rows of data, without adding a new column to the sales data. While being able to perform all these consolidated reporting scenarios, we can always still drill down into the individual line items.
We retain our highly granular data. So far, most of the analysis I have shown are relatively straightforward calculations. Now, I want to demonstrate the some of the more advanced capabilities of this platform. Often, when we examine financial results, we want to compare it to a comparable timeframe from the previous year. Power Pivot has some built-in time intelligence functions. For example, adding just two measures above to the Accounting Data table in Power Pivot enables me to produce the following PivotTable in a few clicks.
As a financial analyst, one problem I often have to solve is that of mismatched granularities. To further this mismatch, we will prepare a quarterly budget, even through the sales data is daily. With Power Pivot for Excel, this inconsistency is easily solved. By creating two additional reference tables, or dimension tables in database nomenclature, we can now create the appropriate relationships to analyze our actual sales against the budgeted amounts.
Further, we can define new measures that calculate the variance between actual sales and budgeted sales as below:. I created the two measures below:. Notice how the calculations are performed at both the category and seasonal type level. I discover my results have changed. Why does 1. Have you ever encountered a similar situation where your spreadsheet does not give you the result you were expecting for a seemingly simple calculation?
You have checked over your calculations and still cannot figure out where it went wrong. Finally, click the Solve button, and the solution is there! This is an example of a simple transportation optimization problem with a linear objective. More complex optimization models of this kind are used by many companies to save thousands of dollars each year.
Topics – Recommended By Role/Job Function
Problem : You want to minimize the cost of shipping goods from 2 different warehouses to 4 different customers. Each warehouse has a limited supply and each customer has a certain demand. Goal : Minimize the total shipping cost, not exceeding the quantity available at each warehouse, and meeting the demand of each customer. Here is how our transportation optimization problem looks like:. To define our linear programming problem for the Excel Solver, let's answer the 3 main questions:.source link
microsoft office excel power tips to the point book 5 Manual
The next thing for you to do is to calculate the total quantity shipped from each warehouse G7:G8 , and the total goods received by each customer B9:E9. You can do this with simple Sum formulas demonstrated in the below screenshot. To make our transportation optimization model easier to understand, create the following named ranges:. Please pay attention that we've chosen the Simplex LP solving method in this example because we are dealing with the linear programming problem.
If you are not sure what kind of problem yours is, you can leave the default GRG Nonlinear solving method. For more information, please see Excel Solver algorithms. Click the Solve button at the bottom of the Solver Parameters window, and you will get your answer. In this example, the Excel Solver add-in calculated the optimal quantity of goods to deliver to each customer from each warehouse with the minimal total cost of shipping:. When solving a certain model, you may want to save your Variable cell values as a scenario that you can view or re-use later. For example, when calculating the minimal service cost in the very first example discussed in this tutorial, you may want to try different numbers of projected clients per month and see how that affects the service cost.
At that, you may want to save the most probable scenario you've already calculated and restore it at any moment. Saving an Excel Solver scenario boils down to selecting a range of cells to save the data in. Loading a Solver model is just a matter of providing Excel with the range of cells where your model is saved. The detailed steps follow below. At the same time, the Solver Parameters window will show up where you can change your constraints and try different "what if" options. This is how you can use Solver in Excel to find the best solutions for your decision problems.
And now, you may want to download the Excel Solver examples discussed in this tutorial and reverse-engineer them for better understanding. I thank you for reading and hope to see you on our blog next week. The processing times per unit for each operation and total available hours per month are as follows:.
Formulate a linear programming model and use Excel Solver function to suggest the best mix of products which would result in the highest profit within the given constraints. I managed to solve the problem that you posted albeit some understandings I have changed to suit the scenario. Post your email ID and I will post the excel file to you asap. I created a workbook for scheduling hours for employees working at a movie theater for 1 week.
I need to have a certain number of employees for each day of the week, but I need to deal with their timeoff requests. And some of my employees are fulltime and some parttime. The timeoff request says "Can't work Saturday". I need to write a constraint based on those entries, add constraints so that employees are not scheduled to work on days when they are unavailable to work.
5 ways to optimize the performance of Excel 2016
How do I write a constraint to cover this? Dear Cheusheva I have tried my best to study your instruction and practice with my problem but I fail to come to an acceptable result. I hope you help me.
Related Microsoft Office Excel 2010 Power Tips (To The Point Book 5)
Copyright 2019 - All Right Reserved