1. Part 1. (TCO 1) Given a spreadsheet with the following data,
ABCD
1Amount Invested3%4%5%
2$10,000
3$15,000
4$20,000
5$25,000
6$30,000
7$40.000
Write a formula in cell B2, using mixed or absolute references as necessary, that will calculate the interest on the Amount Invested. You must to be able to copy the formula down and across without making adjustments to it.
Part 2. Given a spreadsheet with the following data:Enter a formula in cell C4 that will look up the account number in cell B4 and return the associated account name from the chart of accounts table. Use mixed or absolute cell references as appropriate so that you can copy down through cell C7 without further correction.
Part 3. Given a spreadsheet with the following data:Enter a formula in cell B11 that calculates the "monthly" payment and displays it as a positive number. Use proper cell references so that your formula can be copied to rows 12 and 13 without further adjustment.
Part 4. Given a spreadsheet with the following data:Write a formula in cell B7 that calculates the total Gross Wages before taxes for the Total Hours Worked. Use proper cell references so that your formula can be copied to cell C7 without further modification.
Question 2. 2. (TCO 3) Michael is a Professor of Mathematics with DeVry University and is going to prepare a series of charts which show the academic success rates of students for each earned letter grade including A, B, C, D, and F letter values. The charts will be presented to all math majors during a special scheduled session and then to all non-math majors during other scheduled sessions. Before any presentation, Michael had a meeting with the Academic Chair, Sally, and the following concerns were expressed. You decide to help Michael create a document formalizing suggestions based on your extensive experience working with Microsoft Excel charts.
Part 1: The Academic Chair loves all charts; however, she instructs Michael to consider if all charts are appropriate for non-math majors. For example, Michael created some box and whisker diagrams also known as Box Plots. Non-math majors may not understand how to read these types of charts. What other types of charts could Michael consider creating to display academic success rates for earned letter values including A, B, C, D, and F grades and why?
Part 2: Michael also created some histogram charts which are simply customized column charts. Since normal column charts are used often even for non-math majors, would you suggest that Michael also show the histogram charts to the non-math majors? Why or why not?
Part 3: Michael also used some well-balanced creativity using color choices including light earthly tone reds and greens. Should Michael consider that some audience members whether math or non-math majors may be colored blind? Why or why not?
Question 3. 3. (TCO 4) You have been hired by Nike Inc. as an Analyst Consultant. Your first task is to determine which shoe style has the highest, average, and lowest sales. Additionally, you have been asked to determine which months have the highest sales. You have been provided a spreadsheet that contains the shoe style and month of sale.
Question 4. 4. (TCO 5) Super Clean Carpet Cleaning business wants to develop an Excel workbook that contains 12 identical formatted worksheets (1 for each month of the year). On these worksheets the monthly expense descriptions are listed in Column A and expense amounts are in Column B. A total of the expenses is the last entry of Column B. The business controller needs to know for tax purposes what the yearly totals are for each expense category and for the total of all expenses for the year. Given this assignment by the business controller, summarize how, using the tools for managing multiple worksheets, you would create the 12 identical worksheets. Also describe how to consolidate this data into a yearly Excel sheet showing the totals of each expense category and the total of all the expenses in the business for the year.
Question 5. 5. (TCO 9) You work for a small shoe factory where all the records are kept in a Microsoft Access Database. Your boss has asked you to create a report that has the following sections:
Provide a chart showing the shoe type/model with most sales as well as the one with least.
Identify costs per month for the making of the shoes per type/model.
Show which month has the least sales to create a better marketing campaign.
Identify which store sells the most shoes to create a final report.
Make sure to describe at least three different formulas or functions you will use to create the analysis needed for the sections above. Explain how you will provide the final report to your boss.