{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Excel_Tutorial_1 - Student’s Name Excel Tutorial No 1...

Info icon This preview shows pages 1–24. Sign up to view the full content.

View Full Document Right Arrow Icon
Image of page 1

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 2
Image of page 3

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 4
Image of page 5

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 6
Image of page 7

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 8
Image of page 9

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 10
Image of page 11

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 12
Image of page 13

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 14
Image of page 15

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 16
Image of page 17

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 18
Image of page 19

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 20
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Student’s Name: Excel Tutorial No. 1 Structured Spreadsheets (10 points) Requirements: 0 To receive credit for a component, you must meet all requirements for the component. No partial credit is granted. Each component is graded with an “all or nothing” criterion. 0 Required documents must be attached to this requirements’ sheet in the order listed. Points will be deducted if pages are out of order! I This requirements” page must be stapled to the front of the documents you are handing in. Points will be deducted if this requirements page is missing or if the pages are not stapled together! Printing and Spreadsheet Headers and Footers (4 points) Are printouts of the Input, Calculation, and Re 011 s readsheets attached to this form? Do the spreadsheets contain the correct values? Are the Spreadsheets properly formatted? Dees each spreadsheet include the appropriate headers and footers? Displaying Formulas and Auditing the Workbook (3 points) Are printouts of the Calculation and Report Spreadsheets that display formulas attached to this form? S o readsheet'? Are named ranges used in the Report 5 u readsheet? “What If” Analysis (3 points) Re uirement Oka '2 Are updated printouts of the Input, Calculation, and Re ort spreadsheets attached to this form? Does the Input spreadsheet include the new sales volume amount? Do the Calculation and Report spreadsheets — display correct values that reflect the increased sales volume? Assignment 1 Introduction to Excel References Freeman, D. 1996. How to Make Spreadsheets Error-Proof. Journal of Accountancy. (May): 753?. Kreie, J. and .l. Pendley. 1998. Spreadsheet Safety. Journal of Accountancy. (November): 31-34. Purpose The first purpose of this tutorial is to increase your spreadsheet knowledge. The second, more important, purpose is to demonstrate how to create well-structured workbooks that are easily understood and audited. You will need the knowledge and skills learned from this tutorial for other Excel assignments. Please make sure you fully understand each step that you perform. .lust following the instructions and making key strokes to complete this assignment may cause problems when you work on other assignments. Knowledge Objectives This tutorial provides an exposure to the following: Basic Spreadsheet Skills 0 Enter and format spreadsheet information o Formulas and Paste Function wizards 0 Working with multiple spreadsheets or workbooks 0 Spreadsheet Viewing options a Print spreadsheets and formula Structured Spreadsheets - Use separate Input, Calculation, and Report spreadsheets to increase the accuracy of spreadsheets Vocabulary Spreadsheet A table of cells arranged in rows and columns. Cells can have predefined relationships with other cells, where changing the coments of one cell automatically changes related cells. Workbook Powerful spreadsheet programs allow the creation of multiple Spreadsheets within the same file. Files that contain several spreadsheets are called workbooks. However, while these definitions distinguish between Spreadsheets and Workbooks, most people use the terms interchangeably! Spreadsheet Introduction Spreadsheet and Workbook programs automate many of the tasks required of accountants and business professionals. For example, spreadsheets allow you to manipulate numbers, perform “what if" analysis, and make tabular reports. Spreadsheets are pervasive in today’s business environment. Professionals are expected to understand how spreadsheets work, to recognize and develop well-designed spreadsheets, and to effectively use spreadsheets to solve problems. Being able to effectively apply spreadsheets to problems will make you more productive, both in your life as a student and your life as a professional. The goal of this tutorial, therefore, is to introduce you to the fundamentals of spreadsheets. Spreadsheets have had a tremendous impact on the business World. Early personal computers were used only by hobbyists who were simply intrigued with the technology. Businesses did not begin to use personal computers until the first successful killer application, a program that revolutionizes how a common problem experienced by many is solved, was created. VisiCalc, the first spreadsheet program, was the original killer application that started the personal computer revolution. Unfortunately, the creators .of Vi siCalc failed to copyright their invention and do not receive any royalties on the millions of spreadsheet programs sold each year! Moral of the story: remember to copyright the next “killer application" you create. Experienced spreadsheet users will be familiar with some of the material covered in this tutorial. However, experienced spreadsheet users rarely consider the design implications of the workbooks they create, which is a major component of this tutorial. 'l‘herefor, both new and experienced spreadsheet users will benefit from this tutorial. Using Excel This section provides a brief Excel overview. While students are always encouraged to be creative and innovative, creativity will cause serious difficulty with this lab. For ease of reference, the tutorial refers to specific cells on specific spreadsheets. If students have not followed the tutorial exactly, cell references will be meaningless. Therefore, information should be entered exactly as discussed. Once you have mastered spreadsheets, then you can be creative. Excel Screens 1) Open Excel, which is under Start — Programs — Spreadsheets — Excel — Excel 2003 for most lab computers. 2) Once Excel has started, use the following narrative and the screen displayed on your computer to acquaint yourself with Excel (see Figure 1). 0 There are lots of functions of Task Pane, e.g. connect Microsoft Office Online, show the recently used file or create a new document. If you don’t want the task pane shown on the screen, click the “x” on the right top comer to close it. 0 There are many tool bar buttons at the top of the screen. Each button has an icon or picture that describes the function Excel will perform when you press this button. Several of these buttons are used in this tutorial. 0 Spreadsheets consists of cells that are organized in columns and rows. Each cell, therefore, has an address that identifies its location in the spreadsheet. The first cell in the spreadsheet is named Al because it is in column A, row 1. a The Formula Bar displays cell contents. When you use formulas, the cell displays the result of the formula while the Formula Bar displays the underlying formula. 0 Tabs labeled Sheetl, Sheet2, Sheet3 appear across the bottom of the screen. I Each tab represents a different spreadsheet. Excel is a workbook program that allows you to use several spreadsheets within the same file. The tabs allow you to easily access each spreadsheet. i) Use your mouse to point and click on different tabs. ii) Because there is no data in our spreadsheet you will not see much of a change when you move from spreadsheet to spreadsheet, but you can see that you are moving between the spreadsheets. Excel Practice We are going to do some quick data entry to introduce you to spreadsheet concepts and make you more comfortable with the screen displayed on your computer. 1) Three types of data can be entered into a spreadsheet: labels, values, and formulas. Labels 2) are simply text entered into a cell. Values consist ofnumbers entered into a cell. Formulas are calculations or functions entered into a cell. Labels and values appear and print as they are entered. However, Excel’s default setting is to display and print the results of formulas. a) Move to cell Bl of your spreadsheet, enter JAN into this cell press Enter. Move to cell C 1 , and type FEB. You could continue in this fashion for all of the months in the year. However, Excel has several shortcuts that can automate data entry. i) Click on the cell that contains FEB. ii) Move your cursor to the bottom right-hand corner of the cell. Your cursor should change to a small, black plus sign (+). iii) Press and hold down the left mouse button. iv) Move your mouse to the right several cells. You will see a light box around all of the eells you are highlighting; make sure you are only highlighting cells on the first line. If you are doing this correctly, you should only be able to highlight either one line vertically or one line horizontally. Release the mouse button. Excel fills in the months for column headings, and recognized that you only wanted abbreviations! b) V) Spreadsheets are able to perform calculations on numbers that you have entered, just like a calculator. a) Move to cell 82 and type =5+6. Notice that the formula bar and the cell itself display the formula. Press Enter. Excel knows to perform a calculation because the first thing you typed in the cell was an equal sign. Because this was a formula, the screen now displays 11 in cell B2. ' ' 0) Move back to cell 132, and look at the Formula Bar. Notice how cell B2 displays the result ofthe formula, ll, while the Formula Bar displays =5+6 formula. b) 3) However, spreadsheets are far more powerful than a simple calculator is. The true power of spreadsheets lies in their ability to use values from other cells as input values for formulas. Excel can understand formulas that reference other cells and use their values as a part of a calculation! a) Move to cell C2. In this cell, we are going to enter a formula that takes the value from cell B2 and adds 1. b) - Start the formula by typing an equal sign (=). c) Use your left arrow or mouse to move to the cell B2. This'cell should be highlighted with light moving dashes, and you should see B2 in both cell C2 and the Formula Bar. d) Type a plus sign (+) and notice that B2 is highlighted as solid blue line. 6) Type a l. ' - 0 Press Enter. You should see a 12 displayed in cell C2. You should be able to see the formula you created inlthe Formula Bar when you move back to cell C2. 4) Now we want to include formulas in the cells to the right of cell C2 that continue to add 1 to the value in the cell to the left. While we could manually create a new formula for each cell as demonstrated in the above step 3, typing each formula is not efficient. Luckily, Excel can copy formulas. ' a) Click on cell C2. b) Use the mouse to press the COPY button, which is represented with an icon that contains two documents. c) At the bottom of the screen you should see a message that says “Select destination and press ENTER or choose paste”. I d) Use your mouse to highlight several of the cells to the right of C2. To highlight several cells: ' i) Point at the first open cell, which should be cell D2. ii) Press and hold down the left mouse button. iii) Drag the mouse to the last cell. iv) Let go ofthe mouse button, and press Enter. Notice how the cells display 13, 14, 15, etc. e) Move to the cells that contain the new formula and watch the Formula Bar. See how the formula has been copied to all of the cells. ' i) When Excel copies formulas it, by default, copies the relative formula. This means that the formula we type is actually evaluated by Excel as: “Move One cell to the left of the current cell, and determine the value of that cell. Add one to this value, and display the result in the current cell. ” When you copy the formula to cell D2, the formula process will be the same. However, the address of the cell “one cell to the left" is now C2, not B2. Excel modifies the formula to show the new address. 5) Close this spreadsheet (File - Close). Choose No when you are asked if you want to save changes. Structured Spreadsheets As discussed in the readings cited in the References section, large spreadsheets are notoriously fraught with errors. To avoid such errors, Freeman (1996) suggests using three categories of spreadsheets within a workbook. First, lnput spreadsheets serve as a single point of data input for a workbook. The Input Spreadsheet contains the numbers users manually enter or frequently change. Second, Calculation spreadsheets are used to manipulate the numbers from the Input spreadsheet. Separate Calculation spreadsheets allow space to break long formulas into smaller, component formulas that are much easier to audit. Third, Report spreadsheets display information from the Input and Calculation spreadsheets and present the information in a meaningful manner. The Merchant of Venice Cash Flow Forecast Case After several days of rigorous analysis, personal introspection, and a short session with a Magic 8-Ball®, the Merchant of Venice developed forecasts for her new business. The remainder of this tutorial walks through the steps required to develop, audit, and print a well-structured workbook that forecasts future cash flows for the Merchant of Venice. In the following pages of this tutorial, you will: - Create a new workbook with Input, Calculation, and Report spreadsheets. - Create an Input spreadsheet that contains labels and values for the forecasts developed by the Merchant. The Input spreadsheet will initially look similar to Figure 2. The input sheet will then be formatted to appear similar to Figure 3. I o Create a Calculation spreadsheet that manipulates information from the Input Spreadsheet. The finished Calculation spreadsheet will look similar to Figure 4. o Create a Report Spreadsheet that is formatted as a traditional financial report. Figure 5 depicts the completed Report spreadsheet. ‘ 0 Print the workbook and create Headers and Footers for each of the spreadsheets. - Change the Excel diaplay settings to display formulas, instead of the outcome of the fom‘iulas. Figure 6 depicts a spreadsheet displaying formulas instead of values. 0 Perform a “What If” scenario. You will change one of the input values and see how the spreadsheet is automatically updated. Create a New Workbook The first step in creating a well-structured workbook consists of creating a new workbook, naming spreadsheets, and saving the new workbook. I} Use your mouse to click on the New icon to open a new Excel workbook. D 2) Next, the spreadsheets should be named, as the default Sheet], Sheet2, and Sheet3 are not *““"“"“’"‘"‘“ very descriptive. a) Double click on the Sheetl tab. The tab will turn black and the text will turn - white, _ limit b) Once the tab has been selected, or turns black, simply type the new name for the tab, which is Input. c) Press Enter. d) Change the names of Sheet2 and Sheet3 to Calculation and Report, respectively. Figure 2 depicts a workbook with the changed spreadsheet names. Important 3) Save your spreadsheet by pressing the Save button. When prompted, save the Spreadsheet with the name “MOV Structured Spreadsheets by YourFirstName YourLastName.” For me. the file name is “MOV Spreadsheets by Janie Chang.” a) It is important that you use the correct name for your file. You will be accused of cheating if you turn in assignments that use incorrectly named files. Create an Input Spreadsheet Once the new workbook has been created, we can begin to complete the Input spreadsheet. The process of creating the input spreadsheet includes entering and formatting the text labels and titles and entering and formatting the numeric input values. In her forecasting process, the Merchant identified several key items that affect her business’s cash flow. Since each of the following items contain new information provided by the Merchant, they are all included in the Input Spreadsheet. - Outsider Investment — The Merchant hopes to borrow money from outside investors. 0 Amount Spent on Ships — The Merchant plans to purchase a boat that will be used to transport silk from China. I Forecasted Sales, in Bolts — Based on market research, the Merchant forecasted the number of bolts of silk she plans to sell each year. 0 Sales Price per Bolt — The market research has also allowed the Merchant to project the price she will be able to charge for-silk in future years. ' 0 Cost per Bolt — The Merchant has projected the price she will have to pay for silk. 0 Deckhand Cost, per Trip — The Merchant has been monitoring the labor market and deveIOped forecasts for the cost of deckhands. 0 Cost of Additional Manager — With the anticipated growth, the Merchant plans to hire an additional manager once sales reach a certain level. Her labor market research has enabled the Merchant to forecast the cost of an additional manager. Enter and Format Spreadsheet Titles and Labels Now that we know what information the Merchant plans to use in forecasting cash flow, we can begin the Input spreadsheet. Use Figure 2 and Figure 3 as guides for completing this section. 1) Move to your Input spreadsheet and enter the titles that appear in Figure 2. Simply enter the text as it appears in Figure 2. We will begin to format the text in the next step. a) To move between spreadsheets, you can either click on the Spreadsheet tabs with your mouse or use the <CTRL><PAGE UP> and <CTRL><PAGE DOWN> key combinations. - 2) Once you have entered the titles and labels, you will quickly notice that the text needs to be formatted to provide a more pleasant presentation of the information. a) The titles in rows 1 and 2 should be centered over all of the spreadsheet data and their fonts changed. i) Highlight cells A1 to D1 ii) Click on the Merge and Center button to merge cells A1 to D1 into a single large cell and center the text within the new cell. iii) While the new cell is still selected, format the text to be bold and 18-size font. iv) Use the same process to format the title in row 2, only make the text italic and 14—size font. til i . b) The year title and the year numbers should be bold and underlined. To format everything in row 4 at the same time: i) Click on the box that identifies row 4. The entire row will be highlighted. 31 i ii) When you click on the Bold and Underline buttons, everything in row 4 becomes bold WWW; and underlined. c) Column A needs to be widened, as the labels exceed the width of the column and run into columns B and C. In Excel, column widths can be set manually or Excel can automatically adjust column widths. i) First, manually resize column A. (1) Move your mouse to the top of the sheet and point at the line between column A and B. Note that the label of column A and the label of row 1 turn to orange. The normal plus sign cursor will change into a two-headed arrow. (2') Hold down the left button and drag the column width to the right until the column is just slightly larger than the text that appears in column A. ii) You can also let Excel determine the size of the column. This feature is particularly helpful when the spreadsheet has many long rows that cannot be displayed on the computer screen at the same time. (1) Again, move your mouse to the top of the sheet and point at the line between column A and B. The normal plus sign cursor will change into a two-headed arrow. (2) Position the double-headed arrow on the right end of the orange box that identifies column A. (3) Double click on orange box and the column is automatically resized. Enter and Format Spreadsheet Values With the titles and labels entered and formatted, we are ready to enter the Merchant’s forecasted values. 1) Input the values depicted in Figure 3. I 3) Never manually enter dollar signs (‘5) into a spreadsheet. If dollar signs are entered into a cell. Excel treats the contents as text and cannot use the contents of the cell in formulas. The dollar signs will be added with special formatting buttons in subsequent steps. ._ g 2) Once the values have been entered, they need to be formatted. a) Format cells that contain dollar values by highlighting the cells and clicking on the Currency Style button. it _ g b) Format the cells containing quantities using the Comma Style button. I i) The Comma Style button default is to display two decimal places. Use the Decrease ''''' Decimal button and format cells that contain quantities to display no decimal places. 3). Once your Input spreadsheet loo...
View Full Document

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern