Instructions
• You need to use Microsoft Excel (either the Windows or Macintosh version) for this assignment. Some functions may work differently on the Macintosh version (especially in Excel 2008 and Excel 2011). If you do not have Microsoft Excel, you can find it in any of the computer labs (locations at https://offices.depaul.edu/information-services/services/labs-classrooms/computer-labs/Pages/default.aspx) or you can download the free student version by following the instructions at https://offices.depaul.edu/information-services/services/Software/Pages/Office-365-Education-Plus-for-Students.aspx.
• Download Employee_Data.xlsx, ThsirtProduction_Data.xlsx, SpotlightVideo_Data.xlsx, MusicPlayerz_Data.xls, Grades.xlsx, Travel.xlsx, and Time.xlsx workbooks from Desire2Learn (D2L). Submit the files with the same name prior to the final due date (July 22, 2022)
• You can find Step-by-Step Problem Instructions in Content > Excel Assignment > Module Assignments: Step by Step Problem Instructions. I have separated these by operating system (Macintosh or Windows) and Excel version number (Excel 2016, Excel 2019, etc.).
• Save each answer to a separate worksheet within the workbook with the appropriate name.
• Full credit for each worksheet/answer is 4 points.
• Note that you must use the specified function, formula, or technique to earn full credit. Just getting the answer does not fulfill the requirements. So, for example, when creating the answers for the MIS140 Grading questions, you need to use VLOOKUP, not just type in the grades.
• Note that you must answer all parts of the question. For example, in the B&B Travel questions, you must show your analysis and recommendation. One or the other will earn partial credit. Also, you may need to create multiple graphs to fully answer some questions, so read the questions carefully.
• Please upload the workbooks to the Excel Assignment Submission folder in D2L (Desire2Learn).
Spotlight Video Rentals
Spotlight Video, a premier video rental company in Denver, Colorado, offers the latest selections in DVD, game, and VHS releases. After customers view DVDs and VHS tapes a certain number of times, their quality deteriorates to the point that Spotlight Video considers them defective. Furthermore, some customers own defective DVD and VHS players that can ruin Spotlight Video’s merchandise.
So, Spotlight wishes to maintain a certain level of acceptability based on number of rentals and number of defects. Spotlight hired you to create a spreadsheet that evaluates each DVD and VHS using the data in SpotlightVideo_Data.xlsx which shows the number of times each title was rented and the number of reported defects for each title.
• Use the IF function to write a conditional expression that examines the criteria mentioned above. Spotlight wants to know how many (and which) videos they will need to replace if the percent acceptable is 80 percent or above. So, if the video has a percentage above 80 percent, they want to flag this video as REPLACE. Call this worksheet “80”.
• Use the IF function to write a conditional expression that examines the criteria mentioned above. Spotlight wants to know how many (and which) videos they will need to replace if the percent acceptable is 95 percent or above. So, if the video has a percentage above 95 percent, they want to flag this video as REPLACE. Call this worksheet “95”.
Finding Employee Information
SecureIT, Inc., a small computer security contractor, provides computer security analysis, design, and software implementation for commercial clients. Almost all of SecureIT work requires access to classified material or confidential company documents. Consequently, all security personnel have clearances of Secret or Top Secret. Some have even higher clearances for work involving so-called black box security.
While most of the personnel information for SecureIT resides in database systems, they maintain a basic employee workbook for quick calculations and ad hoc report generation. As a small company, SecureIT can take advantage of Excel’s excellent list management facilities to satisfy many of its personnel information management needs. SecureIT provided a sample workbook, Employee_Data.xlsx, for youto produce several worksheet summaries.
• Create a list sorted by last name and hire date. Call this worksheet “Hire”.
• Use a custom sort to list by department in this order: Marketing, Human Resources, Management, Engineering, and Accounting. Total the salaries by department and the grand total of all departments. Call this worksheet “Salaries”.
• Use a filter to show only those employees in the Engineering department who have top secret clearance (TS). Call this worksheet “Top Secret”.
• Use a custom filter to display those employees born between 1960 and 1975 (inclusive). Call this worksheet “Birth”.
• Create a pivot table that shows average salary by gender within the department. Call this worksheet “Gender”.
• Create a pivot table that shows the number of employees by clearance. Call this worksheet “Clearance”.
What Production Problems Does t-shirts.com Have?
Established in 2002, t-shirts.com rapidly became the place to find, order, and save on T-shirts. The company manufactures its own T-shirts, a huge selling point. However, the quality manager for the production plant, Kasey Harnish, noticed an unacceptable number of defective T-shirts during production. You must assist Kasey in understanding the problems. He suggests using formatting, pivot tables and other Excel Decision Support Tools to perform an analysis and provided you with a data file, TshirtProduction_Data.xlsx.
The following is a brief definition of the information within the data file:
Definitions
Batch: A unique number that identifies each batch or group of products produced.
Product: A unique number that identifies each product.
Machine: A unique number that identifies each machine on which products are produced.
Employee: A unique number that identifies each employee producing products.
Batch Size: The number of products produced in a given batch.
Num Defect: The number of defective products produced in a given batch.
• Use conditional formatting to highlight all batches where the number of defective products is greater than 10. Call this worksheet “Conditional”.
• Create a pivot table that shows the number of defective products produced by machine by product and call the worksheet “Machine”.
• Create a pivot table that shows the number of defective products produced by employee by product and call the worksheet “Defective”.
• Create a pivot table that shows the total number of products produced by each employee and call the worksheet “Total”.
• Create a pivot table that shows the total number of products produced by each employee as well as the total number of defective products produced by each employee and call the worksheet “Employee”.
• There are an unacceptable number of defective products being produced. Your task is to use some combination of any Excel formula or function to illustrate the problems, perhaps product, employee, machine, or even batch size. Call this worksheet “Unacceptable”. Based on your analysis, recommend how to correct the problems in a paragraph or two in the Unacceptable worksheet.
MusicPlayerz Sales Projections
MusicPlayerz, headquartered in Morrison, Colorado, distributes MP3 players to retail warehouses scattered throughout the western United States. MusicPlayerz chief procurement officer Julianne Beekman oversees the purchase and distribution operations for all divisions from the Morrison office. MusicPlayerz also maintains a small Web site from which it sells to consumers. While not a large part of the revenue stream, the online store is an essential and growing part of MusicPlayerz’s business. Julianne developed a sales report for the coming year, using the current year’s figures as the basis of the projection. Julianne wants to investigate sales predictions based on the assumption that next year’s wholesale sales will increase by 10 percent for each product included in the projection. Julianne has asked you to complete the worksheet she has provided you, MusicPlayerz_Data.xlsx, for her presentation at the annual board meeting next month.
• Calculate Projected 2021 Sales (10% more than 2020 sales), Gross 2021 Sales, and 2021 Profit. Also, create an appropriate chart comparing Actual 2020 Sales to Projected 2021 Sales. Calculate % of 2021 Sales and create an appropriate chart. Call this worksheet “2021”.
EXPLORING INFORMATION AT B&B TRAVEL
Benjamin Travis and Brady Austin own B&B Travel Consultants, a medium-size business in Seattle with several branch offices. B&B specializes in selling cruise packages. Ben and Brady maintain a workbook called Travel.xlsx that contains information for each cruise package. They have some decisions to make about how to best allocate their funds. In order to earn full credit for your answers in this section, I need to see the use of the decision support features of Excel, and you need to make a specific recommendation (with exact name or location) in two to three sentences. You can use whatever you think appropriate.
Definitions
Location #: Identifies each office recording a sale
Travel Agent #: Identifies each travel consultant recorded the sale
Cruise Line: Identifies each cruise line sold as a package
Total Package Price: Price charged to the customer
Commission: Amount of money B&B made from the sale.
• Analyze the location data and make recommendations as to which location(s) should be closed. Call this worksheet “Location”.
• Analyze the travel consultant data and make recommendations as to which travel consultant(s) should be downsized. Call this worksheet “Consultant”.
• Analyze the cruise line data and make recommendations as to which cruise line(s) should be focused on. Call this worksheet “Cruise”.
MIS140 GRADING
As a Teaching Assistant in MIS140, you need to calculate the quiz grades and final grades for all students using Grades.xlsx. The final grades include the quiz scores as well as individual assignments and a final project assignment. Note that you wish to impress your professor who likes functions such as LOOKUP because you wish to receive a good recommendation and a raise.
• Calculate the class averages and letter grades that the quizzes correspond to for all students. Call this worksheet “Quizzes”.
• Calculate the class average and final letter grades for all students. Also, create an appropriate chart showing the distribution of grades. Call this worksheet “Final Grade”.
EXPLORING TIME VALUE OF MONEY
For this section, use workbook Time.xlsx. This section is slightly different in that you have to create the formulas in each worksheet and then answer additional questions. For full credit, I need to see the use of the function and formula features of Excel. You must use the appropriate function/formula to earn full credit and the answers must be in the highlighted cells with a surrounding border.
Definitions
Cash Flow: Earnings before depreciation, etc.
Discount Rate: Interest rate
Future Value: The value of an asset at a certain point in the future when its return is a known factor.
Period: The time span in which certain financial events took place.
Present Value: The current worth of a future sum of money or stream of cash flows given a specified rate of return
Formulas
Future Value
Annuity Future Value: Equals (Payment) * (((1 + (Discount Rate/Period)) ^ (Number of Periods)) – 1) / (Discount Rate/Period)
Future Value: Equals (Cash Flow) * ((1 + (Discount Rate/Period)) ^ ((Number of Periods) – (Current Period))
Future Value of Each Cash Flow: Equals (Cash Flow) * ((1 + (Discount Rate/Period)) ^ ((Number of Periods) – (Current Period))
Loan Amortization
Beginning Principal Balance in Year T: Equals (Beginning Principal Balance in year t-1) – (Principal Component in year t-1)
Interest Component in Year T: Equals (Interest rate/year) * (Beginning Principal Balance in year t)
Principal Component in Year T: Equals (Payment) – (Interest component)
Present Value
Annuity Present Value: Equals (Payment) * (1 – ((1 + (Discount Rate/Period)) ^ (-Number of Periods))) / (Discount Rate/Period)
Present Value: Equals (Cash Flow) / ((1 + (Discount Rate/Period)) ^ (Number of Periods))
Present Value of Each Cash Flow: Equals (Cash Flow) / ((1 + (Discount Rate/Period)) ^ (Period))
Future Value
• A cash flow of $747.25 is available now (in Period 0). For this cash flow, the appropriate discount rate/period is 6.0%. What is the period 5 future value? Use worksheet “Single-FV”.
• An annuity pays $80.00 each period for 5 periods. For these cash flows, the appropriate discount rate/period is 6.0%. What is the period 5 future value? Use worksheet “Annuity-FV”.
Present Value
• A cash flow of $1,000.00 will be received in period 5. For this cash flow, the appropriate discount rate/period is 6.0%. What is the present value of this single cash flow? Use worksheet “Single-PV”.
• An annuity pays $80.00 each period for 5 periods. For these cash flows, the appropriate discount rate/period is 6.0%. What is the present value of this annuity? Use worksheet “Annuity-PV”.
LOAN AMORTIZATION
• To purchase a house, you take out a 30-year mortgage. The present value (loan amount) of the mortgage is $325,000. The mortgage charges an interest rate/year of 4.00%. What is the annual payment required by this mortgage? How much of each year’s payment goes to paying interest and how much goes to reducing the principal balance? Use worksheet “Loan”.
