CGS 2518 Chapter 2

Explain what a "what-if" analysis is by giving an example based on the worksheet in Question 11.

"What-if" analysis is a scenario based analysis where we can alter few values in memory and identify their effect on a given cell value. To see the change in total if Lexus is priced at $32,075 and $45,000 we can create multiple scenarios each having different values to see the effect on total. Go to Data Tab > What if analysis > Scenario Manager, below pop would appear press "add", name the scenario here as "Lexus--Original", in the next pop up after clicking OK enter the value for this scenario, click OK to confirm then scenario manager would display, add one more scenario as "Lexus--Marked Up", enter scenario value as $45,000, click OK to confirm, now one can switch between these values by clicking on "SHOW" button and below two pics depicts these values with two Lexus prices.

Using the worksheet shown in Question 11, write a formula to determine the value of the third least expensive car.

=SMALL(B2:B10,3)

The formula =ROUND(710.52,-1) results in what precise value?

710

What is the symbol for the less than or equal to relational operator in Excel?

<=

What is the symbol for the not equal to relational operator in Excel?

<>

What formula could you write to calculate the mean of the following data set: 4,10,8,6,2,4,14?

=AVERAGE(4,10,8,6,2,4,14)

Write a formula to automatically determine the average price of only GM vehicles using the worksheet in Question 11. This formula should work even if the data is later modified.

=AVERAGEIF(A2:A10,"GM",B2:B10)

If each car shown in Question 11 is marked up between $100 and $200 in dollar increments, what function could be used to randomly assign the amount to be added to the car price in this formula?: =B2+ ______

=B2+RANDBETWEEN(100,200)

Review the following worksheet, and then use the COUNTIF function to write a formula that determines the number of Ford cars on this list.

=COUNTIF(A2:A10,"Ford")

Using the worksheet showing in Question 11, write a formula to automatically determine the total value of all GM cars. This formula should work even if the data is later modified.

=COUNTIF(A2:A10,"GM",B2:B10)

Using the worksheet shown in Question 11, write a formula to automatically determine the number of cars that cost more than $20,000.

=COUNTIF(B2:B10,">20000")

What is the median value of the data set given in Question 1?

=MEDIAN(4,10,8,6,2,4,14)

What is the mode of the data set given in Question 1?

=MODE(4,10,8,6,2,4,14)

What formula could you write to average the values in cells B2 through B10, excluding blank cells, rounded to the nearest whole number?

=ROUND(AVERAGE(B2:B10),0)

Write a formula to round down 63.34% to the nearest percent. (value is written in cell G1)

=ROUND(G1,2) it is necessary to use '2' instead of '0' places as a parameter because percentage is considered as calculated value here. (ex: 63.34% is considered as 0.6334 in actual)

Write a formula to determine the total value of only vehicles priced below $20,000. This formula should work even if the data is later modified.

=SUMIF(B2:B10,"<20000") or =SUM(B2:B10,"<20000",B2:B10)

In the chapter, the original labor rate for QC inspectors was $35 per hour. However, due to a contract renegotiation, this value is now $40. What mathematical expression could you use to determine the percent increase in labor costs?

For example if cell B2 contains the original QC labor rate and cell C2 contains the current labor rate, then the formula for calculating the percent increase in labor cost is: ((C2-B2)/B2)*100

Explain what a Goal Seeker is by giving an example based on the worksheet in Question 11.

Goal Seeker is an entirely different analysis compared to "What-if" analysis. It is mainly based to know what value is required if attain a required target. Example: current total is $188,556, what is the cost of Lexus car to be if this total cost should become $175,000? Go to Data Tab > What-IF Analysis > Goal Seek, pop-up appears select the total value address (B11) in Set Cell (target cell) and required value in "To Value" text box (target value) and fill in the address of Lexus price ($B$4) in "By changing cell:" and click OK, Goal Seeker would confirm if there is a definite solution to the given criteria and update the sheet.

The formula =RAND() gives what result?

RAND function generates a random decimal number each time the file is opened. RAND() produces only decimal values ranging between zero '0' and one '1'.

The data set given in Question 1 has a standard deviation of 4.14 compared with another data set that has the same mean but a standard deviation of 2.5. What general differences would you expect to find between the two sets of data?

Std. dev of Set A= 4.14 Std. dev of Set B= 2.5 General differences include: 1. Values in Set B are close to the mean, but values in Set A are more dispersed. 2. Std. dev gives the value of the amount gap b/w the most occurring and the least occurrence values in the given sets, whereas mean is only the average of any given values irrespective of their occurrence times in a given set. 3. Mean does not convey a gap between the smallest value and the largest w/ comparison to their occurrence in a given set; whereas std. dev takes into account the size of the given numbers too. 4. Std. dev occurs based on the frequency and size, mean is only a average to determine the average point in given data.

When you use the Increase Decimal button in the number group on the HOME tab, the precise value in the cell is modified. True or False?

True