"In cell B17, enter a formula to calculate the amount budgeted for Hotel Accommodations. This amount is based on the # of Nights, the Hotel Rate/Night, and the Hotel Tax Rate. In parentheses add the Hotel Tax Rate to 1 to equal 118% percent before multiplying."
Okay. Let's break it down.
The parts of any formula are:
cell which houses the formula, where the result will appear
function arguments, the data required for the calculation
suggested/required methodology, how you will enter the data
the combination and order of the data, the desired value
"In cell B17, enter a formula to calculate the amount budgeted for Hotel Accommodations. This amount is based on the # of Nights, the Hotel Rate/Night, and the Hotel Tax Rate. In parentheses add the Hotel Tax Rate to 1 to equal 118% percent before multiplying."
1. The first, obvious item is the cell in which you will enter your data. So select cell B17, and click in the formula bar at the base of the Ribbon to begin the formula. Remember formulas always need to start with an equals sign (=).
2. So the parts of the formula you need are:
number of nights
hotel rate per night
tax rate
Look at your sheet and locate the cells which contain the information. Write down the cell references for each. For the example, I will be using the names of the data (example, number of nights) instead of the names of the cells. You need to use the cell references when entering the data into Excel.
3. You are asked to manipulate the data for the hotel tax rate: "In parentheses add the Hotel Tax Rate to 1 to equal 118% percent before multiplying." But why?
If you use the percent as is, 18% is written as a decimal as 0.18. This value will give you weird, negative results (multiplying by nearly 0).
To make it work, we need the value to be 1 or above. You can manipulate the data in a few ways (such as multiplying by 100). We want the percent (118%) to read 1.18 as a decimal.
To ensure the template-checker on Pearson marks your formula correctly, they've specified how they want you to adapt the rate (by adding 1, which is a whole number - remember a whole number is a number with only zeroes after the decimal, so 1 = 1.00).
It should look like this, but with cell references (the names of cells, derived from the letter of the column and number of the row - think of the game Battleships) of course:
([hotel tax rate, which in this case is 18%]+1)
If you recall BEDMAS (also called PEMDAS), the order of operations for formulas is as follows:
Brackets/Parentheses
Exponents (not really a thing in Excel, thankfully)
Division + Multiplication
Addition + Subtraction
So, if we've put the rate+1 in brackets/parentheses, Excel will do that part of the formula first.
4. Let's look at the rest of the question again.
"In cell B17, enter a formula to calculate the amount budgeted for Hotel Accommodations. This amount is based on the # of Nights, the Hotel Rate/Night, and the Hotel Tax Rate."
B17 = hotel accommodations budget
hotel accommodations budget = number of nights stayed multiplied by the rate per night, plus tax; that is, B17 = nights*rate + tax.
Using BEDMAS, let's arrange our data.
B17 = ([hotel tax rate] + 1)*[number of nights]*[rate per night]
Let's throw some values in here to check our work. I'm just going to use nice round example numbers. My interest will be 10%. We'll stay at the hotel for 5 nights. The room costs $50 per night.
B17 = (10% + 1)*5*$50
B17 = (0.1 + 1)*5*50
B17 = 1.1*5*50 which is = 110%*5*50
B17 = 5.5*50
B17 = $275
We can check this another way.
We know if we stay for 5 nights at $50 per night, the cost before tax would be 5*50, or $250.
We can then calculate the tax by multiplying the tax rate (10%) by the total cost ($250). This gives us $25.00 in tax.
Then, we add the tax to our pre-tax cost. $250 + $25 in tax is $275.
I hope this helps. Let me know if you have further questions for which you would like written guides.
Comments