Assignment 1
Tackle as many of these problems as you can, submitting your answers along the way via this Google Form, which should autosave your answers. Be sure to click SUBMIT at the bottom of the Google Form when you have completed the assignment. You should receive an email receipt when the form has been properly submitted. You can resubmit, too, as many times as you’d like before the assignment’s deadline. Refer to these instructions for guidance on submitting.
Please be mindful of the course’s policy on academic honesty as you complete this assignment. When you engage with others regarding this assignment in peer learning sessions, be aware of our guidelines for assignment-related discussions. With the above in mind, the solution for the first item below denoted with an asterisk (*) may be openly discussed without restriction during peer learning sessions.
Not to worry if you run into trouble. Do just attend office hours for help! Further, consult our AI-based tool, cs50.ai.
Getting Started
Visit the spreadsheet at this link and make your own copy, as via File > Make a copy
or by via File > Download as > Microsoft Excel
.
Using the YouTunes data, solve the following business problems. The answers should be expressed as formulae in the spreadsheets; highlight the cells containing your answer(s) in yellow.
Then, in the submission form, on the space provided for each question, briefly explain how you solved the problem (or, if you got stuck, explain where you got stuck and what you wanted to do).
Question 1*
You’ve recently determined how much it costs you to fulfill an order. Now it’s time to calculate your average invoice amount. In the Invoices
sheet, add a new column between columns H
and I
and fill it in with the total value of each invoice. Then, compute the average value of all the invoices.
Question 2
You are considering establishing a customer loyalty program; identify your top 5 customers (in terms of total revenue from their invoices).
Question 3
Part 1
It’s time to allocate all your sales employees to customers. Let’s begin by assigning each member of the sales team to some customers. We’d like to distribute them equally among the team, so we’ll do this alphabetically. First, in cell A15
of the Employees
sheet, compute the number of employees whose Titles
place them in the sales organization. (No fair just counting – solve this as if you had thousands of employees.) Give this cell the name SalesReps
.
Part 2
Next, we need to figure out how to evenly partition our customers alphabetically. Sort the Customers
sheet alphabetically by company and by last name if there is no company.
Part 3
Create a cell named NCustomers
and set the value equal to the number of Customers
on the sheet.
Part 4
Add a column to the Customers
sheet, between columns I
and J
(between the PostalCode
and Phone
number). Label that column SalesRep
. Now, using the same formula in every cell in the column, assign representatives to customers so that Nancy gets the first quarter of the customers, Jane gets the next quarter, Margaret gets the next quarter and Steve gets the last quarter. Remember, each cell must contain the same formula!
Question 4
Add a column, E
, to the Songs
sheet that includes the price of the song. Hint: This is not simple to do in a spreadsheet so remember if you have spent over 20-30 minutes on the problem, stop and just write a short paragraph explaining what you tried and conceptually, what you need to do to answer the query.