Skip to main content
Continuing Online Operations
Loyola is continuing online operations while we prepare for reopening safely in the fall. Coronavirus resources

MUIN M110: Intro to Music Industry Studies

Spreadsheet Assignment

In this module, you'll be completing a spreadsheet assignment using Google Sheets. The entire tutorial is written out here, or you can work through each section below. There are also video demonstrations of each section of the assignment here.

If you have any questions as you work through the assignment, please do not hesitate to contact me. You can email me at ejkelly@loyno.edu, or make an appointment to meet with me via phone, video/web conference, or chat here.

Introduction

Hi, I'm Elizabeth Kelly and I am your librarian liaison for Music Industry. I am here to help you with any of your research needs or instructional technology needs for this class, or for the other courses that you're taking in Music Industry. Today what I'm going to be working with you on is an assignment where you work with data. Being able to work with data is such an important skill now in so many different industries. In the music business alone you can use it to to balance budgets, assess your social media reach, analyze word frequency in song lyrics, and so much more. What we’re going to do in this lesson is create a budget for a band tour. You’ll keep track of what you spent on tour and what you made back, and we’ll even create a chart so we can visualize some of this data. You’ll be using Google Sheets for this assignment, but everything we do here can also be done using Excel, Open Office Calc, and other readily available spreadsheet software.

To get an idea of what you can accomplish with a well-formatted spreadsheet, take a look at the Generic Road Report Billy has created. This workbook has different tabs for each day of the week. Formulas are already embedded in the spreadsheet so that as you add in income and expenses, you always know your net income, or how much money you actually have available to you. The spreadsheet also includes areas for different currencies, so even if you are touring internationally you can easily convert your values to USD. For our assignment, you will create a slightly smaller version of this spreadsheet to track an imaginary tour using the values in the Intro_MIS_spreadsheet_demo.pdf.

 

Setting Up

  1. In Google Drive, click “New” and Google Sheets
  2. Name your Google sheet something useful, like “2020 Tour.”
  3. Notice that there are letters going across the top of your sheet, and numbers going down the left. The letters are for column names, and the numbers are for your rows. These letters and numbers are important once we start using formulas. Each of the boxes on your sheet is a cell, and each cell has a name based on its column letter and row number. We’ll discuss this more later.

Input Data

  1. Input the Expenses, Starting Balance, Total cash received from promoter, and Cash received today from other sources (or, everything with a value on the Intro_MIS_spreadsheet_demo.pdf); include all headings, blank rows, etc. Your values should be exactly the same as those on the Intro_MIS_spreadsheet_demo.pdf, but don’t worry about formatting (bolds, underlines, background colors) just yet.
  2. To format as currency, highlight the values in your spreadsheet that should be money, and then click the dollar sign icon in the toolbar.

Expenses

  1. The first section of the spreadsheet involves money you have spent while you’re on tour.
  2. Highlight cells B3:B5. Then In cell B6, use the “sum” function (click the Functions button ∑ ) to add up your expenses for Monday. Click enter after you’ve put in the formula.
    • Cell B6 should now say “$335.00” If you click in the cell, you can still see your formula (=SUM(B3:B5)) in the top toolbar. Note that the formula adds the contents of cells B3 through B5; that is what the colon in the formula stands for. This is useful as it means that when you’re adding up lots of adjacent cells, you don’t have to name them all in the formula. The colon allows you to specify a range.
  3. Click cell B6 and drag the tiny blue box on its bottom over to cell C6. Now you should have a total in C6 for Tuesday’s expenses. Click in the cell and see the formula (=SUM(C3:C5)) in the top toolbar. Notice that the formula automatically changed, assuming that by moving over to another column, you wanted to add the numbers in that column.
  4. If you were to later add in expenses for the rest of the week, you could continue dragging that formula to the right to fill in for subsequent days rather than setting up a new formula each time.
  5. Notice that cell A6 reads “Total (do not edit).” Several of the other rows on your spreadsheet say “Do not edit” as well. All of these rows will have formulas in them. Once we’ve put the formulas in, we don’t want to accidentally edit what’s in these rows as it will mess up our data throughout the spreadsheet.

Net Income Report

  1. Skip down to the “Net Income Report” section of the spreadsheet. This is where you will reconcile money you’ve spent against money you’ve earned to know what your net income is. You should have already input your starting cash as well as cash received from the promoter and other sources.
  2. In C14, enter a formula that adds together all of your starting cash
    • Hint: use the “sum” function to add up cells C11, C12, and C13
  3. In cell C16, “Expenses reported today,” copy over the amount of money you spent on Monday (cell B6).
    • Type =B6 to automatically grab the data from B6 without having to retype it. This means that, should you realize you forgot to include an expense for Monday, you can update it in the Expenses section and it will automatically update in the Net Income Report section.
  4. In C18, calculate how much money you have at the end of Monday by subtracting your expenses from your cash received. You will need to manually type this formula in.
    • =SUM(C14-C16)
  5. Copy over your starting cash for Tuesday to cell D11
    • =C18
    • Just like before, this means that if we make edits to our Monday budget later, our starting cash for Tuesday will automatically reflect those changes.
  6. Highlight and drag the formulas in C14, C16, and C18 to the right to create your formulas for Tuesday. As with before, if you were to later add in expenses for the rest of the week, you could continue dragging the formulas to the right to fill in for subsequent days.

Chart

  1. Sometimes charts or data visualizations can convey information from your data in ways that are easier for readers to understand than a table with a bunch of numbers.
  2. Highlight your Expenses table (but do NOT highlight the “Total” row) and select “Insert—Chart.”
  3. Under “Chart Type,” look at the chart options. Which one makes the most sense for this data? Notice the “Switch rows/columns” checkbox at the bottom; does this help or hurt how your data is displayed? Think about what you want your chart to say; do you want to compare how expenses differed from Monday to Tuesday? Or do you want to show how gas and food expenses relate to each other? Or both?
  4. Click the three dot menu at the top of your chart, and select “Move to own sheet.” Notice your workbook now has two tabs, one for your original data and one just for the chart. This makes it easier for us to edit our chart while not disturbing or covering up our original data.
  5. Click “Edit chart” and the “customize” tab to customize the appearance of your chart. You can change fonts, colors, your chart legend, and more.

Formatting

Go back to your original tab. Spend a few minutes formatting your spreadsheet—make headings bold, give them background colors, etc. using the different options in the toolbar. Your spreadsheet does not need to look exactly like the demo spreadsheet, but try to make it look professional. Note that formatting is for appearance only and doesn’t (or shouldn’t) affect your data. Sometimes less is more.

Submit

  1. Click “Share” at the top right of your screen. Click “Get Shareable link” and “Copy link.”
  2. In Blackboard, go to the “Research & Technology” folder in this week’s Course Materials. Scroll down to where it says “Click here to submit your spreadsheet assignment,” and click. Under Text Submission—Write Submission, paste the link to your assignment (Ctrl+V on a PC; Cmmd+V on a Mac; or right-click and select “Paste” if your browser allows). Click Submit.