Document Sample

COMP 4, Summer 2005 – Session II Lab-05B: Using Excel Spreadsheets ……………………………………………………………………………………………………………………… Total Points Possible: 40 Due Date: Friday, 08 July 2005 at 1:15 PM (start of class) ……………………………………………………………………………………………………………………… After attending class(es) and completing this assignment, you should be a little more familiar with spreadsheet basics. ATTACHED Sample spread sheet for Section II BOOKS NEEDED Shelly Cashman Office 2003 workbook FILES NEEDED (only for additional practice) The student data files used for the extra “Apply Your Knowledge” and “In the Lab” projects that come at the end of each Workbook chapter. These are for optional additional practice. You may download and install the self-extracting archive from our Web page. ATTENTION be sure Caps lock (on the keyboard) is turned OFF ……………………………………………………………………………………………………………………… SECTION I: Spreadsheet Parts and Excel Basics (For PRACTICE) ……………………………………………………………………………………………………………………… A. Introduction to Spreadsheets It’s always a good idea to read through an entire assignment before you tackle it, especially for this and subsequent assignments. Often, many of the questions you have (such as “what should the number of locations be? Mine is different from the sample”) will be answered within the context of the assignment. B. Workbook: Microsoft Office 2003 Please pencil the following notes, optionals, and skips in your Office Workbook: PAGES STEPS WRITE THIS: EX 50 – 53 Correcting Errors NOTE: This is very useful, basic information on editing cell contents. Play around with all of the methods shown, and select what you like. EX 91 – 102 Read All Work any that are of interest. EX 103 – 105 Conditional Formatting Skip EX 111 – 112 Spell Checking OPTIONAL (but very useful) 1. Work through the rest of Project 1: first do pages EX 38 through EX 41: Creating a Chart. Now continue with the rest of Project 1, starting on EX 46 and working through EX 55, noting the changes in the table shown above. 2. Work through Project 2 and do everything that is not WEB based, starting on page EX 65 (Formulas, Functions, Formatting, and Web Queries), STOP on page EX 120. Again, watch the table above. 2 ……………………………………………………………………………………………………………………… SECTION II: Working with Spreadsheets (FOR POINTS) ……………………………………………………………………………………………………………………… Some advice: Read completely through this section before sitting at the computer. Get a sheet of paper and write down all the figures, ideas, and whatever else you might need to produce the spreadsheet. This will save you time while at the computer, especially, if you’re in one of the campus labs. Budgeting your startup expenses Your new business venture has taken off! You are projecting to soon be making enormous amounts of money, but initially have to spend a lot. It is time to create an expense report form to keep track of your startup costs. We have attached a sample (also on the course web page) that you can refer to as you work through the requirements. Please do not use our data. Create your own for your unique business. Minimum requirements 1. Create a new spreadsheet file. Save the file as: Lab05B-Expenses-LastFirst 2. Create a title for the spreadsheet in a cell within the first row (title will overflow to other adjacent cells; that’s fine). Use the name of your business and indicate that it is a startup-cost expense form (see example). Make the title big (but not larger than 24 points), bold, and italic, and in Arial font (or something similar if you don't have it). 3. Leave a couple of blank rows between the title and where you’ll start to enter everything else. Starting in cell A4, enter the following headings (labels) for your data, putting each heading in its own column: Quantity, Description, Cost Each, and Total Cost. Resize the columns, as necessary, to fit the column headings (not the spreadsheet title: let the title overflow). Make the headings bold and change their alignment to center. 4. Creatively make up at least eight (8) items that you have had to purchase for your new business, which you will type in the appropriate cells. Examples might be one espresso machine at $2,699.00 each, two television sets at $289.00 each, etc. Think of items that would make sense for your unique business. 5. Now enter your made-up data under each appropriate heading (i.e. quantity in column A, description in column B, and cost each in column C). Next, you must enter a formula to calculate a Total Cost for each item. Starting with the first item in your budget, enter a formula that multiplies the Quantity by the Cost Each. (If you are having trouble, you might check out page E 2.09 to refresh your memory about formulas.) You must use a formula, do not calculate the total on your own. Type the formula only once and then fill the formula down through the rows using the fill-handle. Next, change the width of the columns as you see fit to make it look nice. (Hint: Use auto width for formatting from Lab5A). 6. For the two cost columns, format the numbers in those cells as Currency format (use US dollars with 2 digits after the decimal point). You can use the increase decimal button on the toolbar to help you enter decimal places. 7. At the bottom of the Total Cost column, enter a formula using the SUM function to add together all of the cells in the Total Cost column, to give us a grand total. Again, you MUST use a formula with the SUM function here. Type the word TOTAL under the last item in the quantity column (see our sample). Make the word TOTAL and the actual total cost bold to make them stand out (again, refer to our sample). 8. Add a border around the cells that contain the column headings and the data. Use both Outline and Inside borders (called “all borders”) so each cell is bordered (like the sample). Use a line style of your choice for the border. (Remember to SAVE often!) Now Print out what you have so far. 9. Whoops! Both your coffee grinders broke and you had to purchase two new ones. Add it to your list: two (2) coffee grinders at the price of $399.95 each. Insert a new row somewhere in the middle of your existing list of items and enter the data appropriately. Be sure to copy and paste or fill down the Total Cost formula in the appropriate cell in the newly inserted row. Note: Look at the Total formula where you summed the Total Cost column. Notice that the range of cells now INCLUDES your newly inserted cost. Neat, huh? Remember this! It's an important concept about spreadsheets that you will be expected to understand and recall. 3 10. Finally, go a few rows below all the previous entries, and type your name in column A. Save. Print a copy of this spreadsheet and save that copy to your Turn In disk. 11. Now, you want to experiment with different startup-costs by changing the quantity and items with which your business will be equipped. Use the Save As feature and save the file with the name: Lab05B-Alternate-LastFirst In addition, change the title to reflect that this is an alternate expense estimate. Change at least half of the data values (numbers) and observe what happens. Notice, how the formula automatically updates the totals when you change the data. Change at least two of the item names. Save your file again. Print a copy of this alternate expense report. 12. Good news! Your sister just graduated from Stanford University and wants to start up a satellite office of your business out West. In fact, you have a big family, so you want to study how your costs would increase if you opened more offices. Open the file Lab05B-Expenses-LastFirst and Save it As: Lab05B-MoreOffices-LastFirst. 13. First, determine which of your expenses would increase if you open satellite officesselect five such expenses (i.e., you may need espresso makers for every location you open, but one only computer system for the home office). It does not really have to make business-sense. Second, find a blank cell a couple rows beneath the rest of the spreadsheet data, and enter the words: “Number of locations:” (merge two cells so that this text will fit). Enter the number 1 in the next cell. (Refer to our example.) 14. Now the harder part. Edit one of the five formulas in the Total Cost column (one of the five that would change due to expansion) to multiply the resulting Total Cost by the number of locations. You should use an absolute address for the cell where the number of locations is stored, and then copy that formula into the other four cells that would be affected by adding new offices. Make the five cells with the new formulas bold, and add a note at the bottom of your spreadsheet noting that the Total Costs in bold have been multiplied by the number of locations, as documentation of what you did to plan for expansion. Recall that a quick way to make a lot of cells bold is to make one cell’s contents bold, then with this bold cell selected, click the Format Painter button on the toolbar (little paintbrush). The next set of cells you drag over will then pick up all the formatting the first cell had, including number formatting. 15. Now, here’s the “what-if” part: enter different values for the number of locations cell (example: try 2, try 3, try 5, etc.one at a time, of course), and as you enter each value, observe how total cost changes for some items, while others stay the same, based on the number of offices you may have. Another important concept to remember! 16. Finally, after you've done some experimenting in #15 above, enter 4 as your final number of locations (row 16); Save the file, then print this spreadsheet. 17. Add 3-D Column Chart (charting the total costs) to your spreadsheet, and then print the spreadsheet again. 18. Now you are going to do another printout that displays the formulas. Press the ` key that is left of the number 1 key while holding down the <CTRL> key (see also page E2.56 – E.2.57 in the workbook). This will cause the spreadsheet to toggle the view to display the formulas (instead of the values) in each cell. (It might also mess up you column spacing, but you may resize your columns). Next, go under File/Page Setup. Click on Sheet tab. Make sure you select Print Row and Column Headings. (You can also choose to print all the gridlines from this menu, if you’d like. Or you may prefer to use the inside/outside borders button on only those areas where you want gridlines visible.) 19. Before you print, please adjust the column widths. First, select columns A through D. You can select a whole column by clicking on the letter at the top of that column. To select multiple columns, in this case click on A and hold, move the mouse to the right until columns A through D are selected, then release the mouse button. Next, under the Format menu, choose Column, then AutoFit Selection to set columns to the appropriate widths. You may want to manually decrease the width of column A. Don’t worry that the title up above does not spill over into adjacent empty cellsit does not do that when you are showing formulas. Just make sure the formulas are showing in their entirety. Also, formats will look different in this view (e.g., numeric values will be left aligned instead of right aligned, etc.). 20. Set to landscape orientation, use Print Preview feature to see how it will look, and finally print out this spreadsheet. To change this setting, go to Setup/Page/Landscape. ……………………………………………………………………………………………………………………… SECTION III: Spreadsheet Functions (FOR POINTS) ……………………………………………………………………………………………………………………… 1. In Lab 5-A, you had created a brand new spreadsheet for Section II, called Lab05A-Grades-LastFirst. 4 2. Open that spreadsheet file (or recreate it from Section II of Lab 05A if you didn’t do it the first time). Save it as Lab05B-GradesNew-LastFirst NOW STOP: How many Excel data files are still in memory? Only one! Not both! Only the one that you Saved As. The first file that you loaded into memory is no longer in memory. If you wanted to work with it again, you would have to load it from the disk back into memory. And, of course, you could load several more data files, if you wanted. Now, you are ready to do some interesting things with this spreadsheet. 3. In column E, enter a formula to calculate the total weighted score (cell E4) for the first student, taking into account the weights from instruction 3. The total weighted score will require multiplying each score by its weight, and adding each of these products together. Format the total weighted score to show one digit after the decimal point (remember the handy increase or decrease decimal point button on the tool bar). 4. Now copy the formula for the other students with the Fill handle or use the Fill command. 5. Then calculate a simple sum of the weights in row 16 using the appropriate formula and stored it in E16. That sum represents the total weight of all exams taken. 6. FOR EXTRA CREDIT! (5 raw pts). In column F, enter a formula to calculate the first student’s weighted average score*. Make sure your formula uses absolute references where appropriate so it can easily be copied with the fill handle. Format the weighted average grade to show one digit after the decimal point. Now fill the formula down for the other students. *Hint: Hmmm, you now have a weighted total score for that first student. What number should you divide his/her weighted total score by to get a weighted average score? (Careful! Imagine that some of the scores or even some of the weights may change later. Yet, everything should still work without you having to change the formulas again!). You have to do all this correctly to get the extra credit points. 7. Continuation of Required For Points section. Add a section starting in row 18 (similar to the worksheet in Project 2) where you display the average, maximum, and minimum for each exam. Use the appropriate functions to calculate these numbers. Make sure to label each row so you know what the numbers represent. Type your first and last name somewhere on your sheet and Save your spreadsheet before printing it. Print your spreadsheet twice, making the following changes. The first printout should show the correct values. The second printout should show your formulas (see pages EX 118 – 119) and it should be printed using landscape orientation. Be sure to auto-fit the column widths and to use Print Preview before printing! Most of the formatting will not be shown correctly whenever you change the view to “Print With Formulas Showing.” That’s perfectly fine. The whole purpose of printing with formulas showing is to examine the formulas themselves (the logical structure), not the formats (the physical structure.) MAKE BACK-UP COPIES …………………………………………………………………………………………………………………………… TURN IN …………………………………………………………………………………………………………………………… The six printouts from Section II The two printouts (one showing values, one showing formulas) of the spreadsheet from Section III (*) The above-named files on blackboard.unc.edu using the assignment tool. (*) with “Pledge” and your signature written on the front. …………………………………………………………………………………………………………………………… LAB-05B SUMMARY …………………………………………………………………………………………………………………………… Section II 5 1. Create a new spreadsheet and save it as Lab05B-Expenses-LastFirst 2. Enter a title “…………… Startup Expenses”in the first row: large (up to 24 points), Arial, bold, italic. 3. Starting in A4: enter the four labels shown on the sample (Quantity, Description, Cost Each,Total Cost); bold and centered; resize as needed. 4. Enter items that you need to purchase for your business, their quantity, and their costs each. 5. Enter a single formula that will calculate a Total Cost for each item, and fill down. Change column alignments as needed. 6. Format cost columns as Currency (two digits after decimal). 7. Enter appropriate SUM function formula for the grand total. Put a border around column headings and data. PRINT NOW. 8. Add somewhere within middle of the existing table (not first or last) a new item: a Coffee Grinder that costs $399.95 each and of which you have purchased 2. 9. Type your name a few rows below all that. PRINT NOW. 10. Save AS another file (to experiment with alternate expenses): Lab05B-Alternate-LastFirst. Amend title to reflect that this is an alternate expense calculation, and change at least HALF of the data values; observe. Now change two item names. PRINT NOW. 11. Open the Lab05BExpenses-LastFirst file and save it as Lab05B-MoreOffices-LastFirst. 12. Add the “Number of locations” subtitle appropriately (merging two cells for this). Enter ‘1’ in an adjacent cell (see example). 13. Select FIVE items (lines) that will also be needed in the new offices. 14. For ONE of those FIVE lines, edit the formula Total Cost. Multiply the total cost by the number of locations (use an absolute address reference for Number of Locations’ cell, then copy the modified formula to the four other lines you selected in item #13). Make these five new formulas BOLD. Add a notation about this toward bottom of sheet for documentation. 15. Enter DIFFERENT values for the number of locations. Just try out different numbers. Observe. 16. Enter 4 as final number of locations. PRINT NOW. 17. Add 3-D column chart; PRINT NOW. 18. PRINT OUT again with FORMULAS showing (be sure to adjust column widths and set to Landscape orientation). Section III 1. Open the file Lab05A-Grades-LastFirst from Lab05A. Save it as Lab05B-GradesNew-LastFirst. 2. In Column E, enter a formula that calculates the total weighted score (E4) for the first student. Format that total weighted score with ONE digit after decimal point. Fill formula for other students. 3. Calculate a simple sum of the weights, using appropriate function and store it in E16. 4. FOR EXTRA CREDIT (5 raw pts): In Column F, enter a formula to calculate the first student’s weighted average score (one digit after decimal point), using absolute/relative references appropriately! Fill down. a. Remember: scores may change later, weightings may change later; formulas must still work without editing. 5. Add a section that will display the average, maximum, and minimum, using appropriate functions to calculate each. Include labels. 6. Type your first and last name. 7. PRINT NOW (use autofit). 8. PRINT AGAIN showing formulas (in landscape, and use autofit) 6

DOCUMENT INFO

Shared By:

Categories:

Tags:

Stats:

views: | 1 |

posted: | 9/15/2012 |

language: | Latin |

pages: | 6 |

OTHER DOCS BY 71VH0PK

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.