Buff and tuff gym excel

CS 161 – Laboratory Exercise 8

Spreadsheets Productivity Tools: Tables and Calculations and Charts

In this session you will practice how to create and format tables in Excel and perform calculations on the data

stored in the tables. You will also practice how to create charts to analyze data. When you are done with the

assignment please show your documents to the TAs and then upload the required files to Blackboard Learn

(see: What to Submit section at the end of this document).

Check your grades

Before you continue, please take a few minutes to check your grades. Please let us know if there are any issues

or grades missing. Thanks!

Course Evaluation

Please take a few minutes to complete the course evaluation form. The form is available at:

https://cci.drexel.goaefis.net/index.cfm

NOTE: The completion of the course evaluation form is part of you grade for this lab, so please make sure you

show the lab instructor that you completed the evaluation form. We will make a list of the students who

complete the evaluation so they can receive full credit for the lab.

Software needed

For this lab you will need to use:

 Microsoft Excel 2010. To find Excel 2010 in the lab machines go to the Start Menu, select All Programs, then select Microsoft Office, and then Microsoft Excel 2010:

https://cci.drexel.goaefis.net/index.cfm

Setting up and downloading the files needed for this assignment

Create a folder on the desktop:

1) Right-click anywhere on the desktop

2) From the pop-up menu, pick New and then Folder

3) Name your folder CS161_Lab8

For this assignment you will need the following files which you should download from Learn and save them in

the CS161_Lab8 folder:

 e01c1royal.xlsx

 e02c1gym.xlsx

 e03c1movies.xlsx

Creating and formatting a basic spreadsheet

See attached instructions for this exercise.

Using formulas and functions

See attached instructions for this exercise.

Analyzing data with Charts

See attached instructions for this exercise.

What to submit

When you are done with this lab and you have shown your work to the TA, upload the final versions of

e01c1royal.xlsx, e02c1gym.xlsx, and e03c1movies.xlsx

You manage a publishing company that publishes and sells books to bookstores in Austin. Your assistant prepared a standard six-month royalty statement for one author. You need to insert formulas, format the worksheets, and then prepare royalty statements for other authors.

Enter Data into the Worksheet You need to enter and format a title, enter the date indicating the end of the statement period, and then delete a blank column. You also need to insert a row for the standard discount rate row, a percentage that you discount the books from the retail price to sell to the bookstores.

a. Open the eO 1 cl royal workbook and save it as eO 1 cl royal_LastnameFirstname.

b. Type Royalty Statement in cell Al. Merge and center the . title over the four data columns. Select 16 pt size, and apply Purple font color.

c. Type 6/30/2012 in cell B3, and then left-align the date.

d. Delete the blank column between the Hardback and Paperback columns.

e. Insert a new row between Retail Price and Price to Bookstore. Enter Standard Discount Rate, 0.55, and 0.5. Format the two values as Percent Style.

Calculate Values You need to insert formulas to perform necessary calculations.

a. Enter the Percent Returned formula in the Hardback column. The percent returned indicates the percentage of books sold but returned to the publisher.

b. Enter the Price to Bookstore formula. This is the price at which you sell the books to the bookstore and is based on the retail price and the standard discount. For example, if a book has a $10 retail price and a 55% discount, you sell the book for $4.50.

c. Enter the Net Retail Sales formula. The net retail sales is the revenue from the net units sold at the retail price. Gross units sold minus the returned units equals net units sold.

d. Enter the Royalty to Author formula. Royalties are based on net retail sales and the applicable royalty rate.

e. Enter the Royalty per Book formula. This amount is the author's earnings on every book sold but not returned.

f. Copy the formulas to the Paperback column.

Format the Values You are ready to format the values to improve the readability.

a. Apply Comma Style with zero decimal places to the quantities in the Units Sold section.

b. Apply Percent Style with one decimal place to the Units Sold values, Percent Style with zero decimal places to the Pricing values, and Percent Style with two decimal places to the Royalty Information values.

c. Apply Accounting Number Format to all monetary values.

Format the Worksheet You want to improve the appearance of the rest of the worksheet.

a. Select the Hardback and Paperback labels. Apply bold, right-alignment, and Purple font color.

b. Select the Units Sold section heading. Apply bold and Purple, Accent 4, Lighter 40% fill color.

c. Use Format Painter to apply the formats from the Units Sold label to the Pricing and Royalty Information labels.

d. Select the individual labels within each section (e.g., Gross Units Sold) and indent the labels twice. Widen column A as needed.

e. Select the rangeA7:ClO (the Units Sold section), and then apply the Outside Borders border style. Apply the same border style to the Pricing and Royalty Information sections.

Manage the Workbook You want to duplicate the royalty statement worksheet to use as a model to prepare a royalty statement for another author. You will apply page setup options and insert a header on both worksheets.

a. Insert a new worksheet on the right side of the Jacobs worksheet. Rename the worksheet as Lopez.

b. Change the Jacobs sheet tab to Red. Change the Lopez sheet tab to Dark Blue.

c. Copy Jacobs' data to the Lopez worksheet.

d. Make these changes on the Lopez worksheet: Lopez (author), 5000 (hardback gross units), 15000 (paperback gross units), 400 (hardback returns), 175 (paperback returns), 19.95 (hardback retail price), and 7.95 (paper- back retail price).

e. Click the Jacobs sheet tab, and then press and hold down Ctrl as you click the Lopez sheet tab to select both work- sheets. Select the margin setting to center the data hori- zontally on the page. Insert a header with your name on the left side, the sheet name code in the center, and the file name code on the right side.

f. Change back to Normal view. Right-dick the Jacobs sheet name, and then select Ungroup Sheets.

Display Formulas and Print the Workbook You want to print the formatted Jacobs worksheet to display the cal- culated results. To provide evidence of the formulas, you want to display and print cell formulas in the Lopez worksheet.

a. Display the cell formulas for the Lopez worksheet.

b. Select options to print the gridlines and headings.

c. Adjust the column widths so that the formula printout will print on one page.

d. Submit either a hard copy of both worksheets or an electronic copy of the workbook to your professor as instructed. Close the workbook.

Capstone Exercise• Excel 2010

cs

Highlight

cs

Text Box

Creating and formatting a basic spreadsheet

You are a sales representative at the local fitness center, Buff and Tuff Gym. Your manager expects each representative to track weekly new membership data, so you created a spreadsheet to store data. Membership costs are based on membership type. Clients can rent a locker for an additional annual fee. You are required to collect a down payment based on membership type, determine the balance, and then calculate the monthly payment based on a standard interest rate. In addition , you need to calculate general statistics to summarize for your manager. Spot-check results to make sure you created formulas and functions correctly.

Perform Preliminary Work You need to open the starting workbook you created, acknowledge the existing circular reference error, and assign a range name to the membership lookup table. You will correct the circular reference error later.

a. Open the e02clgym workbook, click Help, read about circular references, close the Help window that appears, and save the workbook as e02clgym_LastnameFirstname.

b. Assign the name Membership to the range Al8:C20.

c. Insert a function to display the current date in cell B2.

Calculate Cost, Annual Total, and Total Due You are ready to calculate the basic annual membership cost and the total annual cost. The basic annual membership is determined based on each client's membership type, using the lookup table.

a. Insert a function in cell CS to display the basic annual membership cost for the first client.

b. Use a function to calculate the annual total amount, which is the sum of the basic cost and locker fees for those who rent a locker. The Locker column displays Yes for clients who rent a locker and No for those who don't.

c. Calculate the total amount due for the first client based on the annual total and the number of years in the contract.

d. Copy the three formulas down their respective columns.

Determine the Down Payment and Balance You need to collect a down payment based on the type of member- ship for each new client. Then you must determine how much each client owes.

a. Insert the function to display the amount of down payment for the first client.

b. Find and correct the circular reference for the balance. The balance is the difference between the total due and the down payment.

c. Copy the two formulas for the rest of the clients.

Calculate the Monthly Payment Clients pay the remainder by making monthly payments. Monthly payments are based on the number of years specified in the client's contract and a standard interest rate.

a. Insert the function to calculate the first client's monthly payment, using appropriate relative and absolute cell references.

b. Copy the formula down the column.

c. Edit the formula by changing the appropriate cell reference to a mixed cell reference. Copy the formula down.

Finalize the Workbook You need to perform some basic statistical calculations and finalize the workbook with formatting and page setup options.

a. Calculate totals on row 14.

b. Insert the appropriate functions in the Summary Statistics section of the worksheet: cells Hl8:H22. Format the payments with Accounting Number Format, and format the number of new members appropriately.

c. Format the other column headings on rows 4 and 17 to match the fill color in the range El7:Hl7. Wrap text for the column headings.

d. Format the monetary values for Andrews and the total row with Accounting Number Format. Use zero decimal places for whole amounts, and display two decimal places for the monthly payment. Apply Comma Style to the internal monetary values. Underline the values before the totals, and then apply Double Accounting Underline (found in the Format Cells dialog box) for the totals.

e. Set 0.3" left and right margins, and then ensure the page prints on only one page.

f. Insert a footer with your name on the left side, the date code in the center, and the file name code on the right side.

g. Save and close the workbook, and submit based on your instructor's directions.

Capstone Exercise• Excel 2010

cs

Highlight

cs

Text Box

Using formulas and functions

You are an assistant manager at Premiere Movie Source, an online company that enables customers to download movies for a fee. You are required to track movie download sales by genre. You gathered the data for September 2012 and organized it in an Excel workbook. You are ready to create charts to help represent the data so that you can make a presentation to your manager later this week.

Change Data Source, Position, and Size You already created a clustered column chart, but you selected too many cells for the data source. You need to open the workbook and adjust the data source for the chart. In addition, you want to position and size the chart.

a. Open the e03cl movies workbook and save it as e03clmovies_LastnameFirstname.

b. Remove the Category Totals from the legend, and then adjust the data range to exclude the weekly totals.

c. Position and size the chart to fill the range Al8:L37.

d. Change the row and column orientation so that the weeks appear in the category axis and the genres appear in the legend.

Add Chart Labels You want to add a chart title and a value axis title and change the legend's position.

a. Add a chart title above the chart.

b. Enter the text September 2012 Downloads by Genre.

c. Add a rotated value axis title.

d. Enter the text Number of Downloads.

e. Move the legend to the top of the chart, and then drag the bottom of the chart area down to cover row 40.

Format Chart Elements You are ready to apply the finishing touches to the clustered column chart. You will adjust the font size of the category axis and display additional gridlines to make it easier to identify values for the data series. You will add and adjust data labels to the Drama data series. Finally, yo u will add a linear trendline to the chart to visualize trends.

a. Format the category axis with 12-pt size.

b. Display major and minor horizontal gridlines.

c. Select the Drama data series, and then add data labels in the Outside End position.

d. Add a Yellow fill color to the data labels.

e. Add a linear trendline to the Drama data series.

Insert and Format Sparklines You want to show weekly trends for each genre by inserting sparklines in the column to the right of Category Totals.

a. Insert a Line sparkline for the weekly (but not category totals) data for Action & Adventure in cell GS.

b. Copy the sparkline down the column.

c. Format the sparklines by applying Sparkline Style Dark #6, display the high point, and format the high point marker in Red.

Create Another Chart You want to create a chart that will show the monthly volume of downloads by genre. You decide to create a bar chart with genre labels along the left side of the chart.

a. Select the genres and weekly totals. Create a clustered bar chart.

b. Move the chart to its own sheet, and then name the sheet Bar Chart.

c. Change the chart type to a stacked bar chart.

d. Add a chart title above the chart , and then enter Sept 2012 Total Monthly Downloads by Genre.

Format the Bar Chart You want to enhance the appearance of the chart by applying a chart style and adjusting the axis values.

a. Apply the Style 31 chart style to the bar chart.

b. Display the value axis in units of thousands.

c. Display the category axis names in reverse order using the Format A.xis dialog box.

d. Apply the Layout 3 layout style to the chart.

Printing the Charts You want to print the bar chart on its own page, but yo u want to print the clustered column chart with the original data. To ensure the worksheet data and chart print on the same page, you need to adjust the page setup options.

a. Create a footer on each worksheet with your name, the sheet name code, and the file name code.

b. Apply landscape orientation for the original worksheet.

c. Set 0.2" left, right, top, and bottom margins for the original worksheet.

d. Select the option that makes the worksheet print on only one page.

e. Print both worksheets.

f. Save and close the workbook, and submit based on your instructor's directions.

Capstone Exercise• Excel 2010

cs

Highlight

cs

Text Box

Analyzing Data with Charts