Collaborate on Billing Statement in Excel for Technical Support with Ease Using airSlate SignNow

Billing statement in excel for Technical Support

hey applicant I'm the hiring manager and in this Excel interview test you'll have four questions from easy to hired alright I'll leave you to it understood thank you let me give this a try and in case you want to test yourself you can download this Excel file for free in the video description alright let's get into it so let's get started with question one which is supposed to be the easiest so here it says to highlight in red the 10 lowest orders so you can see down below that we have all of the units we basically want to highlight in red those that are the 10 lowest so we're just gonna select them by hitting Ctrl shift down arrow that's going to select all of them to the bottom and all we need to do is go over to conditional formatting instead of making a new rule we can just use some of the default ones like the top bottom rules and in this case we want to highlight the bottom 10 items so just click on that and let's suppose that we're happy with it being in red here you can customize if it's 10 or more but we're okay with this so we're just gonna hit on OK there and you can see it's highlighted the 10 lowest orders awesome so we get the approval of our hiring manager and that's q1 done moving on to question two and it says to fill the orange area so this one right over here using a formula that can be copied down to accept or reject a Project based on the minimum requirements so basically based on this criteria the revenue amount of 500 000 and the profit amount of 25 000 we want to see if all of these projects over here should be accepted or rejected here an if statement probably makes the most sense so we're just gonna go equals if hit the top key and here our hiring manager kindly points us out that we actually have two criteria the revenue and the profit and so for this The Logical test is going to be two logical tests so we're gonna put an and sign in here an and formula sorry and you can see that we've got two logical tests here so the first one is gonna be that this Revenue figure B greater than or equal to this one right over here and this we need to lock so so we can drag the formula down by hitting the F4 key there hit the comma key and then we have the second criteria which is the profit so we need this figure right over here the D6 to be greater than or equal to this 25 000 over here and again we're gonna lock it hitting the F4 key and then we can just close this formula so that's the and closed comma and now if these two conditions are met the value if true is that it should be accepted so we're gonna put this in quotations and say accept closer quotations comma and then the value is false meaning if it isn't the case then we're gonna put in quotations that we want to reject close the quotations close the parenthesis and just hit enter there so this first one we accept and we can just double click here to drag it all down and let's see if it makes sense it says to reject this one because the revenue here is less than the 500 000 and it says to accept the first one unless they're both greater than the profit and the revenue minimum requirements awesome so the hiring manager takes a look and it all looks good so that's question two done if you're liking this video and you want to learn more you can consider checking out our Excel for business and finance course and what makes this course different is that it's all applied to the real world while we still cover theoretical lessons like formatting formulas and charts we also offer case studies that simulate the type of work you might be assigned in your day-to-day ranging from Financial modeling to cleaning a data set and presenting some visual insights and if you get stuck along the way you can always ask us the instructors questions in the discussions forum we also offer several other courses including power bi Finance evaluation and more so if you're interested in checking it out head to the link in the description below alright back to the video next up in question 3 we have an optimization problem where we need to find out the number of shirts we need to reach five thousand dollars in profit so for this we've got a few assumptions below as you can see we have the price per shirt at close to 30 dollars the cost per shirt so this is the variable cost and then we've got the fixed cost of just fifteen thousand and so down below here in Orange we have the indicator that it says to change the units to reach 5K in profit so depending on what quantity we sell obviously the profit below is going to be affected so if I put say 1000 in here you can see that the profit is now closer to 5000 but as we're testing this out ourselves so you go to 800 we get a pop-up from our hiring manager saying to use a tool on Excel for this and the first one that comes to mind is using the gold seek on their what-if analysis so we'll head over to data then we're gonna go over to the side here you can see on the right we've got the what-if analysis and more specifically we want the gold seek so just click on that here you should get a pop-up like this and it says to set the cell which in this case we want the profit we want it to be exactly five thousand by changing the cell so this is the variable that we want to change so that's the number of shirts for us so the unit sold right over here and we'll just hit on OK OK there should be able to get exactly 5000 in profit so we show our hiring manager this number of 930.7 and he's asking us if we can sell a fraction on shirt probably we can't our customers won't be too happy if you only give them half of a shirt so instead what we could do here is the way around up so we round it up to a full number so at the very front here we're gonna go equals round up hit the top key there this is the number comma and at the end the number of digits we want it to be zero digits as we want a full number we're going to close a parenthesis and hit enter there and it's going to be 931 units sold to reach the 5000 in profit awesome that's question 3 done and finally let's move on to the last and hardest one in this final question it's asking us to use two different formulas to find the revenue for France in each month so you can see right here that we need to insert one formula for these three cells and then another formula for these so this is the criteria we have one that it has to be in the country of France and two that it has to be in January 2023. down over here we have the data set where we should be pulling from so let's go ahead and give this a try the first Formula that comes to mind is some kind of a sum ifs so it's gonna be sum the number if the condition of January is true based off of this part here and if the condition of France is true based off of the country column so for this we're gonna go equals sum IFS hit the top key there and the sum range for us is gonna be the revenue because that's what we want to find here so we're going to select this whole Revenue column Ctrl shift down there and we want to hit the F4 key so we can drag this along and it's not going to move comma then the criteria range number one is first let's go by countries so we want to select all of the countries here Ctrl shift down hit the F4 key again hit the comma key and the criteria number one is that it has to be friends so we're gonna select France which is right over here and we want to hit the F4 key once a second time and the third time such that it's only on the dollar sign there is only on the column so it's going to stay fixed at that column there comma criteria range number two this time we want the month of January so we'll select all of the dates here Ctrl shift down hit the F4 key comma and then the criteria number two is gonna be that it has to be in January in this case and this one We're Not Gonna lock as we want to be able to move across we're gonna close the parenthesis and just hit enter there so we've got 33 000 and we can just simply drag this across by going to shift right arrow and then hitting on control r and there you go just to check if this is working okay we can select the last one you can see that it's moved to March 2023 while it stayed still in France as it should be awesome that's the first part done and now we need to do a second formula this is where it gets a bit more tricky here you're probably thinking of doing an index match or maybe an X lookup so let's give that a try so down over here we've got the second part and we're just gonna go to equals X lookup hit the top key there in the lookup value for us let's suppose that's France we're looking for France so we're just going to hit the F4 key there three times comma the local parades were looking for France within all of these countries so Ctrl shift down hit the F4 key comma and the return array is we wanted to return to revenue so Ctrl shift down and we'll hit the F4 key there close a parenthesis and just hit enter now the problem here is that it's only for January so if I copy this across just by going shift right and hitting Ctrl R you'll notice that it all stays in January that's because we haven't actually put in the second criteria of putting the month of February and the month of March for the dates now the reason for that is because by default the X lookup doesn't give you multiple criteria it only gives you one lookup value over here so we're gonna have to artificially change this by just using an ampersand just hit on Ampersand in there after B7 and that's where that's how we'll be able to add the second criteria which in our case is going to be the month and this one we don't need to lock there and similarly for the lookup array we don't just want to look up the country but we also want to add an ampersand here at the end because we also want to look up all of the dates so Ctrl shift down and hit the F4 key there again and now we can simply hit enter then when we drag this new formula across just by hitting shift right and Ctrl R you'll notice that the numbers are now matching if I select on this one you can see that everything's moved dynamically all right let me check the results it's actually all looking good very impressive stuff and you're through to the next round awesome just like that we've completed our Excel interview test let me know in the comments what your score was out of four if you want to do an advanced Excel interview test check out this video over here or take our course over here to learn more about Excel hit the like And subscribe and I'll catch you in the next one