CGS2518 EXAM 1, PT 2, EXCEL PORTION
Back on the Survey worksheet select the range J4:J8. Enter a function in the range J4:J8 to calculate the frequency of service ratings from 1 to 5 received by all restaurants.
=FREQUENCY(data_array, bins_array) data array= reference to a set of values for which you want to count frequencies bins array= reference to intervals into which you want to group the values in data_array EXAMPLE) =FREQUENCY(E4:E33, G4:G8) E4:E33 = data array = service data range G4:G8= the ratings 1-5, aka the interval into what we want to group Don't have to use absolute referencing?
CALCULATING HIGHEST VALUE
=MAX(A1:A30)
CALCULATING LOWEST VALUE
=MIN(A1:A30)
Click the Wait Time worksheet and enter a function in cell F6 that calculates the wait time value of the first quartile.
=QUARTILE.EXC(array, quart) array= the cell range of numeric values for which you want the quartile value quart= which value to return EXAMPLE) =QUARTILE.EXC($B$3:$B$12, E6) B3:B12=the range of wait times E6= Quartile 1 Use absolute referencing for the range
Use the RANK.AVG function in cell N3 to calculate the rank of store 251 based on the averages in the range M3:M5.
=RANK.AVG(number, ref, [order]) EXAMPLE) =RANK.AVG(M3, $M$3:$M$5) number= M3= average service satisfaction for store 251 *this is the numbers who's rank you want to find ref= a reference to a list of numbers= M3:M5, which is the range of average services for each store Use absolute referencing for the range, not for the number
Click the Wait Time worksheet and enter a function in cell E3 to calculate the standard deviation between satisfaction scores of the wait time sample. You do not have to edit the number of decimal places displayed.
=STDEV.S(number 1, number 2) STDEV.S= Sample STDEV.P= Population EXAMPLE) =STDEV.S(C3:C12) C3:C12 = satisfaction scores of the wait time sample
In the Annual Volume column B, create a formula that adds the "Sales Volume" columns together from the 4 previously created Q1 Actual, Q2, Q3 & Q4 worksheets. Make sure that the values generated by the formulas in these cells will automatically update if any of the input values are changed on any of the 4 individual quarterly sheets at a later time.
=SUM('Q1 Actual:Q4'!B4) Q1 actual - Q4 are the sheets were using, while B4 is the sales volume from the Q1 actual sheet You add exclamation point when you use sheet names in a formula Excel will enter these automatically, if you type an equal sign and click on another sheet tab and then on a cell A1 in that sheet and press enter you will see
CALCULATING TOTALS
=SUM(A1:A30)
At the bottom of this table in row 29, add a totals row that calculates up the totals for each of the columns B through G.
=SUM(B3:B29) for each column
In cell G3, create a formula that sums the sales to dealers for the entire year by adding the cells C3, D3, E3 & F3. Copy this formula down for all of the models.
=SUM(C3:F3)
in cell F10, add the minimum down payment located in cell C5 with the additional down payment in F5 to determine the total down payment
=SUM(F5, C5)
Insert a function to display the current date:
=TODAY()
Multiply the negotiated cost of the vehicle by the sales tax rate to determine the sales tax
Literally just used =C4*C10, which was the negotiated cost number times the sales tax rate number
In cells L105 - L108 create formulas to use these daily demand totas in cells L4 - L103 to calculate the daily mean, mode, median, and standard deviation(there are several different flavors of the STDEV formula so pick the one for a sample of data) for the combined requirements of all 10 customers. Formal all 4 of these cells to be a Number with 2 decimal places
MEAN =AVERAGE(L4:L103) MODE =MODE.SNGL(L4:L103) MEDIAN =MEDIAN(L4:L103) ST DEV =STDEV.S(L4:L103) sample^ deviation, no referencing
In cell F12, use the PMT function to determine the periodic loan payment. format the results to appear as a *positive number*
PMT function calculates the payment for a loan based on constant payments and a constant interest rate. =PMT(rate, nper, pv, [fv], [type]) rate= interest rate for the loan. = APR based on credit rating/ the number of payments paid per year nper= the total number of payments for the loan pv= present value, or total amount that a series of future payments is worth now fv= optional EXAMPLE) =PMT(F4/12, C11*C12, F11)*-1 ^F4/12 = APR percent based on credit rating/ number of payments per year C11*C12 = Term of loan in years (5) * the number of payments per year. F11= "the amount of the loan" To make it as a positive number, multiple the parentheses by a -1
In the "Q1(Jan-Mar) Sales to Dealers" column C, create a formula that shows the corresponding "Total Sales to Dealers" column from the Q1 Actual worksheet, again ensuring that these values will automatically update if any of the input data changes on that Q1 Actual worksheet.
='Q1 Actual'!G4 G4= total sales to dealers from the Q1 sheet
In the "Q2(Apr-Jun) Sales to Dealers" column D, create a formula that shows the "Total Sales to Dealers" column from the Q2 worksheet, again ensuring that these values will automatically update if any of the input data changes. Repeat for columns E & F that should get the corresponding values out of the Q3 & Q4 worksheets.
='Q2'!G4 Same as the one above
CALCULATING AVERAGES
=AVERAGE(A1:A30)
Similarly to how you did the previous step, do a similar analyses in cells I2:I6 that will AVERAGE the number of days in inventory for each part sold over the 4 day period.
=AVERAGEIF(range, criteria, average_range) average range matches average # with average #
In cell H3, calculate the percentage of how much that model's Q1 sales will contribute to the yearly total sales to dealers (the value in cell G29), In columns I, J, K & L repeat for quarters 2, 3, 4, as well as the annual values. If you use absolute referencing correctly, you will only have to create one formula in H3 (assuming that row 3 is your first row containing model/sales data) for this calculation and copy it down the column to calculate the percentages for each of the corresponding models, and across the row to calculate the percentages for the corresponding time frames. Display all percentage values to 2 decimal places (e.g. 5.20%)
=C3/$G$29 C3= Q1(Mar-Jan)Sales to Dealer G29= Total Sales to Dealers Total Absolute referencing =dollar sign on each one
In cell F11, enter a formula to determine the difference between the negotiated cost of of the vehicle and the total down payment
=C4-F10 C4= negotiated cost of the vehicle and F10 is total down payment
In cell E35 write a formula that provides the number of different models that are available regardless of whether there were sales for that model or not (hint: you will need to use the "Model" column in order to do this because that is the only column that will always contain data for each model). You will need to use the formula that counts cells containing text (i.e. MO307).
=COUNTA(A1:A30) The COUNTA function counts the number of cells that are not empty in a range.
In cell G2 create a formula that counts up all of the Bearings that were shipped during this 4 day timeframe (i.e. the data contained in the cells you just pasted into columns A, B & C). If you do this correctly you should be able to write one formula in cell G2 that you can copy down from G2 to G6 and each cell should accurately count how many Bearings, Timing Belts, Air Filters, Fan Belts and Electronic Boards were shipped during those 4 days WITHOUT you having to edit the formula in any way! This will take your knowledge of cell referencing allowing you to lock down the right parts of your cell references with the $ sign as well as referring to the adjacent cells that contain the names of the 5 items you are trying to count!
=COUNTIF(range, criteria) range= the descriptions criteria= bearings EXAMPLE) =COUNTIF(A$2:A$1051)
enter a formula to determine the total monthly payment by adding the monthly payment in cell F12 and the monthly sales tax in cell F13.
=F12+F13
enter a formula to calculate the monthly sales tax based on the total sales tax located in cell F6, and the total number of payments (C11*C12)
=F6/(C11*C12) =Sales Tax/(Term of loan in years * # of payments per year)
Insert a lookup function that uses the credit rating to determine the minimum down payment based on the table array A15:C18. Include the range lookup argument to ensure an exact match. Multiply the function result by the negotiated cost of the vehicle.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Actual example) =VLOOKUP(C7, A15:C18, 2, FALSE)*C4 He gives most of the information in the instruction, just match up the credit rating cell, table array thats given. column index number is the number of the column that the down payment was in, which was B, or 2. FALSE for exact match, and multiple by negotiated cost of the vehicle which is in cell C4
Enter a lookup function to display the interest rate of the loan. The function should reference the borrowers credit rating and the table array in range A15:C18. Include the range_lookup argument to ensure an exact match.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Actual example) =VLOOKUP(C7, A15:C18, 3, FALSE) *only use false if he asks for an EXACT match, if he does not mention it, just do not include the range lookup
On the SimulationData1 worksheet, in cell L4, use a formula to calculate the total demand by day for all 10 customers. Copy this formula down for all 100 simulated days. Find your busiest days: In cell M4, construct a formula that takes the daily needs in L4 and compares it to see where it ranks compared to all of the other 100 simulated days. Your formula should be constructed so that you can write the formula once in M4 and then copy it down to the bottom. The formula should work so that the day with the least total demand has a rank of 1.
First part: =SUM(B4:K4), then copy it down all the way Second part: use a formula that takes the daily needs in L4 and compares it to see where it ranks compared to all the other 100 simulated days So we use =RANK.EQ(number, ref, [order]) number=whose rank we wanna find number= L4 (said in instruction) ref= a list of numbers ref= referencing L$4:L$103 order=a number specifying how to rank a number order= 1 EXAMPLE) =RANK.EQ(L4,L$4:L$103, 1) MIXED referencing
On the BearingData worksheet, use the existing 31-day data for these bearings for each customer to calculate the high and low limits of the bearing demand by customer. In rows 36 & 37 create something that looks like this (except your formulas should show the right values):
High and low limits = max and min formulas Low =MIN(B4:B34)(copy across) High= MAX(B4:B34)(copy across)
In the Survey worksheet, enter a conditional function in cell M3 to calculate average satisfaction of service for store 251 (cell L3). Format the results with the Number Format with two decimal points.
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. Use AVERAGEIF function =AVERAGEIF(range, criteria, [average_range]) EXAMPLE) =AVERAGEIF($B$4:$B$33, L3, $E$4:$E$33) range = $B$4:$B$33, =the total range of stores criteria = L3 = store 251 average range = $E$4:$E$33 = service scores in the range they're asking for Use absolute referencing on this because both column and row can't change
In cell P4, create a formula that provides the largest daily need found in cells L104 - L103. Do not use the MAX formula here because after you calculate the largest value in P4, you need to copy this P4 formula down through P8 in order to find the 2nd, 3rd, 4th and 5th largest daily needs and the MAX formula only provides... duh. the MAXimum value and not the 2nd, 3rd, etc. largest. Hint: If your formula in P4 is correct with proper referencing and no hard-coded numbers you will just be able to copy it down with no effort). If you find yourself having to edit the formula in P5, P6, P7, P8 in order to get it correct then you didn't use proper referencing! Repeat this process in cells Q4:Q8 in order to find the 5 smallest values from the same range.
Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score. =LARGE(array, k) array= The array or range of data for which you want to determine the k-th largest value. k=position (from the largest) in the array or cell range of data to return. EXAMPLE) =LARGE(L$4:L$103, O4) =SMALL(L$4:L$103,O4)
Enter a function in cell M11 to calculate the number of survey responses in which restaurant 251 (in cell L11) received a 2 or less (in cell L10) in wait time and service.
The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met. =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, criteria_range3, criteria3) EXAMPLE) =COUNTIFS($B$4:$B$33, L11, $C$4:$C$33, $L$10, $E$4:$E$33, $L$10) criteriarange1= all of the stores range criteria1= store 251 criteriarange2= wait time ranges criteria2= <=2 criteriarange3= service ranges criteria3= <=2 Use completely absolute referencing except for the store in L11
For % of Total Volume in column H, first calculate the total volume of all cars sold in cell B30. Then in cell H4, for that model, find the % of its volume as compared to the Total Volume now located in B30. For full credit, you should write your formula in H4 using proper cell referencing (hint: Mixed referencing puts the $ sign in front of either the column OR the row and absolute referencing puts $ signs in front of both column AND row.
first part= calculate total volume in B30 =SUM(B4:B28) (the whole column) "find % of volume compared to total volume" = we want percent of volume to change, but total volume to stay the same. *MIXED REFERENCING* H4 should be =B4/B$30 B4 being the first one and B$30 being the total
On the Simulation worksheet, use the high/low limits you just calculated to simulate daily requirements for each customer to obtain a combined daily requirement. Assume that the daily requirements will vary for each customer randomly between the high/low limits you have calculated from the existing 31-day data for that customer. Generate the random data for 100 instances (days). It should look something like the image (in the Asset folder) CGS2518A2pic1.jpg with each cell containing a randomly generated number between the min and max for that particular customer. For instance, if the min/max values for customer "X" from the BearingData worksheet are 1 & 16, then the 100 cells below customer "X" should contain randomly generated daily demands between 1 & 16. Hint: For full credit, your formula should constructed such a way that you create the formula one time in cell B4 and you should be able to copy it down and across to fill up your entire table with randomly generated data. Disclaimer... the numbers in the following charts change from semester to semester so please use the customer numbers from this semester's data file as opposed to the numbers displayed in this screenshot file.
for generating random data - Returns a random integer number between the numbers you specify =RANDBETWEEN(bottom, top) bottom= smallest integer it will return top=largest integer it will return We are asked to use the high low limits we calculated, so: =RANDBETWEEN(BearingData!B$36, BearingData!B$37) copy it to 100
create a formula that calculate the Profit Margin % and copy that formula down to E28. Format the values to show the percent value with zero decimal places (i.e.24%)
profit margin = (price-cost)/price EXAMPLE) = (D4-C4)/D4 =(selling price to dealer - mfg. cost per vehicle) / selling price to dealer
Similarly to how you did the previous step, do a similar analyses in cells H2:H6 that will sum the total values of all of the sales for those 5 parts over the 4 day period.
to sum the values in a range that meet criteria that you specify. =SUMIF(range, criteria, [sum_range]) range= the labels criteria= defines which cells will be added sum range= The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied). sum range matches value with value mixed referencing
In cell F4, create a formula that calculates the Total Cost and copy that formula down to F28.
total cost = (cost * volume) EXAMPLE) =(C4*B4) C4= cost per vehicle, B4= sales volume
In cell G4, create a formula that calculates the Total Sales to Dealer using the logic above and copy that formula down to G28
total sales = (sales volume * selling price) (amount * price)
