computer science lab homework
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. Thank s! Course Evaluation Please take a few min utes 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 20 10 in the lab machines go to the Start Menu , select All Programs , then select Microsoft Office , and then Microsoft Excel 2010 : 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 sa ve 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 betwee n the Hardback and Paperback columns. e. Insert a ne w 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 w hich 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 impro ve the readabilit y. a. Apply Comma Style w ith 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 wa nt 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, Lighte r 40% fill color. c. Use Format Painter to apply the formats from the Units Sold label to the Pricing and Royalt y Information labels.
d . Select the indi vidual 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 wa nt 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 wo rksheet. 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 v iew. 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 You are a sales representative at the local fitness center, Buff and Tuff Gym. Your manager ex pects 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 create d, 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 determin ed based on each client's membership type, using the lookup table. a. Insert a function in cell CS to displa y 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 co lumn displays Yes for clients w ho rent a locke r 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 co ntract. d. Copy the three formulas down their respective columns. Determine the Down Payment and Balance You need to collect a do wn 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 displa y 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 pa ym ents are based on the number of years specified in the client's contract and a standard interest rate. a. Insert the function to calc ulat e the first client's monthl y payment, using a pp ropria te relative and absol ute cell references. b. Copy the formu la down the column. c. Edit the formula by changing the appropr iate cell reference to a mixed cell reference. Copy the formula down. Finalize the Workbook You need to perform some basic statist i cal calculations and finalize the workbook with formatting and page setup options. a. Calcu late 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 appropriatel y. c. Format the other column headings on rows 4 and 17 to match the fill color in the range El7:Hl7 . Wra p text for the column headings. d. Format the monetary values for Andrews and the total row wi th Accounting Number Format. Use zero decimal places for whole amounts, and display two decimal places for the monthl y payment. Apply Comma Style to the internal monetary values. Underline the values before the tota ls, and then apply Double Accounting Underline (fo und in the Format Cells dialog box) for the totals. e. Set 0.3" left and right ma rgin s, and then ensure the page prints on on ly one page. f. Insert a footer with yo ur nam e on the left side, the date code in the cente r, and the file name code on the right side. g. Save and close the work book, and submit based on your instructor 's directions. Capstone Exercise• Excel 2010 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 crea ted a clu ster ed column chart, but you selec ted too man y cells for th e data source. You need to open the workbook and adjust the data source for the chart. In ad ditio n, you want to position and si ze the char t. a. Open the e0 3cl movies workbo ok and save it as e03clmovies_LastnameFi r stname. b. Remove the Category Totals from the lege nd, and then a dju st the data range to exclude the wee kly total s. c. Position and size the chart to fill the range Al8:L37 . d. Change the row and col umn orientation so th at the weeks appear in th e catego ry axis a nd 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 cha rt titl e abo ve the chart . b. Enter th e te xt September 2012 Downloads by Genre . c. Add a rota ted value axis title. d. Enter the text Number of Downloads. e. Move the legend to the top of the chart, a nd then drag the bottom of the chart area down to cover row 40. Format Chart Elements You are ready to apply th e finis hing tou ches to the clustered column chart. You will a dju st the font s ize of the category axis and displ ay additional gridline s to make it eas ier to identify values for the data ser ies. You will add and adjust data labels to the Drama data series .
F ina lly, you will add a linear tre n dline to the chart to visua lize tr ends.
a . Forma t the catego ry ax is with 12- pt size.
b . Display major and minor horizontal gr idlin es. c. Select th e Drama data series, and then add data labe ls in the Outside End position. d. Add a Yellow fill color to the data la b els . e. Add a line ar trend line to the Drama data series. Insert and Format Sparklines You want to show wee kly trends for each genre by inserting sparklines in the column to the right of Cat egory Total s. a. Insert a Line sparkline for the weekly (but not category totals ) data for Actio n & Ad venture in cell GS. b. Copy the sparkline down the column. c. For mat the sparkl ines by applyi ng Sparkline Style Dark #6, displa y the high point, and forma t the high point marker in Red. Create Another Chart You want to create a cha rt that w ill show the monthly volume of downloads by genre. You decid e to create a bar chart with genre l abels a long the l eft side of the chart. a. Select the genres and wee kly tota l s. Create a clus tered bar chart. b. Move the chart to its own sheet, and then name the sheet Bar Chart. c. C hange the chart type to a stacked bar chart. d. Add a chart title abov e the char t, a nd then enter Sept 2012 Total Monthly Downloads by Genre. Format the Bar Chart You want to enhance the appearance of th e chart by app l ying a chart styl e and adju sting the axis values. a. Apply the Style 31 chart style to the bar c hart. b. Disp l ay the value axis in units of tho usands. c. Displa y the category axis names in reverse order using t h e Format A.x is dialog box. d. Apply th e Layout 3 layout style to the chart. Printing the Charts You want to print the bar chart on its own page, but you wa nt to print the clustered column char t with the original data. To ens ure the works heet data and chart pri nt on the same page, you n eed to adjust the page setup options. a. Create a footer on each works heet with your nam e, the sheet name code, and the file name code. b. Apply lan dscape orientati on for the origin al works heet. c. Set 0.2" left, righ t, to p, and bottom margin s for the original worksheet. d. Select the option that m akes the worksh eet print on on ly one page.
e . Print bo th worksheets. f. Save and close th e workbook, and submit based on your instructor's directions. Capsto ne Exe rcise• Excel 2010
