PA: Basic Excel Modeling (in-class) Due: 1/21/2016 This practice assignment will require you to use learn to use basic formulas and functions in Excel. Successful completion of this assignment will help to ensure that you are prepared to be successful in the modeling section of the course, which will begin after the water tower project concludes.

PA: Basic Excel Modeling (in-class) Due: 1/21/2016 This practice assignment will require you to use learn to use basic formulas and functions in Excel. Successful completion of this assignment will help to ensure that you are prepared to be successful in the modeling section of the course, which will begin after the water tower project concludes. In order to receive credit for this practice assignment, you must submit the completed assignment to the course blackboard site (under “Practice Assignments”, click on the link for this assignment itself, upload your document, and hit the “submit” button) by the start of your on class on Thursday, 1/21/2016. Late assignments will not be accepted for course credit, but will be evaluated for learning purposes. This is an individual (not a group) assignment. To receive credit for this practice assignment, you’ll need to submit a spreadsheet with Steps 1 – 12 completed. To help you get up to speed with the Excel 2013 skills you need to complete this assignment, the following playlist of YouTube tutorials is suggested:

There are 28 total tutorials available on this YouTube playlist. For those of you completely new to Excel, it might be worth starting at the beginning with Video 2: Getting to Know Excel. For those of you with more experience, but who still aren’t comfortable with how to work with formulas and functions, you might want to start with Video 11: Simple Formulas and then go through Video 15: Functions. For this assignment, you will need to utilize a dataset posted to the course blackboard site along with this assignment (under ‘practice assignments’, then ‘PA: Basic Excel Modeling’). The dataset is titled “water_tower_data.xlsx” and supplies the data recorded from the water tower project demonstration day during three previous EGR 101 class sections. Specifically, in the “raw data” sheet, the file gives the following attributes for 28 teams: team number, water tower design type (speed vs. height), time to build tower (sec), tower height (in), number of cups, and number of rows (levels) in the tower. For this assignment, complete the following tasks and then upload your completed 1. Save the “water_tower_data_2013.xlsx” file to your local machine with the following name: PA_YourLastName (e.g., “Bekki.xlsx” or “Smith.xlsx”). 2. Copy and columns A and B and E and F from the “raw_data” sheet into the same locations in the “converted_data” sheet. At the end of this operation, columns A, B, E, and F in the “converted_data” sheet should be identical to their counterparts in the “raw_data” sheet. 3. In the “converted_data” sheet, in cell C1, type “time to complete (min)” 4. In the “converted_data” sheet, in cell D1, type “tower height (cm)” 5. Input a formula in cell C2 of the “converted_data” sheet that convert the “time to complete” value in cell C2 of the “raw_data” sheet (and is in seconds) to a corresponding value in minutes. Drag this formula down to cell C29 (on the “converted_data” sheet) so that the same calculation is done for all 28 teams. 6. Input a formula in cell D2 of the “converted_data” sheet that converts the “tower height” value from cell D2 of the “raw_data” sheet (which is in inches) to a corresponding value in centimeters. Drag this formula down to cell D29 (on the “converted_data” sheet) so that the same calculation is done for all 28 teams. 7. In cell G1 on the “converted_data” sheet, type “score”. 8. Add a formula in the “score” column that calculates the score for each team based on the following formula, where tower height is given in centimeters, and time to build is given in minutes. (NOTE: this is not the exact formula that will be used on our demonstration day): 9. Insert the text below in the cells shown below on the sheet titled “averages” 10. In the appropriate cells on the “averages” sheet, calculate the average, maximum, and minimum score for each of the respective design types using the appropriate functions (“AVERAGE”, “MAX”, “MIN”). So, cell B2 of the “averages” sheet, for example, should contain a formula that calculates the average score (based on the scores calculated on the “converted_data” sheet) of all teams who were classified as the “speed” design type, cell C3 should contain the maximum score of all teams who were classified as the “height” design type, etc. 11. Based on the calculations now contained within the “averages” sheet, which design type do you think is preferable (speed or height)? And why? Record your answer to these questions in Cell A6 of the “averages” sheet. Please write in complete sentences. 12. Submit your completed spreadsheet to the course blackboard site (under Practice Assignments, click on the link for the assignment itself, upload your documents, and hit the “submit” button) by no later than 1/21/2016.

 

Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"