Defined names for working more effectively with data
.docx
School
Kennesaw State University**We aren't endorsed by this school
Course
BUSINESS BUSN 460
Subject
Accounting
Date
Jan 13, 2025
Pages
4
Uploaded by ProfBearMaster1253
Question 1Have a look at the Travel expense calculatorworksheet. 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 Ratesworksheet and useCreate from Selectionto name all the rates using the labels in columnA.What value is now showing for Total Other Expensesin K6?17.92Question 2While the calculation of Other Expensesis looking better it is still not correct. Openthe Name Manager. Have a look at the named ranges for Ex_Rateand Other, they only go to row 14, which explains the incorrect calculation. Edit Ex_Rateto go from L11:L21and change Otherto go from J11:J21. Click OKand close the Name Manager.Other Expenseshas been corrected. What is the total for Otheras shown in K6?27.12Question 3Let's fix Total Transportation Costs next. Open the Name Manager, there is a named range called Travel_Costs, but this is the wrong name. Change it toTravelCostsand click OKWhat is the value for Transportas shown in K3(one or two decimal places only)?471.50Question 4Next, Accommodation Costs, use any method you think suitable to give the name Accommodation_Coststo rangeF11:F21.What is the total for Accommodationas shown in K4(one or two decimal places only)?2663.80Question 5
And now to fix meals, let's be efficient and use Create from Selectionto name all three ranges simultaneously. Select G10:I21and click Create from Selection.What is the total cost of Mealsas shown in K5(one or two decimal places only)?1218.58Question 6Our Travel expense calculatorworksheet 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 - Jakarta. Now go to the Summary By Region worksheet and observe the calculated values for London.What is the summary value for London in the local currency? (no commas)1789.09Question 7Stay on the Summary By Regionworksheet. Enter a formula in C5to add up the total amount spent in Paris(used the named range you just created). Then do the same in C6for Jakarta.Using the named range and the SUMfunction, what are the formulas to use here? Note: you should have one answer for Paris and one answer for Jakarta. Just type the answer for one of these as your answer to this question.=SUM(Paris)Question 8Stay on theSummary By Regionworksheet. In D5 create a calculation to convert Eurosto Dollarsby multiplying the Euros spent (C5)by the exchange rate for Euro (which uses the named range EUR). Perform a similar calculation to convert the Indonesian Rupees to dollar (using the correct named range).What is the formula in D5?=C5*EURQuestion 9Click in D7(still in Summary By Region), and useAutosumto get the total spent in USD.
What is this value? (no commas, no characters, no dollar signs)4381.01Question 10Click in B9(still in Summary By Region), and use the Paste Namestool to Pasteall the named ranges into your workbook.What are the contents of cell C28? (Cut and paste your answer here to avoid errors.)='Currency Rates'!$B$4:$B$12Question 11Look at the Travel expense calculatorworksheet. What is the value of Total Meals in cell K5? (no characters or commas)1218.58Question 12Still on the Travel expense calculatorworksheet. What is the value of Total Other Expenses in cell K6? (no characters or commas and rounded to the nearest whole number with no decimal point)27.12Question 13How many rows are in your Name Manager?21Question 14In the Travel expense calculatorsheet, what is the value of Total Trip Expensesin K7 (rounded to the nearest dollar, input as a number with no "$" and no commas)?4381Question 15
In the Travel expense calculatorsheet, what is the value of L21 (to three decimal places)?0.016