Create Invoice from Excel Database for the Travel Industry

Create invoice from excel database for Travel Industry

hi guys so today I wanted to talk about this project that I'm working on for college so this project is based on an imaginary travel agency and the project the spreadsheet will record input data which means data that doesn't include calculations so here you have the input data customer customer name telephone and destination and ticket cost from here to here you have the output data now output data is data that includes calculations and formulas I have a range of calculations in here and one of the requirements of the assignment was I have to use a simple F and the condition is so here you can see an example of a conditional F and I'll quickly explain that so the column is the discount column and the formula reads if in five so if this column equals to two rocks or if c5 equals to Barcelona and in the customer the ticket cost will be multiplied by 4% so that means the customer will get a discount of 4% if they choose deluxe airport transfer or if they book and take it to Barcelona and then the discount is deducted from the subtotal here to give you a final total so as you can see h5 moment and five so that is an example of a conditional F and I have a simple if somewhere every single is here sorry my travel agency needed had a target and at their target of eleven thousand and eleven thousand five hundred euro so this is that the total cells that they hope to reach and the actual cells that they made equals to twelve thousand six hundred and one euro and for ancient and if this role here is going to tell me whether my company is making a profit or a lost and as you can see it made a profit because it exceeded the target and I guess is simple if to determine that so here is formula it says if j 8 FJ 18 which is actual sales is less than J 17 which is here then the company need a profit and if J 18 yeah does that make sense and it had a brain fight where I couldn't figure out what I was talking about okay yeah so that's my simple if okay now I get it so if J 18 is more than J 17 and the company makes a profit and if J 18 is less than J 17 and they make a loss I can edit that so I can make my target cell 15,000 and g19 is going to switch automatically to a lot like that so I'm gonna change my targets on our back to if you go 1.39 so yeah that's my simple F so here I have a nested-if in column F so I will explain that so calling F calculates the total that the customer owes if they choose I mean what type of Airport transfer don't you so I have two options they have basic under looks basic is like attack rehab and the rocks is like a limousine and the formula we if in five equals to basic and charge the customer 35 euro and or if in five equal to deluxe then charge and seventy five so that's my example of a nested if I can change this to basic and everything changes as you can see so yeah I will change after the rocks so on to the next column and to calculate the VAT over each customer I went to my ticket cost and I multiplied the ticket cost by this j21 which which means 23% and then I fell down and it copied the formula to each of these rows okay and then to get my subtotal I added I just missed a mistake in my calculations I should be adding this to and at me yeah so yeah to get my photo I added ticket cost plus Airport transfer cost plus patch and then I throw down again yeah and then she get the total as I explained earlier I deducted the discount from the subtotal and that is basically it I hope you enjoyed this video and I will see you guys in the next video