All language subtitles for 10. Mathematical Formulas Case Study - Solution

In this video we will solve the case study for formulas let us first calculate the net price for mobile

phone for quarter 1 the net price is the difference between list price and the discount offered on the

product in the quarter

select cell S2 and enter the formula to calculate the net price which is list price minus discount price

center.

Now we have the net price for mobile phone in quarter 1 let us know calculate sales amount for mobile

phone in quarter 1 the salesman days the product of unit sales and the mid price

to calculate that let us into the formula sales amount equal to unit sales multiplied by net price press

center we now have the sales amount for mobile phone in quarter one to calculate net price and salesman

for other products and for all of the remaining quarters let us copy sales is 2 to I2

and based in those three to 17

Excel has now calculated net price and sales amount for all products for all quarters.

Let us calculate quarterly sales using the subtotal function that quarterly sales is the total of sales

amount for all of the four products in the quarter let us do that for quarter on select Saturday 5 they

will calculate subtotal for what their 1 and dime equal to and SUV Excel displays two formulas that

might let us.

We have entered as you'll be let us select subtotal from this list Excel displays the function number

argument or option for subtotal let scroll down to find some which is function Number 1 0 9 we are using

some because we are going to create subtotal so we are going to add numbers here and Tacoma.

Now we need to provide the cell reference where the values are restored which need to be added up by

subtotal so let us select the sales amount for Ford products in quarter 1 close the bracket to complete

the formula and presenter.

Now we have sales amount for quarter 1.

Let us copy this formula and use it for the remaining three quarters copy the formula for quarter 1

and based on sales in 9

day 13 for quarter 3 and 17 for quarter for.

Now we have quarterly sales for all the four quarters let us know calculate annual sales volume for

mobile phone.

We can get this volume by adding up mobile sales in each of the four quarters will allow the sum if

function to select only the rows where the product name is mobile phone.

Using some a function will be able to add up unit sales only for mobile phone from this list of unit

sales for all products in all quarters.

So let us do that.

Select Selby 20 where we want to store the unit sales volume for mobile phone on the formula step like

Matt and Craig and from the dropdown list select some suddenly function as do mandatory and one optional

but I mean does you can enter the first parameters range.

Here we need to enter the seller friends there out criteria value those stored in this case the product

name so we will select D2 to be 17 because these are the cells where we are storing product name which

will be used to filter the unit sales information in that criteria parameter because we want to find

sales for mobile phone will select cell D2 which has the mobile phone text in the Summerland field we

will enter the cells which have the values we want to add.

So let us select cells to through e 17 we have the unit sales volume for products for all therefore

quarters click Okay.

And you can see that Excel has now calculated unit sales for mobile phone for the entire year.

Let us make one thing to this formula that is calculating mobile phone unit since if you look at the

formula right now the seller fancies that the lady references let us take them to absolute references

so let us put a dollar sign before Roman column notations

to make them absolute references

let us do that for the values then you also.

Now why are we doing this that one day just using absolute references is if we copy and paste this formula

to another cell for calculating unit sales for phone cover and other products the reference to the selling

of the product names and the sale end with unit sales will not end.

However the criteria are product name for which we are calculating sales is still using relative reference

and therefore as we copy the formula down in this table automatically the reference will shift from

mobile phone to phone cover to pen drive and to headphone let us copy the formula we use for mobile

phone sales

and based it in phone our pen drive and headphone unit sales field and you can see we now have units

as did let us now calculate the gross sales discount and net sale for each of the four quarters to do

that.

The value of the sum product function in Excel.

The sum product function takes two cylinders as parameter multipliers corresponding sales in each of

the ranges and acts of the result of the multiplication will use a sum product function to pass unit

sales

and the list price has two parameters.

And the same product will then multiply unit sales and list price for each of the products and add them

up to give us the gross sale for discount.

Will do the same thing except will multiply unit sales with the discount amount to calculate how much

total discount was given of it and to calculate net sales will multiply unit sales with the net price

so let us calculate for the grosses on the formulas that click.

Day and then from the list of formulas.

Select some product the first area that we want to pass to some product function is the unit sales information

for our level product in quarter 1 the next information we want to pass

is the list price the gross sale equals unit sales multiplied by list price.

So in added to will pass the sales which have the list price for the four products for order 1 click

Okay.

You can see that gross element has been calculated by multiplying unit sales with the list price for

each product and then adding it up let us do this for the rest of the quarters.

So now we have cross-sell for all four quarters let us not calculate discount offered in quarter 1 in

the Gospel function also we use the unit sales.

Let us use absolute referencing for unit sales so we can copy this formula

and use it for discount.

Also let us copy this and based on discount and now you see that the discount has been calculated

and discounted in column G and you can see that because of the letter referencing for the list price

when we copied the formula from column it to f the reference to the list price tends to discount.

Copy that the second formula for net sales

no let us repeat the exercise for this content itself for the remaining quarters.

Now if you compare the net sale we have calculated in this table with what we calculated using subtotal.

You can see that is the same amount.

Finally let us calculate and sell for we can store we use that some formula

that is select the for quarterly sales numbers grows the bracket 2 and the formula and press enter.

Now we have the annual sales for the comp store.