CS2 Lab 1 2024, Excel
🎰
<<< Back to
main site
Due Date: 2024/9/19
Good luck on your first "real" lab of CS2!
Need help? Remember to check out Edstem and our Website for TA help
assistance.
Background 🎰
Be sure to read all background notes, as they are vital to do the assignment.
We will go over some Excel basics, including how to
- Link cells together across sheets of a workbook
- Set up calculations
- Format data to make it readable and aesthetically pleasing
You can complete this lab on your own or with other students. You are welcome to come to TA hours to
work through the lab or do it on your own time. To get credit, you
must come to TA hours to get checked off.
Important Note: Do not use Google Sheets. Please make a new file
using Microsoft Excel and edit the file only using Microsoft Excel.
Lab Description 🎰
An ambitious entrepreneur named Marco Bellini has begun launching a chain of luxury casinos. However,
unforeseen costs emerged due to unexpected setbacks. To manage and allocate resources effectively, he
enlists your expertise as a financial consultant from Royal Flush Financials, Inc. Your tool of choice
is Excel, enabling you to analyze budget requirements and expenditure projections for his casino
endeavor. 💸
If you have any questions/need assistance when hours aren’t happening, please feel free to reach
out on Ed! It has all the questions asked by your fellow peers about assignments, and chances are,
they will help you too. :)
In addition, you definitely can Google/search online for how to complete these tasks. In fact, it’s
encouraged that you learn how to search online for examples/documentation. It will help you during
the later coding assignments.
Tasks 🎰
Task 1 Double click your CS2 folder on your
desktop, and enter your folder for Excel. Then, create a new folder inside
Excel called Excel Lab. All your files for this lab
should live in this folder.
To download Microsoft Excel on your personal computer, click here. Note that you will need to be on the Brown
network (either Brown wifi or VPN) to install Excel.
Task 2 Follow the instructions above to download Excel. Next, open
Excel and click "Blank Workbook". This will be the Excel sheet we will be using for this lab. Name
this file Excel Lab and be sure to save it in your Excel Lab
folder.
How to save to a folder and name
Once you've created your first sheet, click "File" at the top of your screen, then "Save As".
This will open up a menu where you will see a text entry for "name" and "place. Be sure to name
it Excel_Lab, and navigate through your file system to find where to
save the folder to. A reminder that it should be the Excel Lab folder
within the CS2 folder on your desktop.
Important Note: Before you are ready to close your file, always be
sure to save it with command/control + s, or by clicking
file -> save at the top of your screen.
Task 3 Rename your first sheet "Financials" by right-clicking on the
Sheet1 tab in the bottom left corner and selecting
Rename.
Marco's casino renovations are scheduled to take place over the course of a month.
Task 4 Add each of the following 4 weeks to a row cell in the
spreadsheet: September 10-16; September 17-23;
September 24-30; and October 1-7 (for a total of
four labeled rows).
Hint!
Your sheet should look like this:
| Dates |
x |
x |
| September 10-16 |
x |
x |
| September 17-23 |
x |
x |
| … |
x |
x |
Royal Flush Financials, Inc. has provided you with the following data:
Executive Assistant's Salary Expenses,
Casino Floor Updates Expenses,
New Gaming Systems Expenses,
Beverage and Food Expenses,
Miscellaneous Expenses, and
Weekly Revenue.
Task 5 Add each of the categories above to a column cell in the
spreadsheet. So there should be six labeled columns and four rows in total at this point.
Double check your work
| Financials |
Executive Assistant's Salary Expenses |
Casino Floor Updates Expenses |
New Gaming Systems Expenses |
Beverage and Food Expenses |
Miscellaneous Expenses |
Weekly Revenue |
| September 10-16 |
|
|
|
|
|
|
| September 17-23 |
|
|
|
|
|
|
| September 24-30 |
|
|
|
|
|
|
| October 1-7 |
|
|
|
|
|
|
Marco is interested in knowing
- His
Total Expenses each week
- Look in the write-up before about how we can
SUM across a
range!
- What % of his Revenue is spent on Expenses (
Expenses / Revenue)
- His Weekly % Change in Expenses. This can be calculated by finding:
(this week’s total expenses - last week’s total expenses) / last week’s total expenses
Add these categories to a column cell in the spreadsheet. So there should be nine columns
total.
Task 6 Fill in the chart with the following information that Royal
Flush Financials, Inc. has given you. You can highlight this table and copy and paste straight into
Excel.
| Financials |
Executive Assistant's Salary Expenses |
Casino Floor Updates Expenses |
New Gaming Systems Expenses |
Beverage and Food Expenses |
Miscellaneous Expenses |
Weekly Revenue |
Total Expenses |
Expenses as a % of Revenue |
Weekly % change in Total Expenses |
| September 10-16 |
$1670 |
$25,620 |
$900 |
$22,200 |
$9,580 |
$82,970 |
|
|
|
| September 17-23 |
$1680 |
$25,620 |
$870 |
$17,800 |
$4,820 |
$300,790 |
|
|
|
| September 24-30 |
$1740 |
$25,620 |
$790 |
$19,760 |
$9,087 |
$146,997 |
|
|
|
| October 1-7 |
$1100 |
$25,620 |
$420 |
$15,500 |
$8,999 |
$516,300 |
|
|
|
While a cell may have just a number or some text, it may also contain a formula. The formula bar shows
the calculations in a cell and is the white bar (highlighted in yellow in the picture below) located
next to the fx symbol just above the row titles of the spreadsheet.
We say formulas are dynamic because their calculations can
reference specific cell values instead of relying on a fixed numerical value. For example,
=2+2 is not a dynamic equation, but =CellA+CellB is.
Formulas are useful because if the value in CellA needs to be changed for some reason, when CellA is
modified, the value in the cell with the formula will also be modified
too. Excel will do your work for you! Typing an equals sign
= in the formula bar tells Excel that you’re about to type in a formula. If you
omit =, Excel will think you’re just typing text.
More About Cells
Cells are referred to by their column (a letter) and their row (a
number); E12, F10, and C3 are all valid names of cells. To make dynamic equations that refer to
cells, you can either type their names directly into the formula bar (eg. “=A10-B3”) or you can
click on the cells themselves as you type in the formula bar. This works the same way if you want to
refer to a cell on another sheet (use the tabs at the bottom of the Excel file to go to the other
sheet and click on the cell).
To copy information quickly over a bunch of cells, select the cell whose information (or formula!) you
want to copy. Click and hold on the small square that appears in the lower right-hand corner of the
cell, then drag towards the direction in which you want the information to be copied. If you use the
same technique on two cells containing chronological data (such as 1 and 2, Monday and Tuesday, or June
and July), Excel will continue the chronology.
If you copy a cell that contains a formula, the formula will also be copied but slightly modified; this
again speaks to the fact that formulas in Excel are dynamic!
Let’s say you have 3 rows of data in columns A and B, and you want the fourth row of each column to be
the sum of the 3 respective rows above. You can manually type =SUM(A1:A3) in
A4 and =SUM(B1:B3) in B4 to derive these sums, OR you could type this formula
only in A4 then drag-copy the cell over to B4; Excel assumes the same formula function for each
subsequent cell you drag-copy to while modifying the formula content on a case-by-case basis.
Important Note: When writing formulas in Excel make sure that you
consider the order of operations. Use parentheses to ensure that Excel is executing the formula you
want it to execute. For example, = 2 + 4 / 2 would give you 4, which is
much different than = (2 + 4)/2 which would give you 3 - just something to
keep in mind.
Example
Here's an example:
| My Revenue |
My Expenses |
My Profit |
| $100.00 |
$48.00 |
=(b1 - b2) |
Where the revenue value is in the cell b1, and the expenses value is in
b2. The profit value box would automatically fill in the answer as
$52.00.
Task 7 Use formulas to
fill in the remaining (the blank) cells in your table (the one right above). Each cell should be
using a formula that computes new values based on already-existing ones. By the end, all the cells
of the above table should be full.
Task 8 Create a new sheet called "Summary" to report a summary of
your findings (To create a new sheet, click the tab with the plus “+” symbol at the bottom of
excel).
You must use formulas to fill in these values. When
writing formulas in the "Summary" sheet, you can grab values (link cells) from the "Financials"
sheet by starting to write the formula, and then clicking on the tab on the bottom and selecting the
appropriate cell/cells from the financial sheet. Then click enter to "lock in" those selections and
update the function.
The Summary sheet should include:
- The sum of Marco's expenses from all four weeks
- Think, how can we add up a range of values? (check
above!)
- The average percentage of revenue that is spent on expenses
- Investigate an Excel "average" function on google!
- The maximum among all total expenses over the four week period
- Investigate an Excel "maximum" function on google!
- Each subcategory (i.e. Executive Assistant's Salary, Casino Floor Updates Expenses,
New Gaming Systems Expenses, Beverage and Food Expenses, Miscellaneous Expenses) as a % of
total expenses over the four week period
- For the denominator (the sum of total expenses), we can just refer to the value in the
cell from the first part of task 9
- To make the division a percent, you can
click the percent symbol along the top editing bar of Excel
Hint
Your sheet should look like this:
| Stat |
Answer |
| Sum of Marco's expenses from all four weeks |
|
| Average percentage of revenue that is spent on expenses
|
|
| Maximum among all total expenses over the four week period
|
|
| % Executive Assistant's Salary / Total
Expenses |
|
| % Casino Floor Updates Expenses / Total
Expenses |
|
| % New Gaming Systems Expenses / Total
Expenses |
|
| % Beverage and Food Expenses / Total Expenses
|
|
| % Miscellaneous Expenses / Total Expenses
|
|
Every cell should reference data on the Financials sheet and use a built-in Excel formula or a
combination of built-in Excel formulas.
To change the appearance of cells, highlight the cells you’d like to format by clicking on the first
cell you want and dragging your mouse until you have covered the entirety of your data. Then,
right-click on the cells and choose Format Cells.
A pop-up window like below will appear. You can change a variety of things (text font, cell background
color, type of information populating the cell, etc.), but arguably the most useful tool is the one that
lets you change the borders of your selected cells (under the Borders tab). It’s a great way to make
your data easier to read (and grade: hint, hint).
Group formatting
To format a group of cells in the same way, highlight the cells you want to include and go to
Format As Table in the Home tab. A drop-down menu like below will appear. In the Design tab, you can
further modify your table should you want to do so.
Task 9 Update the Financials sheet to show your favorite design/color
from the drop-down.
Task 10
Create two different kinds of charts (bar, line, pie,
surface, etc.) from your Financials and/or Summary sheet. Each chart should contain data that would
be logically represented by these kinds of charts. Please choose a chart type that makes logical
sense for the data! Add a title to each chart and play around with some of the other options (e.g.
label the axes). Look below to see which charts would be appropriate for your data.
Most of the work here is in laying out the information correctly. To create a chart, select the
information you want represented by highlighting all the relevant cells (including category labels!) and
choose a chart under the Insert tab.
For a two-dimensional chart, make sure you select two sets of data that you want to compare; for a
three-dimensional chart, three sets of data. The default is to make the first column (or row) of data
the x axis of the graph and the second column (or row) of data the y-axis so position your data
accordingly. There are many other chart functions available to you in Excel that won’t be covered here;
feel free to experiment with them!
Types of charts
- Pie charts are
for depicting proportions of data against a whole; for example, percentages of different
categories, what percent of a class is freshmen.
- Line charts
are for chronological data, like highest temperature predictions for the month of July. The
lines between data points means an approximated trend between what we know the values actually
are.
- Scatterplots
are for looking at correlation between two numerical, continuous variables, like money made at
an amusement park versus average number of new visitors per hour. That would be a positive
correlation - when one increases, the other does too.
- Bar graphs are
suitable for data that is labeled qualitatively. For example, showing the differences in visitor
counts to different zoos in a state.
- Vertical bar graphs are good for
showing positive and negative values, especially.
Hand-In 🎰
To Hand-In Lab 1:
Come to hours and show your TA three things for checkoff:
- Formatted Financials sheet with correct values with equations.
- Summary of Marco’s expenses on separate sheet using dynamic equations.
- Two unique, logically-chosen graphs from the financials and/or summary sheets.
Congrats! You just finished the Excel lab! 
CS2 Lab 1 2024, Excel 🎰
<<< Back to main site
Background 🎰
We will go over some Excel basics, including how to
You can complete this lab on your own or with other students. You are welcome to come to TA hours to work through the lab or do it on your own time. To get credit, you must come to TA hours to get checked off.
Important Note: Do not use Google Sheets. Please make a new file using Microsoft Excel and edit the file only using Microsoft Excel.
Lab Description 🎰
An ambitious entrepreneur named Marco Bellini has begun launching a chain of luxury casinos. However, unforeseen costs emerged due to unexpected setbacks. To manage and allocate resources effectively, he enlists your expertise as a financial consultant from Royal Flush Financials, Inc. Your tool of choice is Excel, enabling you to analyze budget requirements and expenditure projections for his casino endeavor. 💸
If you have any questions/need assistance when hours aren’t happening, please feel free to reach out on Ed! It has all the questions asked by your fellow peers about assignments, and chances are, they will help you too. :)
In addition, you definitely can Google/search online for how to complete these tasks. In fact, it’s encouraged that you learn how to search online for examples/documentation. It will help you during the later coding assignments.
Tasks 🎰
Task 1 Double click your
CS2folder on your desktop, and enter your folder forExcel. Then, create a new folder insideExcelcalledExcel Lab. All your files for this lab should live in this folder.To download Microsoft Excel on your personal computer, click here. Note that you will need to be on the Brown network (either Brown wifi or VPN) to install Excel.
Task 2 Follow the instructions above to download Excel. Next, open Excel and click "Blank Workbook". This will be the Excel sheet we will be using for this lab. Name this file
Excel Laband be sure to save it in your Excel Lab folder.How to save to a folder and name
Once you've created your first sheet, click "File" at the top of your screen, then "Save As". This will open up a menu where you will see a text entry for "name" and "place. Be sure to name it
Excel_Lab, and navigate through your file system to find where to save the folder to. A reminder that it should be theExcel Labfolder within theCS2folder on your desktop.Important Note: Before you are ready to close your file, always be sure to save it with
command/control + s, or by clickingfile -> saveat the top of your screen.Task 3 Rename your first sheet "Financials" by right-clicking on the Sheet1 tab in the bottom left corner and selecting Rename.
Marco's casino renovations are scheduled to take place over the course of a month.
Task 4 Add each of the following 4 weeks to a row cell in the spreadsheet:
September 10-16;September 17-23;September 24-30; andOctober 1-7(for a total of four labeled rows).Hint!
Your sheet should look like this:
Royal Flush Financials, Inc. has provided you with the following data:
Executive Assistant's Salary Expenses,Casino Floor Updates Expenses,New Gaming Systems Expenses,Beverage and Food Expenses,Miscellaneous Expenses, andWeekly Revenue.Task 5 Add each of the categories above to a column cell in the spreadsheet. So there should be six labeled columns and four rows in total at this point.
Double check your work
Marco is interested in knowing
Total Expenseseach weekSUMacross a range!Expenses / Revenue)(this week’s total expenses - last week’s total expenses) / last week’s total expensesAdd these categories to a column cell in the spreadsheet. So there should be nine columns total.Task 6 Fill in the chart with the following information that Royal Flush Financials, Inc. has given you. You can highlight this table and copy and paste straight into Excel.
Writing Formulas in Excel
While a cell may have just a number or some text, it may also contain a formula. The formula bar shows the calculations in a cell and is the white bar (highlighted in yellow in the picture below) located next to the fx symbol just above the row titles of the spreadsheet.
We say formulas are dynamic because their calculations can reference specific cell values instead of relying on a fixed numerical value. For example,
=2+2is not a dynamic equation, but=CellA+CellBis. Formulas are useful because if the value in CellA needs to be changed for some reason, when CellA is modified, the value in the cell with the formula will also be modified too. Excel will do your work for you! Typing an equals sign=in the formula bar tells Excel that you’re about to type in a formula. If you omit=, Excel will think you’re just typing text.More About Cells
Cells are referred to by their column (a letter) and their row (a number); E12, F10, and C3 are all valid names of cells. To make dynamic equations that refer to cells, you can either type their names directly into the formula bar (eg. “=A10-B3”) or you can click on the cells themselves as you type in the formula bar. This works the same way if you want to refer to a cell on another sheet (use the tabs at the bottom of the Excel file to go to the other sheet and click on the cell).
To copy information quickly over a bunch of cells, select the cell whose information (or formula!) you want to copy. Click and hold on the small square that appears in the lower right-hand corner of the cell, then drag towards the direction in which you want the information to be copied. If you use the same technique on two cells containing chronological data (such as 1 and 2, Monday and Tuesday, or June and July), Excel will continue the chronology.
If you copy a cell that contains a formula, the formula will also be copied but slightly modified; this again speaks to the fact that formulas in Excel are dynamic!
Let’s say you have 3 rows of data in columns A and B, and you want the fourth row of each column to be the sum of the 3 respective rows above. You can manually type
=SUM(A1:A3)in A4 and=SUM(B1:B3)in B4 to derive these sums, OR you could type this formula only in A4 then drag-copy the cell over to B4; Excel assumes the same formula function for each subsequent cell you drag-copy to while modifying the formula content on a case-by-case basis.Important Note: When writing formulas in Excel make sure that you consider the order of operations. Use parentheses to ensure that Excel is executing the formula you want it to execute. For example,
= 2 + 4 / 2would give you 4, which is much different than= (2 + 4)/2which would give you 3 - just something to keep in mind.Example
Here's an example:
=(b1 - b2)Where the revenue value is in the cell
b1, and the expenses value is inb2. The profit value box would automatically fill in the answer as$52.00.Task 7 Use formulas to fill in the remaining (the blank) cells in your table (the one right above). Each cell should be using a formula that computes new values based on already-existing ones. By the end, all the cells of the above table should be full.
Task 8 Create a new sheet called "Summary" to report a summary of your findings (To create a new sheet, click the tab with the plus “+” symbol at the bottom of excel).
You must use formulas to fill in these values. When writing formulas in the "Summary" sheet, you can grab values (link cells) from the "Financials" sheet by starting to write the formula, and then clicking on the tab on the bottom and selecting the appropriate cell/cells from the financial sheet. Then click enter to "lock in" those selections and update the function.
The Summary sheet should include:
Hint
Your sheet should look like this:
Every cell should reference data on the Financials sheet and use a built-in Excel formula or a combination of built-in Excel formulas.
Formatting in Excel
To change the appearance of cells, highlight the cells you’d like to format by clicking on the first cell you want and dragging your mouse until you have covered the entirety of your data. Then, right-click on the cells and choose Format Cells.
A pop-up window like below will appear. You can change a variety of things (text font, cell background color, type of information populating the cell, etc.), but arguably the most useful tool is the one that lets you change the borders of your selected cells (under the Borders tab). It’s a great way to make your data easier to read (and grade: hint, hint).
Group formatting
To format a group of cells in the same way, highlight the cells you want to include and go to Format As Table in the Home tab. A drop-down menu like below will appear. In the Design tab, you can further modify your table should you want to do so.
Task 9 Update the Financials sheet to show your favorite design/color from the drop-down.
Task 10
Create two different kinds of charts (bar, line, pie, surface, etc.) from your Financials and/or Summary sheet. Each chart should contain data that would be logically represented by these kinds of charts. Please choose a chart type that makes logical sense for the data! Add a title to each chart and play around with some of the other options (e.g. label the axes). Look below to see which charts would be appropriate for your data.
Most of the work here is in laying out the information correctly. To create a chart, select the information you want represented by highlighting all the relevant cells (including category labels!) and choose a chart under the Insert tab.
For a two-dimensional chart, make sure you select two sets of data that you want to compare; for a three-dimensional chart, three sets of data. The default is to make the first column (or row) of data the x axis of the graph and the second column (or row) of data the y-axis so position your data accordingly. There are many other chart functions available to you in Excel that won’t be covered here; feel free to experiment with them!
Types of charts
Hand-In 🎰
To Hand-In Lab 1:
Come to hours and show your TA three things for checkoff:
Congrats! You just finished the Excel lab!
If you have any issues with completing this assignment, please reach out the course HTAs: cs0020headtas@lists.brown.edu
If you need to request an extension, contact Professor Stanford directly: don.stanford@gmail.com