PS1 - PS1 PDF

Preview

Summary

PS1...

Description

ISYS1021 Excel Problem Set #1

Fall 2020

Excel Problem Set #1 [100 points total] Upload your workbook to Canvas before Friday 10/2 at 11:59pm. Late work will be penalized 5 points if not submitted by the due date, and 1 point for each additional day late. #1

Open a new Excel workbook

[5 points]

Save the workbook and name the file such that it identifies your last name and the assignment, e.g., LastName_FirstInitial_PS1. Enter your full name in cell A1 on the first worksheet. Add worksheets as needed, to use a separate worksheet for each problem. To be sure, you will turn in one workbook with multiple worksheets. Label each worksheet with the problem #. #2

Currency Conversion Worksheet

[15 points]

Create a well-labeled currency conversion worksheet. Start by entering the following conversion rates1 into two cells: dollars to euros ($1 = € 0.91) and dollars to Chinese yuan ($1 = ¥ 6.81). Additionally, your worksheet should have three columns: i. ii. iii.

A column to enter an amount in dollars and obtain the equivalent values in euros and yuan A column to enter an amount in euros and obtain the equivalent values in dollars and yuan A column to enter an amount in yuan and obtain the equivalent values in dollars and euros

Formulas in each of the three columns should reference the cells with conversion rates as well as one of the input cells where you enter an amount in dollars, euros, or yuan. Try your worksheet on $29.98, €507, and ¥5,640 2. Don’t hard code any numbers in your formulas – only use references to the other five cells. If the currency conversion rates are changed or the amounts in entry cells are changed, other values on the worksheet should update accordingly. Cells should display two decimal places and the appropriate currency symbols.

1

Obtained from https://finance.yahoo.com/currency-converter/ on 1/26/20 On the sample worksheet design for PS1 #2 shown on the Problem Set Q&A Canvas page, those values would be entered in cells C4, E4, and G4, respectively.

2

ISYS1021 Excel Problem Set #1 #3

American League Analysis

Fall 2020

[20 points]

The 2019 Boston Red Sox season was the 119th season in the team’s history, and their 108th season at Fenway Park. You might be a Red Sox fan or a Red Sox hater. You might be ambivalent about the sport entirely but (now that you live in Boston) want to try to understand why everyone is obsessed with the Red Sox. Regardless of your baseball preferences, you decide to take stock of the competition by comparing the number of season wins for each AL team as of January 25. Enter or copy/paste the data below in columns A and B of a worksheet: American League team Red Sox Yankees Astros Athletics Indians Mariners Rays Angels Twins Blue Jays Rangers Tigers White Sox Royals Orioles

# of season wins as of 1/25/20 84 103 107 97 93 68 96 72 101 67 78 47 72 59 54

Sort the data by the number of wins. In rows below the team data, calculate the League average number of wins, the smallest number of wins, and the largest number of wins. Label each calculation. Add a new third column in C labeled as “Difference from League Average”. In the first data row of your column C, write a formula to calculate the difference between the number of games won by the Red Sox and the League average. Use the Fill handle to drag down your formula down the rows in column C to calculate for each team the difference between their number of wins and the League average. Add a new fourth column in column D labeled as “Difference from Red Sox”. Starting in row two, write a formula to calculate the difference between the number of games won by the Red Sox and the number of games won by the team in row two. Use the Fill handle to drag down your formula in column D to calculate the difference for each team in the rows below. If the number of wins is edited for any team, the league statistics and data in columns C/D should change accordingly. Cells should display no decimal places.

ISYS1021 Excel Problem Set #1 #4

Hogwarts Work-Study Payroll

Fall 2020

[25 points]

From the Canvas assignment page for PS 1, download the “Hogwarts work-study payroll” workbook. This file contains information on seventy student employees, their hourly wages, and the number of hours worked on each day during one week. Move/Copy the “Hogwarts workstudy payroll” worksheet from the workbook that you just downloaded from Canvas to your Problem Set workbook (i.e., the workbook that you created for #1 - #3)3. Enter the appropriate tax withholding rates and medical deduction amount (details below) in the three designated input cells (L1:L3). Reference these input cells with formulas that you create.

3

i.

Add a column (K) with formula in row 6 to calculate total weekly hours for each employee.

ii.

Add a column (L) with formula in row 6 to calculate the gross wages earned for the week.

iii.

Add a column (M) with formula in row 6 to calculate Ministry tax withholdings (11% of gross wages).

iv.

Add a column (N) with formula in row 6 to calculate local Hogsmeade tax withholdings (4% of gross wages).

v.

Add a column (O) to enter a £7 Hospital Wing medical deduction (regardless of gross wages).

vi.

Add a column (P) with formula for take-home pay (gross wages minus tax & medical).

vii.

Each new column that you create should be clearly labeled. Use the Format Painter to give the column labels that you enter (K5:P5) the same formatting as the labels in cells A5:J5.

viii.

Use AutoFill to drag formulas and entries from row 6 down the worksheet for each employee row through row 75. Formulas should update automatically if values for hourly wage or hours worked are changed.

ix.

Add two clearly labeled sets of calculations at the bottom of the worksheet in rows 77 and 78. Use Excel FUNCTIONS to calculate the Total and Average values (for each of columns E – P) in cells E77:P78.

x.

Use border formatting to: (a) add a Left Border to cells F5:F78, (b) add a Right Border to cells J5:J78, (c) add an Outside Border to cells P5:P75, and (d) add a Bottom Border to cells A75:P75.

xi.

Make cells P77:P78 bold. Format other cells in columns E – P appropriately. Assume that the British pound (£) is the relevant currency. Use consistently some form of appropriate accounting/currency format to identify cells that contain £ amounts. Cells that do not contain £ amounts should display no decimal places.

Make sure that you move/copy the entire worksheet, not merely copying/pasting the worksheet contents into your workbook. See Gips p. 19

ISYS1021 Excel Problem Set #1 #5

BC Dining demand and revenue

Fall 2020

[25 points]

BC Dining has asked for your help in creating a worksheet to model customer demand and expected revenue for this semester. Boston College dining facilities are anticipated to have 12,000 visitors this week. Of these visitors, 97% are diners (i.e., they place orders for food). The average order this week is $13.74. Thus, during this week (Week 0), given the number of diners and average amount that they spend, BC Dining can expect to take orders for $159,934. Over the semester, BC Dining expects the number of visitors to decrease by 2% per week as students run out of meal plan money and seek off-campus dining options. The percentage of visitors who place dining orders is assumed to remain the same. The average order amount is expected to decrease by $0.36 per week. (a) Design a worksheet to predict dining revenue during Spring 2020 semester. You should create input cells for given variables: the % of visitors who place dining orders, the projected rate for decrease in visitors, and the projected decrease in average order amount. (b) Each week (0-16) should be represented by a row. (c) Create columns for: week #, number of visitors, number of diners, average amount ordered, and the total expected order amount for each week. (d) Use formulas in your top row with references to input cells, then Fill down the rows to complete calculations for each week. (e) Add two clearly labeled cells at the bottom of the worksheet. There, calculate the average number of visitors across all weeks and the total expected amount ordered for the semester. Changing any of the values in your three input cells should be reflected by changing calculations throughout your worksheet. Format your worksheet such that cells representing visitors/diners show no decimal places. Any dollar amounts should be formatted appropriately and consistently with two decimal places. Format the appropriate input cells as percentages. You are welcome – but not required – to use other formatting features (e.g., tasteful borders of shading or color or font) to enhance the look of your worksheet.

ISYS1021 Excel Problem Set #1 #6

Importance of graphical analysis

Fall 2020

[10 points]

Many businesses use numbers to communicate information about performance, and in particular, how performance varies with different variables: for instance, do sales increase as you work longer hours? While numbers are useful, a picture is worth a thousand words. Create a graphical display to conduct exploratory analysis on the relationship between sales and the number of hours worked. From the Canvas assignment page for PS 1, download the “Worker productivity” workbook. This file contains information about four employees’ number of hours worked and sales. Move/Copy the “worker productivity” worksheet from the workbook that you just downloaded from Canvas to your Problem Set workbook (i.e., the workbook that you created for #1-#5). i.

ii. iii.

iv. v. vi.

Fill in the “Performance Summary” table (i.e., B20:E24) with the appropriate functions. To calculate the correlation between hours and sales for an employee, use the CORREL function in excel, and select the range of all hours worked by the employee as argument 1, and the sales made by the employee as argument 2 (the order does not matter!). Display up to 2 decimal points for the statistics you calculated Insert a Scatter Plot for each employee that shows the relationship between hours worked (horizontal axis) and sales (vertical axis). To be abundantly clear, create a total of 4 Scatter Plots, one for each employee (1-4). Add an informative chart title for each of the scatter plots you created. Again for each plot you created, insert a Horizontal Axis title so it is clear we are considering the relationship between hours worked and sales made. Using one or two sentences, briefly describe in cell A27 how the four workers are similar/different. Can you tell the workers are different based on the performance summary in i)? What additional information do the graphs in iii) provide?

You are welcome (but not required) to further enhance the chart formatting and presentation....