WGU C268 Spreadsheets – Latest 2023 Graded A+

WGU C268 Spreadsheets – Latest 2023 Graded A+ IN Columns AA, BB, CA In Rows 5, 6, 7 In clip art Images What is a possible outcome using Boolean logic Only the numbers 0 and 1 What is possible in freeze panes Freezing the row above where a person is working Auto sum Count, Number, Max Logical And, Not, OR Financial Duration, Coupdays, Effect Date and Time Weeknum, Weekday What is true about correlation coefficients when using the Correl function in Excel? They must be a whole number Which two options are possible under "Review" on the Excel riboon? Protect sheet and Track Changes An administrator wishes to display the number $3.10 in cell A4. How can this goal be met? Select cell A4, select the currency category, then enter the number 3.1 A student noted a correlation between hours of studying and test grades. The student decides to track this correlation coefficient in Excel. How should this be set up? By setting up hours in array A and grades in arrow B, then using the CORREL function A student wants to have text appear in Excel as it would in a business document, with all of the text starting on the left-hand side. Which group on the homepage should be used to accomplish this goal? Alignment A manager has a spreadsheet table with company name, stock prices, and sales dollars. What should the manager do to sort this table by name and then by sales dollars, from largest to smallest? Clink anywhere in the table, click the sort icon, click the name in the sort by dropbox, then click "then by" and choose "sales dollars largest to smallest" The following data is in an Excel Spreadsheet: Company A-Manufacturing-$22,000,000; Company B - Industrial - $12,000,000; Company C - Manufacturing - $14,000,000. Which steps will display only the information for the manufacturing industry? Click anywhere in the table, click the filter button, click the down arrow next to "Industry", and then select "Manufacturing" A doctor wants to know how many patients she has seen this month and their diagnoses. The doctor's assistant puts together a report showing each patient's name in column A and diagnosis in column B. Which steps should the assistant take to obtain a list of patients who have been diagnosed with strep throat? Click in the report, click the filter icon, click the down arrow for the diagnosis column, Cell B3 contains a formula that multiply cells B1 and B2. Which formula is in cell B3? =B1*B2 The accountant for a company wants to create a spreadsheet to calculate sales markup. The costs of these items are located in cells C4 through C30, and the markup percent is located in cell A1. The formula for the markup will be in the range D4 through D30. What does the formula in cell D4 have to be in order to copy it into cells D5 through D30? =C4+$A1 (A1) Name (B1) Sales $ (C1) Commission $ (A2) Rep 1 (B2) $12,500 (C2) (A3) Rep 2 (B3) $15,000 (C3) (A4) Rep 3 (B4) $17,000 (C4) The commission percent of 2% is located in cell A5. What should the fomula be in cell C2 in order to calculate commission dollars and enable copying to cells C3 and C4? =A5+$B2 A local golf course held a charity tournament that was open to all ages. The following information was gathered about the golfers and presented in a table: name, age, handicap, and score. Using the score column, which function will yield the number of scores returned? COUNT A table included information about 1,000 adults who will be attending a sporting event. One column indicated the number of children who will be attending with each adult. If no children will be attending, the column contains an "N" for none. Which function returns the total number of children for all adults attending the event? SUM A table with information from a random survey indicating the age of the participants or an "N' for "no response". Which function will return the number of people surveyed who responded with their age? COUNT A table with information on a company's employee population includes a column with each employee's annual salary. The president of the company would like to know the total salary paid annually by the company. Which function will provide this function? SUM An individual wants to buy a boat. To obtain the money, the individual decides to save $100 per month at a 5% interest rate. Which function will determine how much money the individual will have at the end of two years? FV = Future Value A client wants to borrow $10,000 and pay $200 per month over five years to make a capital purchase. Which function will tell the client the maximum annual interest rate that will meet this payment budget =RATE(60, -200, 10000) An individual agrees to pay a friend $250 per month for 36 months at an interest rate of 4.99%. Which function will provide the present value of these payments? NPV = Net Present Value An individual wants to purchase a golf cart from a neighbor for $3,500. The neighbor agrees to accept monthly payments of $100 at an interest rate of 3%. The buyer would like to determine how many $100 monthly payments will be needed to complete the purchase. Which function will determine how many $100 monthly payments are needed to complete the purchase? NPER = returns the number of periods for an investment based on periodic, constant payments and a constant interest. A company's 4Q 2013 sales are as follows: A1 - Month; B1 - Sales $; A2 - October; B2 - 125,000; A3 November; B3 142,000; A4 December; B4 162,500 Which two formulas will determine the minimum monthly sales for the three months? =MIN(B2:B4) =MIN(B2,B3,B4) Which three formulas will add the sales for 4Q? Info above. =SUM(B2,B3,B4) =+B2+B3+B4 =SUM(B2:B4) Which two formulas count the number of unique part numbers with a quantity >0 =COUNTA (B2,B3,B4,B5,B,6,B7,B8) =COUNTA (B2:B8) Which formula reflects the present value of the investment? B1 - Rate 5%, B2- Years 30, B3 - Monthly Payment $125.00 PV(rate, nper, pmt) =PV(5%/12,(30x12),15) =PV(B1/12,B2*12,B3) Rate - the interest rate, per period NPER - the number of periods for the lifetime of the annuity or investment PMT - an optional argument that specifies the payment per period. The commission rate is stored in cell B2, the commission dollars are in cells D5 through D20. The formulas to calculate the commission amount is in cells E5 through E20. The sales manager does not believe the commission amounts is correct and wants to know which formulas reference cell B2. Which steps should the sales manager take to get the desired information? Click on the Formulas tab, click cell B2, and then click the Trace Precedents icon The owner of a small retail store wants to pay her sales tax, which is 7.5% of her total sales. Her spreadsheet shows monthly sales in cells A20 through L20. The sales tax rate is located in cell B2 (7.5%). The formula to calculate sales tax for each month is located in cells A21 through L 21. What should be done with cell B2 to verify the correct sales tax rate is used for each month? Click cell B2, click the Formulas tab, and then click "Trace Dependents" Cell D21 contains total sales for the current year. Cell E21 contains sales for the previous year. Cell F21 contains the difference between the two years. Cell F21 contains a negative number. The sales clerk knows that this is wrong, because sales increased by $10,000 in the current year, and the clerk wants to see which cells are in the formula for Cell F21. How can the clerk meet this goal? Click in F21, select the formulas tab and then select the trace precedents icon. Cells A1 through A4 contain the values 20, 30, 40, and 50 respectively. Which two options result in an answer of TRUE being displayed? =SUM(A1+A2+A3+A4)=140 =AVERAGE(A1:A4)=35 The value in cell A1 is 10. Which NOT formula gives the result of TRUE? =NOT (A1=8) A cook is planning a lunch for eight people. A2 - Chichen primavera; B2 - 4; A3 - Beef bourguignon; B3 - 5; A4 - Salmon filet; B4 - 3; A5 - Veal parmesan; B5 - 2; A6 - Vegetable lasagna; B6 - 3 =(SUMIF(A2:a6,"Salmon filet",B2:B6)+SUMIF(A2:A6,"beef bourgignon", B2:B6)>8) SUMIF - adds the cells specifically by a given condition or criteria A student is interested in purchasing a laptop computer. The student's goal is to spend a maximum of $40 per month towards the purchase. Presuming no down payment and no interest plus monthly payments for 18 months, the student wants to determine which laptop the student can afford. Laptop 1 - $900 Laptop 2 - $700 =700/18<=40 An individual has a goal to set 10 appointments for each month in 2013. Which formula will show if the individual met the goal in January? =IF(B2<10, "Did Not Meet Goal","Met Goal") Which formula displays a sale meeting the goal of $800? =IF(B2>=800,"Met Goal","Did not Meet Goal" B2 - $1,024 Which formula, entered in cell C2, will reflect sales greater than or equal to #3,000 at "Y" and all others as "N"? =IF(B2>=3000,"Y',"N" ????? Which formula will reflect the appropriate letter grade for the student's percentages? B2 - 76; B3 - 51, B4 - 93, B5 - 85, B6 - 62, B7 - 98 =IF(B2>89,"A",IF(B2>79,"b",IF(B2>69,"c",IF(B2>59,"d","f")))) A data set in Excel includes rows for inventory item number, price, and quantity on hand for a golf shop. The data set has been named "inventory value". The goal is to create a spreadsheet that will retrieve the item number, price and quantity on hand upon entering the item description of a column. Which formula should be used to accomplish this, using the named range? HLOOKUP ?????? Looks for a value in the top row of a table or array of values and returns the value in the same column from a row you specify. Cells A1 through A5 contains the salespeople's names and cells B1 through B5 contain their May sales: A1 - Person A; B1 - $5,000; A2 - person B; B2 - $6,000; A3 - Person A; B3 - $4,300; A4 - Person A; B4 - $3,800; A5 - Person B; B5 - $2,500 =SUMIF(A1:A5, "person B", B1: B5) A restaurant keeps track of daily sales during the week. Cells A1 through A7 list the day of the week, and cells B1 through B7 list that day's sales: Which excel function will show the total sales for the days that made at least $10,000 in daily sales? =SUMIF)B1:B7,">10000" A grocery store wants to track the sales of certain items. Which Excel function will show how many food types sold 500 or more items? =COUNTIF(C1:C6,">=500") An accountant wants a spreadsheet that limits user input to a pre-chosen set of numbers that can be shown in a drop-down list. This set of numbers is given in the cell range A1:A7. How should the accountant restrict user inputs using data validation according to this criteria? Settings tab, "List" for Allow, "A1:A7" for Source An accountant wants a spreadsheet that displays customers' requests as whole numbers (zero or greater) for the quantities ordered. How should the accountant create a message that alerts users after they input invalid data? By choosing the Error Alert tab, checking the box next to "show error alert after invalid data is entered, and typing the message in "error message". An individual's boss requires him to place the date and time on each of his spreadsheets. Which formula should he use? =NOW() An analyst is presented with a list of account numbers displaying 20 digits, starting in cell A2. The analyst only needs the last 10 digits. Which formula will show the information the analyst needs? =RIGHT(A2,10) I lik =LEFT(A2,5) I like to eat ice cream on the 4th of july (all lowercase) =LOWER(A2) I Like to Eat Ice Cream On The 4th Of July =PROPER(A2) I LIKE TO EAT ICE CREAM ON THE 4TH OF JULY =UPPER(A2) Which two chart types can be used to present a president with the total sales of a company over the last four years? A column chart and a line chart ??? Which chart type displays each individual investment compared to the entire investment portfolio at a given point in time? A column chart or a stacked column chart (comparing) The analyst has added bullet points on the right of the chart showing what each color reflects. What is the name of this graphing feature? Legend Management asked the analyst to add the values of each data element to the chart. What charting feature accomplished this request? Data Labels Management asked the analyst to label each color to display what the color means. What charting feature did the analyst use to complete the request? A data label or A legend ??? Management would like the analyst to show the data in the chart. Which charting feature should the analyst use to complete this request? A data label. A client would like to include the percentage data points on the pie chart. Which feature should be used? A data series Which series will reflect the correct mobile phones data? B7:M7 Every good spreadsheet model will have three common elements Changeable inputs, outputs and intermediate calculations What is a characteristic of a changeable input in a spreadsheet model It includes any element of the process that may change Which output can be used to build a model for an automobile loan approval? Financial decision --- Outputs represent the outcome of the process that is modeled in the spreadsheet What is a valid use of a circular reference in a spreadsheet model? Two different calculations that must be completed simultaneously and depend on the other's calculation's solution An owner would like to purchase a building, and the bank has presented several interest rate options. A spreadsheet exists showing the amount of money available per month and displays it as an income statement complete with formulas. The owner would like to use Excel to present the interest rate options with their respective monthly payments. Which function in Excel will complete this task? The Scenario manager -- is useful in examining the impact of several inputs to a spreadsheet model changing at the same time. SM is sophisticated enough to allow you to create a number of different scenarios based on different states of the same set of inputs. In this way, you can compare a number of different hypothetical situations at the same time. A student has a loan with a balance of $20,000 and payments are $333.33 each month. Another student can afford $250 payment and would like to know the maximum that can be borrowed. Which what-of analysis tool should the student use? Goal seek The monthly financial information of a person interested in buying a house is reflected in this template used by a banker to determine loan eligibility. What what-if analysis tool did the banker use? The Scenario manager A manager would like to see a compilation of all the different financing options available for their upcoming machine purchase and would like them presented in a concise report that displays all of the options at the same time. Which what-if tool should the analyst use to achieve this goal? The Scenario Summary A list of monthly sales is displayed in multiple worksheets, one tab for each month. Each range of data is named with the three character month abbreviation. Management wants to identify the largest sale of the year. Which formula will display this information? =MAX(JAN,FEB,MAR,APR,MAY,JUN,JULY,AUG,SEP,OCT,NOV,DEC) An owner of a company buys Product A from four different vendors. the purchases made for each vendor last year are displayed in separate tabs in Excel. Each tab's set of purchase order data is saved as a named range, "V1PA" , "V2PA", "V3PA", "V4PA" The owner would like the analyst to identify the largest purchase order from last year. Which formula accomplishes this goal? =MAX(V1PA!,V2PA!,V2PA!,VAPA!) An owner of a company buys Product A from four different vendors. the purchases made for each vendor last year are displayed in separate tabs in Excel. Each tab's set of purchase order data is saved as a named range, "V1PA" , "V2PA", "V3PA", "V4PA" The owner would like the analyst to summarize all purchase data for Product A on a new tab, adding up the cost of all purchase orders from last year. Which formula accomplished this goal? =SUM(V1PA+V2PA+V3PA+V4PA) The loan amount is $85,000, which is 80% of the selling price. The remaining 20% is the down payment. What is the down payment on the house, using the iterative calculation and rounding the answer to the nearest dollar? 17,000 A person is interested in knowing if there is a correlation between the sky color and their desire to mow the lawn. This person uses the =CORREL function in Excel to determine the correlation. The correlation coefficient is .274. What type of relationship exists between the sky color and desire to mow the lawn? WEAK <.3 An auto mechanic is interested in knowing if there is a correlation between the number of teenage drivers and car repairs. The mechanic uses the =CORREL function to determine the correlation coefficient of .234. The mechanic then uses the =FORECAST function to predict the number of repairs based on the number of teenage drivers. How reliable is the auto mechanic's forecast? Unreliable, due to a weak correlation What is the benefit of using the Values element in Pivot Tables? It summarizes using SUM and other calculations What is the benefit of using Pivot Tables? It can perform three additional calculations simultaneously on the Pivot Table data. Although it is difficult to visualize examining data from more than two dimensions at the same time, it is possible to do so in a Pivot Table. What is the benefit of using the Pivot Table values? It defines which data will be displayed. What is a benefit of using a slicer in a Pivot Table? It applies additional filtering of a selected data field. An analyst is assigned the task of creating a Pivot Table of data for a manufacturing company. Which steps need to be taken to create a Pivot Table that shows gross pay by a department? Click on a cell in the spreadsheet containing the table or data, click on Insert, Click Pivot Table, verify or modify selection in table range box, click OK drag cell B155 to the Row Labels area, drag cell C155 to the Values area. Which two options are available in the Solver parameters dialog window for optimization analysis? MAX and Value Of and MIN (set Objectives)