The constraints involved in this example is the fact that we cannot produce more computers than we have components for and we also can’t produce a portion of a computer – we have to produce one whole computer at a time.
Now we need to add the constraints to our solver. So select cell B4:D4 as the variable range. What we are trying to work out is the best combination of sales per branch to maximize profit so the variables are sales per area. The variables for our calculation are the sales per branch. We will leave the “To:” value at Max since we want to achieve maximum profit. Solver opens a new window to allow you to add your constraints and objectives:Ĭlick the “Set Objective” field and then select the Profit cell – this will set the objective as the maximum sales that Excel can calculate based on the variables and constraints. Using Excel SolverĬlick the Data tab and then click solver. You will gain a solid understanding of most of the powerful features available in Excel and after the course you will feel comfortable about using Excel in any commercial environment. This course offers over 128 lectures and over 10 hours of content designed to take your Excel skills to the next level. Our worksheet now contains all of the formulas we need to calculate the optimal level of sales per branch.įor lessons on the advanced functions available in Excel, enroll in Microsoft Excel 2013 Advanced Online Excel Training Course now and join over twenty three thousand students who are learning to harness the power of the advanced features and functions in Excel. We can use the same SUMPRODUCT formula to calculate how many inputs are used for each type of component: Profit is calculated by multiplying the units sold times the unit price for each area. We will use the SUMPRODUCT formula to calculate the profit. So we have all of the components we need to get Excel to use the solver to work out the best combination of sales for each branch. We can’t make more units than we have in stock. The components we have represent the constraints of the problem. We need Excel to work out what the optimal sales for each branch will be based on the inputs we have to maximize our profit. The sales are represented by B4 C4 and D4. Our variables for this problem are the unit sales for each area. Our objective is to maximize profit so cell E5 will be our objective cell and it will show us what profit we will make if we sell the optimal number of computers in each branch. We also know how many inputs we have on hand. We also know what the inputs are for each area – seen under the Inputs for each PC.
Tutorial for excel 2013 Pc#
We know the sales price of the PC’s in each area – shown in the PC profit column. If we manually calculated the figures, it could take hours to work out the most profitable combination, but with Excel Solver, Excel does the work for you.
What we need to work out is how many PC’s we should make in each area to maximize the company’s profit. We know the inputs for each PC and we also how many units of input we have. The company produces PC’s that they sell at each of the different branches at different prices based on the area because the PC’s in California are slightly faster and therefore people pay slightly more for the PC than in Ohio, for example.
Tutorial for excel 2013 how to#
You will learn how to work with dates and times, how to calculate depreciation, how to insert and format tables and how to work with Pivot tables and charts.įor this tutorial we will assume you work for a computer company that has branches in California, Texas and Ohio. This course offers over 52 lessons and 12 hours of video content to teach you how to take advantage of advanced Excel functions. If you are familiar with Excel and want to take your skills to the next level, enroll in Learn Microsoft Excel 2013 – Advanced now and join over four thousand students who are learning to use Excel like a pro. This tutorial will show you how solver works and how you can use it to determine how many units to sell in different branches of an organization. The Excel solver function for example, can help you work out what optimum levels of production or sales will be based on various objectives and constraints. Excel 2013 contains powerful tools to save you hours of calculations.
0 kommentar(er)
