University of Utah Basic Excel Homework

User Generated

Sntbbfr

Business Finance

The University of Utah

BCOR

Description

Work on the exercises as instructed in this PDF HERE

Actions

.

NOTE:

All answers requiring Excel formulas must be in Excel formulas instead of actual values. Otherwise you will receive zero credit. If you do not understand, please ask.

SUBMISSION DETAILS:

  1. Zip both files Nowlin.xlsx and NowlinFinancial.xlsx.
  2. Submit the zip file to canvas. How to zip files?
  3. Late submission is accepted for 1/2 credit 3 days after the due date, and 0 thereafter.

Unformatted Attachment Preview

BCOR 2020 Basics of Excel Outline • Using Microsoft Excel  Basic Spreadsheet Workbook Operations • Spreadsheet Basics  Cells, References, and Formulas in Excel  Finding the Right Excel Function  Colon Notation  Inserting a Function into a Worksheet Cell  Using Relative versus Absolute Cell • Install Excel Data Analysis ToolPak for MAC and Windows Using Microsoft Excel Blank Workbook in Excel Name of the book Cell A1 is selected Ribbon Worksheet tab Groups on the Home Tab in the Ribbon Home tab Clipboard group Front group Alignment group Number group Cell group Style group Editing group File Tab, Quick Access toolbar, and Formula bar of an Excel Workbook Quick Access toolbar Name Box Insert Function button Formula bar Formula box Basic Spreadsheet Workbook Operations Step 1: Right click on the worksheet tab named sheet. Step 2: Select the Rename option Step 3: Enter Nowlin to Rename the worksheet and press ENTER Continued… Basic Spreadsheet Workbook Operations You can create a copy of the newly renamed Nowlin worksheet by following these steps: Step 1: Right click the worksheet tab name Nowlin Step 2: Select the MOVE or COPY…option Step 3: When the MOVE OR COPY dialog box appears, select the checkbox for CREATE a copy, and click OK Continued … Basic Spreadsheet Workbook Operations The name of the copied Worksheet will appear as “NOWLIN(2)”. You can then rename it, if desired, by following the steps outlined previously. Worksheets can also be moved to other workbooks or to a different position in the current workbook by using the MOVE or COPY option. Continued…. Continued… Basic Spreadsheet Workbook Operations To create additional worksheets follow these steps: Step 1: Right click on the tab of any existing worksheet Step 2: Select INSERT Step 3: When the INSERT dialog box appears, select WORKSHEET from the General area , and click OK. OR…New worksheets can also be created using the insert worksheet button at the bottom of the screen. Creating, Saving, and Opening Files in Excel Step 1: Open a blank excel sheet and enter details as shown(sample) Step 2: Click the File tab on the ribbon Step 3: Click SAVE in the list of options Continued.. Continued..Creating, Saving, and Opening Files in Excel Step 4: Select THIS PC under SAVE AS, and click BROWSE Step 5: When the SAVE AS dialog box appears: Select the location where you want to save the file Enter the filename NOWLIN in the FILENAME: box Click SAVE Spreadsheet Basics: Cells, References, and Formulas in Excel From the previous excel,  We add Model into Cell A10 and press Bold button.  In A11 cell, we enter the label Quantity Continued... Continued…Cells, References, and Formulas in Excel  We enter the labels Total Cost to Produce, Total Cost to Outsource, and Savings due to Outsourcing in A13,A15, and A17 respectively.  Now we enter formulas in cells B13,B15 and B17. Continued... Continued….Cells, References, and Formulas in Excel  The formula for Total Cost to Produce is ( = B4+B11*B5 ) i.e Cell B4 PLUS Cell B11 MUTLIPLIED with Cell B5 and write this entire formula at Cell B13 ($254,000.00) NOTE : YOU MUST USE EQUAL TO ( = )SIGN BEFORE WRITING ANY FORUMULA AT RESPECTIVE CELL Continued… Continued….Cells, References, and Formulas in Excel  Next, Total cost to Outsource is the product of the outsourcing cost per unit (cell B7) and the quantity (cell B11); this is computed by entering the formula = B7*B11 at cell B15 ($35,000.00)  Finally, the savings due to outsourcing is computed by subtracting the cost of outsourcing (Cell B15) from the production cost (Cell B13). Thus, in cell B17 we enter the formula = B13-B15 ($219,000.00) Save file AS Nowlin.xlsx Finding The Right Excel Function To identify the functions available in excel click the INSERT Function button on the FORMULA bar.  This opens the INSERT function dialog box as shown below Continued... Continued….Finding The Right Excel Function  The SEARCH for a function box at the top of the dialog box enables us to type a brief description of what we want to do. Colon Notation  Colon notation provides an efficient way to convey arrays and matrices of cells to functions.  For example, the colon notation provides B2:B13 means cell B2 “through” cell B13, namely the array of values stored in the locations (B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13). Continued… Inserting a Function into a Worksheet Cell Step 1 : Insert the details as shown below in the new excel worksheet. Save the file as NowlinFinancial.xlsx Continued... Continued…Colon Notation  In cell B15  Enter = SUM(B2:B13). The sum function adds up the elements contained in the function’s argument. Continued... Continued…Colon Notation  Consider, for example, the following function = AVERAGE(B2:B13). The sum function averages all the elements contained in the function’s argument.  Do this in cell B17 Continued... Using Relative versus Absolute Cell Reference One of the most powerful abilities of spreadsheet software such as Excel is the ability to use relative references in formulas. Use of a relative reference allows the user to enter a formula once into Excel and then copy and past that formula to other places so that the formula will update the correct data without having to retype the formula. Lets practice this case. Continued... Continued…Using Relative versus Absolute Cell Reference Step 1: Enter the label profit in cell D1 and press the BOLD button in the Font group of the Home tab. Step 2: Enter the formula =B2-C2 in cell D2 as shown. Step 3: Copy the formula from cell D2 by selecting cell D2 and clicking COPY from the Clipboard group of the HOME tab or DRAG the cursor from cell D2 to D13. Continued... Continued….Using Relative versus Absolute Cell Reference Step 4: Select cells D3:D13. Step 5: Paste the formula from cell D2 by clicking Paste from the Clipboard group of the HOME tab. Values in column D can be seen as shown: Install Data Analysis ToolPak for MAC and Windows MAC : Installation Step 1: Open new excel sheet and in the menu bar we can see TOOLS option, and click it. Continued... Continued… Install Data Analysis ToolPak in Excel When we click TOOLS, we can observe at the bottom of the drop-down window an EXCEL ADD-INS. Click it. Continued… Continued… Install Data Analysis ToolPak in Excel When you click on EXCEL ADD-IN you will see a new pop up window as shown below and here click on Analysis Tool Pak Check-box and next click on OK button. Continued… Continued… Install Data Analysis ToolPak in Excel After clicking OK button, now go to DATA tab in the excel ribbon then you can find DATA ANALYSIS at the TOP RIGHT MOST corner as shown: WINDOWS : Installation Step 1: Open a new excel sheet and click on File. Continued… Continued….Install Data Analysis ToolPak in Excel Step 2: Click on Options Continued… Continued..Install Data Analysis ToolPak in Excel Step 3: After clicking options, a new pop up window appears, in that choose ADD-INS and at the bottom of the window choose EXCEL ADDINS under manage dropdown field and Click on GO button. Continued… Continued…Install Data Analysis ToolPak in Excel Step 4: Next, we can see a ADD-INS pop up window that has four check boxes. Now click on Analysis Tool pak checkbox and click on OK button. Continued… Continued…Install Data Analysis ToolPak in Excel Step 5: Now , under menu bar go to DATA and you can find the DATA ANALYSIS at the right most corner of excel sheet. Lab Submission • Zip both files – Nowlin.xlsx – NowlinFinancial.xlsx. • Submit the zip file to canvas. • Late submission is ½ credit up to 3 days late. 37 Outline * Using Microsoft Excel  Basic Spreadsheet Workbook Operations * Spreadsheet Basics  Cells, References, and Formulas in Excel  Finding the Right Excel Function  Colon Notation  Inserting a Function into a Worksheet Cell  Using Relative versus Absolute Cell * Install Data Analysis ToolPak in Excel in MAC and in Windows END 39
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Please ...


Anonymous
Awesome! Made my life easier.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags