Exam 2: Analytics on Spreadsheets

Use the data given below to answer the following question(s). Below is a spreadsheet of purchase orders for a computer hardware retailer. -________ measures the worth of a stream of cash flows, taking into account the time value of money.

Identify the equation for calculating the net present value for a stated period of time, where $F _ { t }$ = cash flow in period t, and i is the discount rate.

Which of the following is a differentiation between calculating using the functions COUNT and COUNTIF?

Which of the following Lookup functions returns the relative position of an item in an array that equals a specified value in a specified order?

Use the data given below to answer the following question(s). Below is a spreadsheet of purchase orders for a computer hardware retailer. -For which of the following columns can the COUNT function be performed?

In a MATCH function, the default value for match_type = 0.

Use the data given below to answer the following question(s) Below is the spreadsheet for demand prediction of a company that sells chocolates. A B C 1 Demand Prediction Models 2 3 Linear Model 4 a 10,000 5 b 10 6 7 Price Demand 8 \ 50 9,500 9 \ 55 9,450 10 \ 45 9,550 11 -Given that D = a-bP, where D, is demand, "a" and "b," are linear constants, and P, is price, from the below spreadsheet, how will the formula in B9 be represented in Excel using relative addressing?

Use the data given below to answer the following question(s) Below is the spreadsheet for demand prediction of a company that sells chocolates. A B C 1 Demand Prediction Models 2 3 Linear Model 4 a 10,000 5 b 10 6 7 Price Demand 8 \ 50 9,500 9 \ 55 9,450 10 \ 45 9,550 11 -If, in the spreadsheet, cells B9 and B10 were empty, which of the following formulas should be entered in B8 so that the formula can be dragged to B9 and B10 to obtain their correct values?

Describe the method of calculating the net present value (NPV)in Excel.

A positive NPV means that the investment will provide added value because the projected return exceeds the ________.

Using the spreadsheet below to answer the following question(s). The spreadsheet below shows the net income model for a company that sells shoes. 1 A B 2 3 Data 4 5 Sales \ 10,000,000 6 Cost of Goods Sold \ 6,400,000 7 Administrative Expenses \ 500,000 8 Selling Expenses \ 900,000 9 Depreciation Expenses \ 750,000 10 Interest Expenses \ 70,000 11 Taxes \ 620,000 12 13 Model 14 15 Gross Profit \ 3,600,000 16 Operating Expenses \ 2,150,000 17 Net Operating Income \ 1,450,000 18 Earnings Before Taxes \ 1,380,000 19 20 Net Income -Which of the following formulas would be used to calculate the net operating income?

Use the data given below to answer the following question(s). Below is a spreadsheet of purchase orders for a computer hardware retailer. -To find the number of orders with A/P terms less than 25 months, what Excel formula should be used in A12?

The function ________ returns a value or reference of the cell at the intersection of a particular row and column in a given range.

Use the data given below to answer the following question(s). Below is a spreadsheet of purchase orders for a computer hardware retailer. -To find the average of the total cost of orders from Rex Technologies, what Excel formula should be used in A12?

Using the spreadsheet below to answer the following question(s). The spreadsheet below shows the net income model for a company that sells shoes. 1 A B 2 3 Data 4 5 Sales \ 10,000,000 6 Cost of Goods Sold \ 6,400,000 7 Administrative Expenses \ 500,000 8 Selling Expenses \ 900,000 9 Depreciation Expenses \ 750,000 10 Interest Expenses \ 70,000 11 Taxes \ 620,000 12 13 Model 14 15 Gross Profit \ 3,600,000 16 Operating Expenses \ 2,150,000 17 Net Operating Income \ 1,450,000 18 Earnings Before Taxes \ 1,380,000 19 20 Net Income -Using the spreadsheet below, provide the steps in using Excel formulas in finding the cost of the first order for Item number 1345, and the total cost of all Item numbers 1345, using the Match and Index functions in Excel.Column B is sorted by item number in ascending order.

Using the spreadsheet below to answer the following question(s). The spreadsheet below shows the net income model for a company that sells shoes. 1 A B 2 3 Data 4 5 Sales \ 10,000,000 6 Cost of Goods Sold \ 6,400,000 7 Administrative Expenses \ 500,000 8 Selling Expenses \ 900,000 9 Depreciation Expenses \ 750,000 10 Interest Expenses \ 70,000 11 Taxes \ 620,000 12 13 Model 14 15 Gross Profit \ 3,600,000 16 Operating Expenses \ 2,150,000 17 Net Operating Income \ 1,450,000 18 Earnings Before Taxes \ 1,380,000 19 20 Net Income -AND(condition 1, condition 2…)is a logical function that returns TRUE if all conditions are true and FALSE if not.

For which of the following MATCH functions must the values in the lookup_array be ordered in a descending order?

Give an account of how the design and format of spreadsheets can be improved.

Which of the following symbols is used to represent exponents in Excel?

Use the following scenario to answer the following question(s) The Blue Sunset Band is planning to record a new album.A major decision to be made is if the band can record the album on their own, or if they should hire a studio to record it with.The fixed cost for recording at the studio is $100,000 plus the manufacturing cost per CD, which is at $250.If they record the album in-house, the cost per CD is $350.They plan to produce 3000 copies of the album regardless of the place of recording.The band plans to record with the cheaper option.Below is the spreadsheet of the Recording Decision. A B The Blue Sunset Band Album Recording Decision 2 3 Data 4 5 Recording at Studio 6 Fixed Cost \ 100,000 7 Unit CD Cost \ 8 250 9 Recording In-house 10 Unit CD Cost \ 11 350 12 Production Volume 3000 13 14 Model 15 16 Total studio manufacturing cost \ 850,000 17 Total in-house 18 \ 200,000 19 Cost Difference 20 Recording Decision Studio -Which of the following formulas is used to calculate the total studio recording cost?