ACIS 1504 Final (EXAM 1 & 2 with Chapter 10)

The output of a correlation analysis ranges from:

-1 to 1 Chapter 10

Assume that in cell Q8 that I have the following IF function: =IF(E4<95000,F4*6.2%,0) Assume that the value in cell E4 is 95,000 and the value in cell F4 is 3,000. The output of the function will be:

0 Logical test, is cell E4 is less than 95,000 Logical test is NOT met, so the FALSE output is 0 Chapter 6

The ABS value function has how many arguments?

1 Only arguments of the ABS value function is the (Number) Chapter 8

We have the following IF function with an ISERROR function as the logical test: =IF(ISERROR(O2),1,0) Assume that we do have an error within cell O2. The output of the function would be:

1 logical test is analysis if there's an ERROR message in cell O2 If TRUE, output is 1 If FALSE, output is 0

T/F: Assume we have a P-Value of 98%. This would mean that there's a 2% chance the relationship between the independent and dependent variables occurred out of coincidence.

FALSE Chapter 10

The minimum predicted value in a regression output is the:

Intercept Chapter 10

The Combo Chart created within Chapter 10 had:

One x-axis and two y-axes Chapter 10

_________________________ is an analytics technique that can identify the lowest and highest amounts in a given dataset.

Sorting Chapter 3 "sorting data (single-level)" (pg. 96-98)

T/F: Assume we are using the ISERROR function but we are not using it within an IF function (we are using the ISERROR function by itself). The output of the ISERROR function would either be "TRUE" or "FALSE".

TRUE Chapter 8 ISERROR function (pg. 350)+

T/F: When creating a link to an Excel chart in a PowerPoint or Word file, the Excel workbook must be kept in its original location on your computer or network.

TRUE Chapter 9 why box (pg. 427)

T/F: The TEXT function has two arguments and the EOMONTH function has two arguments.

TRUE TEXT function has 2 arguments (value, Format_text) EOMONTH has 2 arguments (start_date, Months) Chapter 5 (pg. 198) (pg. 193)

T/F: The YEAR function has only 1 argument.

TRUE The only argument for the YEAR function is the serial number Chapter 5 "the YEAR function" (pg. 196)

Which of the following statements is FALSE?

The flexible budget is based on the budgeted number of units sold. Chapter 4

If the [type] argument in the PMT function is left blank which of the following is TRUE?

The function will assume that payments will be made at the end of each period. Chapter 5 table 5.2 (pg. 183)

A line chart is being used to compare two data series: the stock price of Microsoft and the S&P 500 stock index. The goal of the project is to use 52 weeks of data to compare the growth of the Microsoft stock with the S&P 500 stock index. The stock price range for Microsoft is $160 to $180. The price range for the S&P 500 index is $2,800 to $3,400. Which of the following must be considered or accomplished when creating this chart?

The percent change in price for each week must be calculated for the Microsoft stock and the S&P 500 stock index. Chapter 9 why box (pg. 391)

Which of the following is TRUE with respect to the AND function? (assume we have two logical tests)

The percent change in price for each week must be calculated for the Microsoft stock and the S&P 500 stock index. With the AND function both logical test must be true to produce a TRUE output. If ONE logical test is not met than it will produce a FALSE output.

Assume we are in the process of entering data into an Excel worksheet, and instead of typing the number 726, we type the number 762. This is what type of error?

Transposing Chapter 2 "entering data" integrity check box (pg. 47)

Which of following commands can be used to to stack multiword column headings vertically in a cell location, reducing the need to expand column widths?

Wrap Text Chapter 2 "wrap Text" (pg. 68-69)

Estimated shipping cost at Junk Food Imports is estimated using the high-low method . Past records indicate total shipping cost was $18,000 for 16,000 pounds shipped and $22,500 for 22,000 pounds shipped. Using the high-low method, what is the company's estimated cost function?

Y = $6,000 + $0.75(x) how to solve: 22,500 - 18,000/ 22,000 - 16,000 = 0.75 22,500 = a + 0.75 (22,000) a = 6,000

The total sales dollars _______________________ quantity sold equals average price per unit.

divided by Avg. price per unit = Total sales/ Qty sold Chapter 1

The x-axis is on the:

horizontal

The correlation analysis between the S&P and VIX was close to a:

perfect negative relationship Chapter 10

One of the arguments of the WEEKNUM function is the:

serial_number WEEKNUM has two arguments 1. Serial_number 2. [return_type]

The FICA tax is a combination of what taxes and what is the overall FICA tax rate:

social security and medicare taxes and the overall rate is 7.65%

Assume we are either working with the VLOOKUP or HLOOKUP function. Which error message will appear if the column index number or row index number exceeds the number of columns or rows in the table array range?

#REF! Chapter 6 why box below figure 6.31 (pg. 269)

Runaround Corporation sells running shoes and during January they ran production machines for 20,000 hours total and incurred $9,000 in maintenance costs. During July they ran production machines for 14,000 hours total and incurred $7,200 in maintenance costs. Using the high-low method, what is estimated total maintenance cost if the company ran the production machines for 15,000 hours?

$7,500 how to solve: 9,000 - 7,200/20,000-14,000 - 0.30 per hr 9,000 = a + 0.30 (20,000) a = 3,000 y = 3,000 + 0.30 (x) = 3,000 + 0.30 (15,000) y = 7,500

XYZ Corporation is estimating the following purchases from June thru September: June purchases = $450,000 July purchases = $500,000 August purchases = $750,000 September purchases = $1,000,000 The company also estimates that it will pay 30% of the purchases in the month of purchase, 65% in the month after purchase, and 5% in the second month after purchase. What are the estimated cash payments for the month of September?

$812,500 450000 500000 x 5% = 25000 750000 x 65% = 487500 1000000 x 30% = 300000 =812500 Chapter 5

Lore Corporation has provided the following information: Sales Revenue = $200,000 Total Variable Costs = $40,000 Total Fixed Costs = ?????? What would the company's total fixed costs be if we wanted to operate the company at the break-even point? (use the contribution-format income statement)

160,000 Sales revenue - Total VC = Contribution margin 200,000 - 40,000 = 160,000

How many additional Excel Ribbon tabs are added when we create a PivotTable?

2 Chapter 3 "creating a PivotTable" figure 3.15 (pg. 103)

The following function was entered into cell F9 on an Excel worksheet: =EOMONTH(D4,4) If the date 10/20/2020 was entered into cell D4, which of the following will be the output in cell F9 after proper formatting is applied?

2/28/2021 Chapter 5 4 months from 10/20/2020

The cell range $B$2:$F$300 is used to define the table array argument of an HLOOKUP function. If the row_index_num argument is defined with the number 3, data from what row will appear as the output of the function when a match is found for the lookup value?

4 Row 1 -> 2 Row 2 -> 3 Row 3 -> 4

Which of the following was NOT deducted from gross pay to arrive at the "net payment" amount for an employee?

401K Company Match

Which argument is different when comparing the FV function to the PV function?

4th argument Chapter 5 FV function (pg. 213) PV function (pg. 221)

Larimer Company has total fixed costs totaling $90,000 and variable costs of $5 per unit. Each unit of product is sold for $20. What is the break-even point in units?

6,000 units how to solve: Breakeven point = FC/CM per unit SP - VC = CM per unit 90,000/(20 - 5) = 6,000

The following RIGHT function was entered into cell P15 on an Excel worksheet: =RIGHT(C15,6) Cell C15 contained the following: LT4388892HTM What would be the output of the RIGHT function in cell P15?

892HTM 6 characters from the right

The following is entered into cell F3: =$B8 If cell F3 is copied and pasted into cell G6 which of the following will appear in the formula bar when looking at cell G6?

=$B11 down 2 rows F3 -> G6 = $B8 -> $B11

The following is entered into cell G10: =$D$2 If cell G10 is copied and pasted into cell H15 which of the following will appear in the formula bar when looking at cell H15?

=$D$2 Chapter 4 "absolute references" (pg. 148-151)

The cell reference C9 is entered into cell D11: =C9 If cell D11 is copied and pasted into cell G15, what will appear in cell G15 when it is double clicked?

=F13 C9 over 3 columns and down 4 rows would be F13 Chapter 4

The following formula was entered into cell B9 on an Excel worksheet: =G6/F$14 If cell B9 is copied and pasted into cell D10, which of the following shows how relative referencing will change this formula?

=I7/H$14 Move two columns to the right move one row down the number after dollar sign stays the same

The following loan data is entered into an Excel worksheet: Loan Principal, cell B5, $150,000APR, cell B6, 5%Terms, cell B7, 30 years Assume loan payments are made annually at the beginning of the year. Which of the following functions will accurately calculate the required annual payment for this loan? (assume the fv is 0)

=PMT(B6,B7,-B5,0,1) Chapter 5 "PMT function for loans" (pg. 183 - 190)

The following lease data is entered into an Excel worksheet: Asset Cost, cell B5, $30,000 Residual Value, cell B6, $8,000 Lease APR, cell B7, 3% Lease Term, cell B8, 5 years Assume lease payments are made monthly (beginning of the month). Which of the following functions will accurately calculate the required monthly payments for this lease?

=PMT(B7/12,B8*12,-B5,B6,1) Chapter 5 "PMT function for leases (pg. 202-204)

Assume we have values in cells A1, A2, A3, A4, A5, and A6. Which of the following is a correct formula to sum up the values from only cells A2 and A5?

=SUM(A2,A5) Chapter 4 integrity check box (pg. 154)

The AVERAGEIFS or SUMIFS function will only select values from the average_range or sum_range if _____________ criteria pairs are true.

All Chapter 7 table 7.3 (pg. 301)

The cell range $B$2:$G$30 is used to define the table array argument of an HLOOKUP function. Which of the following cell ranges show where the function will search for the lookup value?

B2:G2 functions will search for the lookup value within the first row of the table array

The Freeze Panes command is going to be applied to a worksheet such that columns A and B and rows 1 through 6 are visible. Which cell location should be activated first before applying the Freeze Panes command?

C7 The cell that needs to be activated is one column over and one row down from the column/rows shown. Chapter 4

Total fixed costs divided by _____________________________ equals the breakeven point in sales dollars.

CM% or CM ratio Breakeven point in sales dollar = FC / CM%

Assume we are creating a function and the first argument of the function is text1. What function has a first argument of text1?

CONCATENATE function First argument of the CONCATENATE function is text1

________________________________ enable Excel to produce new outputs when one or more inputs in the referenced cells are changed.

Cell reference Chapter 4 "key takeaways" (pg. 144) bullet point #3

you must include _____ headings when highlighting the range of cells in your dataset before inserting a pivot table

Column Chapter 3 (pg. 104)

An analyst needs to show the number of invoices that fall into each of the following past due categories: 10 days, 20 days, 30 days, and more than 30 days. Which of the following charts would be most appropriate to use for this data?

Column Chart Chapter 9 "column charts for frequency distribution" (pg. 403-404)

Assume that in cell F9 we have a yearly amount for "salaries expense", and in cell G14, we would like to convert the yearly amount to a monthly amount. In cell G14, we have the following formula: =F9/12 The 12 in the above formula would be considered a:

Constant Chapter 4 "formulas" why box (pg. 136)

The second argument in the AVERAGEIFS function is the:

Criteria_range1 Chapter 7 table 7.3 (pg. 301)

If you are using a dataset that was provided by another person, it is critical to conduct a ___________________________.

Data Audit Chapter 1 "conducting a data audit" integrity check box (pg. 12)

Subtotals and grand totals can be turned on or off within the PivotTable by using which Excel Ribbon tab?

Design Chapter 3 "formatting a PivotTable" (pg. 110)

Assume we were looking at a PMT function on a loan and we noticed that the "rate" within the PMT function was divided by 4 and the "nper" was multiplied by 4. How often would the company be making payments on the loan?

Every 3 months 12 divided by 4 is 3 Chapter 5

Assuming we have no mathematical computations placed in parentheses, which of the following is executed first?

Exponential computations Chapter 4 "complex formulas" table 4.2 (pg. 140)

T/F: B2:G15 would be referred to as a cell reference.

FALSE Chapter 1 "navigating excel worksheet" see definition of cell range (pg. 19)

T/F: Assume we are analyzing variances for a company's expenses. The expense variance would be considered unfavorable when the actual expense came in below the flexible expense.

FALSE Chapter 4 Actual expense > flex budget = unfav Actual expense < flex budget = fav

T/F: The output of the AVERAGE function will be the middle number in a group of numbers.

FALSE Chapter 4 table 4.3 (pg. 145-146)

T/F: Assume we have a function that includes cell references that are blank. The excel function will count these blank cells as a zero within the function.

FALSE Chapter 4 why box (pg.153)

T/F: The COUNTIF function will only count the number of cells in a range that contain numeric data.

FALSE Chapter 7 "the COUNTIF function" (pg. 286)

T/F: The "format_text" argument of the TEXT function must be enclosed in parentheses.

FALSE Must include quotations not parentheses Chapter 5 "the TEXT function" why box (pg. 198)

T/F: The contribution margin equals zero at the break-even point.

FALSE Net operation income equals zero at the break even point

T/F: Sales revenue minus total variable costs equals gross profit or gross margin.

FALSE Sales - Total VC = Contribution margin

T/F: When entering a date into a cell location you must put an equal sign before typing the date.

FALSE Chapter 5 why box (pg. 194)

T/F: When using the CONCATENATE function both text entries and cell references need to be enclosed in quotations.

False Chapter 7 why box (pg. 309)

When the mouse pointer gets close to the ___________________________, the white block plus sign will turn into a black plus sign.

Fill handle Chapter 2 "Auto fill" section. see step 2 with fill handle definition (pg. 48)

The sum_range argument is the __________________________ argument for the SUMIFS function

First Chapter 7 (pg. 301)

An excel user can edit data by using the:

Formula bar Chapter 2 "editing Data" (pg. 53)

The ______________________________ command can be used to lock column and row headings in place when you are scrolling through large worksheets.

Freeze panes Chapter 4 key takeaways (pg. 144)

The cell range $C$4:$H$30 is used to define the table_array argument of an VLOOKUP function. If the col_index_num argument is defined with the number 5, data from what column will appear as the output of the function when a match is found for the lookup value?

G 1 -> C 2 -> D 3 -> E 4 -> F 5 -> G

Which tool maximizes the benefits of Excel's cell-referencing capabilities by changing inputs to precise values to achieve specific outputs produced by formulas or functions?

Goal seek Chapter 5 "evaluating scenarios with goal seek" (pg. 218)

The following logical test was entered into cell J1 on an Excel worksheet: =D5<=50 The output in cell J1 will be FALSE if the value in cell D5 is 50.

Greater than True output would be any number less than or equal to 50 False output would be any number greater than 50

A Treemap chart is what type of a chart?

Hierarchy chart Chapter 3 "the treemap chart" (pg. 90-91)

Assume we are looking at the multi-step income statement (this was the income statement from Chapter 5). Which of the following would decrease a company's gross profit or gross margin? (assume other amounts remain constant)

Increase in cost of goods sold sales revenue - cost of goods sold = gross profit or gross margin

The multi-step income statement was learned in Chapter 5. Which of the following items was not factored into calculating net operating income?

Interest expense Interest expense is below the calculations for net operations income. Chapter 5

Which of the following symbols defines "not equal to"?

None are correct <> is not equal to symbol Chapter 6 table 6.1 (pg. 242)

The following is entered into cell A2: ABC123 Assume in cell H2 we would like to extract the C123 out of cell A2. Which of the following functions would be correct in cell H2?

None are correct Correct function = RIGHT (A2,4)

Which of the following would be considered a mixed reference?

None are correct Mixed references would only have ONE $ sign in front of the column letter or row number EXAMPLE: $M9

Medoc Company provides the following information about its single product: Selling price per unit = $10.00 per unit Variable cost per unit = $6.00 per unit VC % = 60% Total fixed costs = $250,000 Target profit = $150,000 How much in total sales dollars does the company need to reach the overall target profit goal?

None of the answers are correct How to solve: 1 - VC = CM% 1 - 60% = 40% FC + P / CM% = Target profit in sale dollars 250,000 + 150,000 / 40% = 1,000,000 Answer is: 1,000,000

Which of the following functions has the arguments of "range" and "criteria"?

None of the answers are correct The COUNTIF function has the two arguments of "range" and "criteria"

What is the TEXT function format code if we want the output for 01/04/2020 to appear as: 4-Jan

None of the options are correct The code is "d-mmm" needs to have the quotations at the beginning and end Chapter 5 Table 5.3 (pg. 198-199)

Assume we have a number entered into cell A5 on an Excel worksheet. In cell B5, an IF function will be entered to produce an output based on the value in cell A5. If the value in cell A5 is greater than or equal to 70, the output of the IF function should be the words PASS. Otherwise, the output of the IF function should be the word FAIL. Which of the following is the correct IF function for this scenario?

None of these are correct Correct IF fucntion = IF (AS > = 70, "Pass","Fail") must have quotations

The COUNTIF, SUMIF, and AVERAGEIF functions can select specific cell locations from a range to compute an output using ___________ criteria.

ONE Chapter 7 COUNTIFS, SUMIFS, AVERAGEIFS would be more than one criteria if it is a plural version

Assume we are dragging fields into areas such as the rows area or columns area. Which analytical tool must we be using?

PivotTable only PivotTable has rows, columns, and values area PivotChart has legend, axis, and values area Chapter 3

When a column is too narrow for a long number, Excel will automatically convert the number to a series of ____________________________.

Pound signs (##) Chapter 2 "adjusting colums and rows" (pg. 61)

Which of following commands can be found within the "Review" tab on the Excel Ribbon?

Protect worksheets or workbooks Chapter 1 "the excel ribbon" table 1.2 (pg. 20-21)

Two ____________________ should be used if a blank cell is the desired output of the IF function.

Quotations Chapter 6 why box (pg. 257)

Assume we have the following nested IF function: =IF(E10>=100,"BUY",IF(E10<0,"HOLD", "SELL")) If cell E10 had the value of 30 than the output of the function would be:

SELL Greater than or equal to 100 = BUY Less than 0 = HOLD any number between 0 and 100 = SELL

A _______________________________ chart is used to show how a percent of total changes over time.

Stacked column Chapter 9 "stacked column chart for total trends" (pg. 395)

T/F: The status bar is located at the bottom of the excel worksheet and the formula bar is located towards the top of the excel worksheet.

TRUE Chapter 2 "editing data" formula bar (pg. 53) "basic data audits" status bar (pg. 45)

T/F: A chart sheet can only contain a chart and no other data can be added.

TRUE Chapter 3 "the chart sheet" (pg. 93)

T/F: The [Range_lookup] argument of the VLOOKUP function is defined with either "TRUE" or "FALSE".

TRUE Chapter 6 Table 6.3 (pg. 25-260)

T/F: The CONCATENATE function is used to combine data into one cell location.

TRUE Chapter 7 "the CONCATENATE function" (pg. 308)

Assume that in cell P5 that I have the following IF function: =IF(O5>=61,"Hot", "Cold") T/F: Assume that the value in cell O5 is 60. The output of the function will be: Cold

TRUE if cell O5 is greater than or equal to 61 then the output would be HOT If cell O5 is less than 61 then the output would be COLD

T/F: Assume we have an OR function with two logical tests (within an IF function). If one of the logical tests is true (and the other is false) than the function will produce a TRUE output.

TRUE only needs to meet one of the two logical tests to produce a true output

T/F: The COUNT function will ignore blank cells.

TRUE Chapter 4

T/F: Adding capacity to functions is similar to adding blank cells to a function.

TRUE Chapter 6 "adding capacity to function" (pg. 240-241)

Which of the following statements relates more to the COUNT function?

The COUNT function will only count the number of cells in a range that contain numeric data. Chapter 4 "COUNTA and COUNT" (pg. 159)

Which of the following statements is FALSE concerning an amortization table for a loan (assume we are making annual payments and the initial amount of the loan is $100,000, similar to Figure 5.1 in Chapter 5)?

The balance of the loan will decrease by the amount of the principal and interest payments combined. Chapter 5 figure 5.1 (pg. 183)

Which of the following was not a chart you learned in Chapter 9?

Treemap chart Chapter 3

A __________________________ is a valuable addition to a scatter plot chart and is used to estimate or predict where plot points may occur at various points along the X and Y axes.

Trendline Chapter 9 "adding a trendline and equation" (pg 440-443)

Which of the following equations is correct for calculating total sales dollars?

average price per unit multiplied by quantity sold Chapter 1 Avg. price x Qty sold = Total sales

Assume we are creating the "criteria" argument for an IF or IFS function and we have the following: ">=10/24/2021" The above syntax would describe:

cells that contain the date 10/24/20201 or any date after 10/24/2021 will be selected. Chapter 7 table 7.2 (pg. 300)

On a supply and demand curve which axis contains the price?

y-axis figure 9.62 (pg. 440)