Course 2 - Week 3 Assessment A travel expenses template has been compiled for your company to make it easier for staff t
Views 441 Downloads 12 File size 91KB
Course 2 - Week 3 Assessment A travel expenses template has been compiled for your company to make it easier for staff to record their expenses when travelling to branches in other countries. It has recently been updated and the named ranges have been damaged in the process so most of the formulas are returning an error. You need to correct the named ranges to fix the problem.
1
Have a look at the Travel Expense worksheet. Note there are quite a few errors. Start by addressing the problem of the missing exchange rates by naming the ranges. Go to the Currency Rates worksheet and use Create from Selection to name all the rates using the labels in column A.
2
While the calculation of Other Expenses is looking better it is still not correct. Open the Name Manager. There is a range called "Coffee" that is no longer used, so delete it. Now have a look at Ex_Rate, it only goes to row 14, which explains the incorrect calculation. Edit it to go from L11:L21. Click OK and close the name manager.
3
Let's fix Travel Costs next. Open the Name Manager, there is a named range called Travel_Costs, but this is the wrong name, change it to TravelCosts and click OK and close the name manager.
4
Next, Lodging Costs, use any method you think suitable to give the name "Lodging_Costs" to range F11:F21
5
And now to fix meals, let's be efficient and use Create from Selection to name all three ranges simultaneously. Select G10:I21 and click Create from Selection.
6
Our Travel Expense worksheet is now looking good, but we would also like to complete a breakdown of expenses by region. Start with adding the following named ranges: E11:J14 - London E15:J18 - Paris E19:J21 - Mumbai Now go to the Summary By Region worksheet and observe the calculated values for London.
7
Enter a formula in C5 to add up the total amount spent in Paris (used the named range you just created. Then do the same in C6 for Mumbai.
8
In D5 create a calculation to convert Euros to Dollars by multiplying the Euros spent (C5) by the exchange rate for Euro (which is named EUR). Perform a similar calculation to convert the Indian Rupees to dollar.
9 10
Click in D7 and use Autosum to get the total spent in USD. Click in B9 (still in Summary By Region), and use the Paste Names tool to Paste all the named ranges into your workbook.
Employee Details Mei Wang Employee Name MW886 Employee ID 5/20/2017 Trip dates Start on 5/28/2017 End on System Rollout Purpose
Travel Expense Calculator
Transportation Travel Date
From
To
Expenses at a glance Transportation Expense $495.27 Lodging Expense $2,546.41 Meal Expense $1,279.42 Other Expenses $27.49 TOTAL TRIP EXPENSES $4,321.10
Lodging Travel Costs
Lodging Costs
Meals Breakfast
Other
Lunch
Dinner
Other
Currency Code
Ex Rate
20-May
Heathrow Airport
Slough Metropole
£
86.88 £
299.00 £
35.90 £
18.50 £
289.76 £
8.00
GBP
1.3133
21-May
Slough Metropole
Slough Office
£
35.72 £
299.00 £
35.90 £
15.50 £
42.37 £
4.00
GBP
1.3133
22-May
Slough Metropole
Slough Office
£
33.28 £
299.00 £
35.90 £
68.78 £
28.76
GBP
1.3133
23-May
Slough Office
Heathrow Airport
£
92.31
GBP
1.3133
23-May
Charles De Gaul
Hotel Augustin
€
46.43 €
310.00 €
28.00 €
15.50 €
EUR
1.1730
24-May
Hotel Augustin
Paris Office
€
12.55 €
310.00 €
28.00 €
12.80 €
EUR
1.1730
25-May
Hotel Augustin
Paris Office
€
12.55 €
310.00 €
28.00 €
46.85 €
EUR
1.1730
26-May
Hotel Augustin
Charles De Gaul
€
52.78
EUR
1.1730
26-May
Mumbai Airport
Hotel Sahara Star
INR
598.00 INR 8,900.00 INR
620.75 INR
INR
0.0156
27-May
Hotel Sahara Star
Mumbai Office
INR
340.00 INR 8,900.00 INR
620.75
INR
0.0156
28-May
Hotel Sahara Star
Mumbai Airport
INR
569.80
INR
0.0156
Page 2 of 4
38.50 €
5.00
118.25 70.22 €
385.00 INR 3,255.72
5.00
Travel Expense by Region Local Currency
USD
London
£
1,728.56
$2,270.19
Paris
£
1,450.43
$1,701.28
Mumbai
£
24,190.02
$377.12 $4,348.59
AUD Breakfast CAD Currencies Dinner EUR Ex_Rate GBP INR JPY Lodging_Costs London Lunch Mumbai NZD Other Paris TravelCosts USD ZAR
='Currency Rates'!$B$8 ='Travel expense calculator'!$G$11:$G$21 ='Currency Rates'!$B$9 ='Currency Rates'! $A$4:$A$12 ='Travel expense calculator'!$I$11:$I$21 ='Currency Rates'!$B$5 ='Travel expense calculator'!$L$11:$L$21 ='Currency Rates'!$B$6 ='Currency Rates'!$B$7 ='Currency Rates'!$B$12 ='Travel expense calculator'!$F$11:$F$21 ='Travel expense calculator'!$E$11:$J$14 ='Travel expense calculator'!$H$11:$H$21 ='Travel expense calculator'!$E$19:$J$21 ='Currency Rates'!$B$11 ='Travel expense calculator'!$J$11:$J$21 ='Travel expense calculator'!$E$15:$J$18 ='Travel expense calculator'!$E$11:$E$21 ='Currency Rates'!$B$4 ='Currency Rates'!$B$10
Exchange Rates Currencies
USD Conversion Rate 1
EUR GBP INR AUD CAD ZAR NZD JPY
1 1.17295 1.31334 0.01559 0.804 0.80344 0.0774 0.75349 0.009