Unit 1 Project

Purpose: The purpose of this assignment is to showcase how much you have learned during this Excel unit of the class. You will get to choose with your team between 3 different prompts listed below.

Instructions

Form a team of 3 members and choose a group name. Record your team name and members of your team here:
(Please note that you will be with this team for all other projects and will use this name the whole semester. So choose wisely)

You will then choose one of the three prompts listed below.
Complete the prompt using all the following requirements (this is where your grade will come from)

Create a nice cover sheet tab with instructions on how to use your sheet, your group name, and names of the members in your group
Use relative and absolute references
Use named cells
Some cells should be automatically calculated using mathematical equations
Use at least 3 built-in functions such as min, max, vlookup, etc.
Use at least one IF statement
Use conditional formatting on at least one column or row of data
Use data validation
Use at least one type of graph to view data
Add a new function that we didn't talk about in class to your project (making 4 functions total). - Make it something useful so it adds value to the sheet
Create a sheet in your project that reports on how each group member contributed (if any group member was not easy to communicate with or didn't show up to help with the project let the head TA or the professor know, and they will take care of it)

After you have completed your project, create a video. You can use ScreenPal (formerly ScreenCast-O-Matic) for this https://screenpal.com/. ScreenPal records your screen and your voice as you demonstrate what your project does and how it works. After you record your video, there is a "share" link in ScreenPal that lets you upload the video to YouTube and get a public link for it. The main purpose of this video is to make grading these projects easier. When making your video: - Keep it short (ideally under 4 min) - Show how your group completed every element in the rubric - Have each group member share one thing they learned.

The Prompts

Prompt 1 - Stock Market Data Challenge

You and two friends have decided to install the Robinhood app on your phones and start making regular stock purchases each week for 12 weeks to see who can choose the best stocks and get the most return on investment. You agree that you will each start with a $100 investment in a stock of your choice (each friend must choose a different stock). You make this initial investment on a Monday morning. Each Monday thereafter for 12 weeks, you each invest an additional $20 - again you must choose one stock, and it can’t be one that you already chose (this kind of restriction is hard to code into a spreadsheet, so you’ll just ensure this requirement when you are entering the data from your friends). You will need to build a list of stocks that have been bought by the group, including the initial purchase price and the current price (i.e. the cost of one share of the stock). Each week thereafter, you will update the price of one share and compute the percent gain or loss of that stock. You will use this information to update the current value of the investment of each friend in the stocks they purchased. At the end of the 12 weeks, you have agreed that whoever did the best overall (i.e. has the greatest value of their total investments) wins 25% of the gains of each of the other friends. Your job is to build an Excel spreadsheet to manage this process. You can use one or more tabs on the spreadsheet but your resulting product should automatically compute the gains or losses of each of the purchased stocks each week (based on initial and current price per share), and should compute the running total and final total of all the investments of each person, and should determine who is the overall winner and how much money they win from each friend. Fill your spreadsheet with synthetic data for the full challenge to show how it works. Be sure to include the items in the grading rubric below.

Prompt 2 - Friend Group Weight Loss Challenge

You and two friends decide to enter a 12-week weight-loss challenge. You each pay $20 into a kitty at the beginning of the challenge on a Monday morning and you each submit your current weight to whomever is managing the spreadsheet. Each of the following 12 Mondays you weigh in and enter your weight in the spreadsheet. The spreadsheet automatically computes the percent body weight lost (or gained) since the previous week for each person. (To keep it simple, you are going to ignore BMI or other metrics, and just focus on percent of weight loss. Let’s assume each friend is generally healthy and doesn’t have any eating disorders and has a “few pounds to lose” to reach whatever they consider to be their target/desired weight.) The three of you have agreed that each week you will each add another $5 to the kitty - except for the person who did best on the challenge that week (highest percent loss - comparing the current weight to the previous week’s weight - not compared to the original weight.) The money in the kitty gets put into a simple savings account that returns 0.05% interest per month. Make sure this interest rate can’t be changed in your spreadsheet (use an absolute reference to a cell that contains the interest). At the end of the 12 weeks, whoever has the greatest percent loss overall (compared to their very first measurement) wins the kitty. Your job is to build an Excel spreadsheet to manage this process. You can use one or more tabs on the spreadsheet, but your resulting product should automatically compute and name the winner each week and should also compute and name the overall winner and the total amount of money in the kitty that the winner will receive. Once you have built your spreadsheet, fill it with synthetic data including names of the friends and weekly weigh-ins, so that it can demonstrate all of its capabilities. Be sure to include the items in the grading rubric below.

Prompt 3 - Landscaping Invoice Tracker

Create a professional spreadsheet that can be used to generate bills for landscaping clients. Your spreadsheet must include:

At least three types of foliage, soil, and rocks, along with their cost per square foot
A section where the client’s square footage purchase for each material is entered
A way to calculate total costs based on quantity and unit prices
An entry field for person-hours required to complete the landscaping
Any other details needed for a clear and useful client invoice

Turning in/Rubric

REMINDER - For this class, you will only turn in the links to your Excel files. You will get a 0 for this assignment if you turn in an Excel file or a link that is not shareable.

On the top right, click the share button --> share --> settings
Click "anyone" at the top, then underneath "More settings", change "can view" to "can edit". Then click apply.
Copy the link, then turn it into Learning Suite in the feedback box for that assignment.

Only one team member needs to turn in the following in the feedback box on the assignment in Learning Suite: - An Excel link with sharing turned on and editing on - The link to watch the video

Rubric:

Item	Points Possible
Create a nice cover sheet tab with instructions on how to use your sheet, your group name, and names of the members in your group	7
Use relative and absolute references	5
Use named cell references	5
Some cells should be automatically calculated using mathematical equations	7
Use at least three built in functions such as min, max, vlookup, etc.	7
Use at least one IF Statement	7
Use conditional formatting on at least one column or row data	6
Use data validation	6
Use at least one type of graph to view data	7
Add one new function to your Excel sheet (making 4 functions total)	6
Create a sheet in your project that reports on how each group member contributed	7
Prompt completed	10
Video uploaded	10
Participation points	10
Total	100

The following is not a part of the rubric, but specifies how you can lose points. For example: if you fail to share your link correctly.

Reasons for Points Lost	Amount
Link shared incorrectly	-10%
Turned in late (per week)	-10% (up to -50%)