-------------------------------------------------
New Perspectives excel 2010
-------------------------------------------------
Tutorial 7: Case Problem 1
PC-Market Distribution
Skills

Insert calculated columns in an Excel table
Use the IF function
Use structured references to create formulas in Excel tables
Create nested IFs
Use the VLOOKUP function to find and exact match
Use the VLOOKUP function to find an approximate match
Using the IFERROR function
Highlight duplicate values
Summarize data conditionally
Use the COUNTIF function
Use the AVERAGEIF function

Project overview
Linda Klaussen works for PC-Market Distribution, a computer supply store. She needs your help with designing an Excel workbook to enter purchase order information. She has already entered the product information on PC-Market’s line of modems. She wants you to insert a lookup function to search for data in the product table. The company also offers three shipping options—Standard, Express, and Overnight—that vary in price. She wants the purchase order worksheet to be able to calculate the total cost of the order, including the type of shipping the customer requests. STUDENT start FILE

NP_Excel2010_T7_CP1a_FirstLastName_1.xlsx (Note: Download your personalized start file from www.cengage.com/sam2010)

Instructions
Open the file NP_Excel2010_T7_CP1a_FirstLastName_1.xlsx and save the file as NP_Excel2010_T7_CP1a_FirstLastName_2.xlsx before you move to the next step. Verify that your name appears in cell B4 of the Documentation sheet. (Note: Do not edit the Documentation sheet. If your name does not appear in cell B4, please download a new copy of the start file from the SAM Web site.)

2.Go to the Purchase Order worksheet. In cell B5, enter the Product ID 1050. Then use the VLOOKUP function in cells C7, C8, and C9 as follows: a)...

...Excel
e
This Course Covers: • • • • • • Spreadsheet fundamentals: How to open, create, and work with a spreadsheet How to enhance spreadsheets using formatting techniques How to create and work with charts How to create formulas to perform a variety of calculations How to manage workbooks and advanced printing options How to analyze data using Excel’s list features
Microsoft
®
Table of Contents
Introduction .......................................................................................................................... 7 Chapter One: The Fundamentals...................................................................................... 11 Lesson 1-1: Starting Excel...................................................................................................12 Lesson 1-2: What’s New in Excel 2002?.............................................................................14 Lesson 1-3: Understanding the Excel Program Screen........................................................16 Lesson 1-4: Using Menus ....................................................................................................18 Lesson 1-5: Using Toolbars and Creating a New Workbook...............................................20 Lesson 1-6: Filling Out Dialog Boxes .................................................................................22 Lesson 1-7: Keystroke and Right Mouse...

...A computer is a general purpose device that can be programmed to carry out a set of arithmetic or logical operations automatically. Since a sequence of operations can be readily changed, the computer can solve more than one kind of problem.
Conventionally, a computer consists of at least one processing element, typically a central processing unit (CPU), and some form of memory. The processing element carries out arithmetic and logic operations, and a sequencing and control unit can change the order of operations in response to stored informPrograms
The defining feature of modern computers which distinguishes them from all other machines is that they can be programmed. That is to say that some type of instructions (the program) can be given to the computer, and it will process them. Modern computers based on the von Neumann architecture often have machine code in the form of an imperative programming language.
In practical terms, a computer program may be just a few instructions or extend to many millions of instructions, as do the programs for word processors and web browsers for example. A typical modern computer can execute billions of instructions per second (gigaflops) and rarely makes a mistake over many years of operation. Large computer programs consisting of several million instructions may take teams of programmers years to write,...

...How to Use a Data Spreadsheet: Excel
One does not necessarily have special statistical software to perform statistical
analyses. Microsoft Office Excel can be used to run statistical procedures. Although in
some respects Excel is not as preferable for data analyses as IBM SPSS, it is very userfriendly with simpler statistical procedures. This appendix describes how to use Excel to
execute basic statistical calculations. Data from the 2004 version of the General Social
Survey (GSS) is used for examples. This Appendix is based on Excel 2007 version, which
differs in certain aspects from Excel’s previous versions. The most notable change that
affects the exercises presented in this appendix concerns the pivot table feature.
BASIC PROCEDURES
Starting Excel:
To start Excel using Windows, click on the Start button at the bottom left corner of
the screen. Under Programs locate and click the Microsoft Excel icon.
The layout of the Excel program has changed substantially for the Microsoft Office
2007 edition compared to its predecessors. Commands are now grouped in ribbons that are
accessed by clicking on a specific tab. Thus, the Home tab grants access to a ribbon of
several command groups: Clipboard, Font, Alignment, Number, Styles, Cells, and
Editing.
Once the program is started you will see a Worksheet Area that consists of cells...

...Student ID: 21709822
Exam: 038229RR - Microsoft Excel
When you have completed your exam and reviewed your answers, click Submit Exam. Answers will not be recorded until you
hit Submit Exam. If you need to exit before completing the exam, click Cancel Exam.
Questions 1 to 20: Select the best answer to each question. Note that a question and its answers may be split across a page
break, so be sure that you have seen the entire question and all the answers before choosing an answer.
1. The order of precedence is very important when building formulas in Excel. Which of the following
formulas will produce 778 as the result?
A. =25*(27/9 + 5) + 309*2
B. =25*27/9 + 5 + 309*2
C. =25*((27/9 + 5) + 309*2)
D. =(25*(27/9) + 5 + 309)*2
2. Which of the following identifies the patterns used for each data series in a chart?
A. The horizontal and vertical axes
B. Data series
C. Legend
D. Data point
3. Helga needs to lay out her newly created pivot table. What are the two ways she can set up a pivot
table?
A. When Helga created the pivot table, Excel automatically put the fields to the appropriate cells of the pivot table. There aren't
two ways to set up a pivot table.
B. Helga can check the field or type in the data in the pivot table.
C. Helga can drag the fields to the four boxes in the pivot table field list or right-click a field name and choose its location from
the shortcut menu.
D. Helga can slide cells to the four boxes or double-click...

...Getting Started in Excel
Many of the editing tools that you use in Excel are similar to Word and PowerPoint
You will need to become familiar with a few new things for Excel: Rows, Columns, Various Formulas, and Charts just to name a few
When you see a reference such as A7, A is the column and 7 is the row, this will give you the particular cell that is being referenced.
You will learn that you can click or enter in information when it comes to cells and formulas. Everyone will have their own preference on how to do this and one way is not better than the other.
Basic calculations can be done as follows:
=SUM(A1:A7)
=A1+B2
=4+3
Once you have entered a formula for a cell – if the following cells require the same formula you can click and drag your mouse to copy the formula to adjacent cells
Once you have entered a formula you can change one of the numbers used in the formula and it will automatically adjust the results of the formula!! This allows you to use spreadsheets repeatedly without having to redo everything.
There are several display options for the way the numbers will appear (accounting, general, etc.)
For formulas just think back to the basic operations of math (parenthesis, adding, subtracting, dividing, etc.) – Excel does what you tell it to.
If you find your numbers are not coming out right, check for numbers entered incorrectly! This is the case 99% of the time. It is easy to transpose a...

...Week 6
Excel
PF008 A14
Introduction to Spreadsheets
A spreadsheet is an electronic file that contains a
grid of columns and rows used to organize related
data and perform calculations.
Excel is a spreadsheet program used to create and modify
electronic spreadsheets.
An electronic spreadsheet makes data-entry changes easy,
and if the formulas are correctly constructed, the results
recalculate automatically and accurately.
A worksheet is a single spreadsheet that typically contains
descriptive labels, numeric values, formulas, functions, and
graphical representations of data.
A workbook is a collection of one or more related
worksheets contained within a single file. Each worksheet
is identified by a sheet tab. By default, new workbooks
have three worksheets.
The whole file is a workbook
Worksheet
Planning Structure of Worksheets
1. State the purpose of the worksheet
2. Decide what input values are needed
An input area is a range of cells containing values
3. Decide what outputs are needed
An output area is a range of cells containing
results
4. Assign the worksheet inputs and results
Use rows and columns
5. Enter the labels, values, and formulas
6. Format the numerical values
7. Format the descriptive titles and labels
8. Document the worksheet
9. Save the completed workbook
Exploring the Excel Window
• Worksheet rows lie horizontally
• Worksheet columns lie...

...history.
First of all, Microsoft Excel is the industry standard in spreadsheets (history on spreadsheets can be accessed at the above website address). With Excel, you can create a shared workbook and place it on a network location where several people can edit, change, and work on the contents of a workbook, I discovered. Therefore, if the people in your work group each handle several projects and need to know the status of each other's projects, the group can use a shared workbook to track the status of the work, make changes, etc. Everyone can then enter the information for their projects in the same workbook from the shared network service.In researching this topic, I found out that you can manage it by removing users from the shared workbook and resolving conflicting changes – and when you initially set up the workbook you can make some features permanent, which cannot be changed by others. When all changes have been incorporated, you can stop sharing the workbook. So, this gives you some control.
In Excel: you simply open up your Excel worksheet, click on Review and click on Share Workbook. Then from dialog box you allow changes by more than one user. There’s an advance function on the dialog box that allows you to allow others to see it (for a period of time) and to keep you updated on changes to...

...Frist step , collecting the data from Yahoo. Finance, and calculate the quarterly return by the formula (return of fourth mouth- the return of frist mouth)/ retrun og first mouth
Question a
Average quarterly return= average (all returns of one asset)
Standard deviations= stdeva (all returns of one asset)
Question b and c
File—options—add-ins—solver add in--go, and click all the options, then using the data analysis, choosing correlation (covariance), then selecting all the returns of all assets.
Question d
The average weight= 1/12
Sum of weight =1
Solver portfolio
Return=MMULT(whole covariance matrix, TRANSPOSE(whole correlation matrix))
Variance ==MMULT(average weight,MMULT(whole covariance matrix, TRANSPOSE(average weight)))
Risk =SQRT(Variance)
Mvp
First calculate sigma and return
Sigma =SQRT(MMULT(MMULT(weight,whole covariance matrix),TRANSPOSE(weight)))
Return=MMULT(weight,TRANSPOSE(annual return))
Then using solver set sigma in target cell, choosing min, by changing cells: total weight
The constrains should be sum of weight =1
Lastly, click solve.
Question e
Set a target of sigma ,similarly to one of weight, and though slover,
set the return as target cell, choosing max , by changing cells : all weight, the constraints should be :sigma= target of sigma, sum of weight =1,click solve.
So after getting a new group figure of sigma, return and weight, change target of sigma slowly, and get 10 different sigma result, the last three should...

909 Words |
3 Pages

Share this Document

{"hostname":"studymode.com","essaysImgCdnUrl":"\/\/images-study.netdna-ssl.com\/pi\/","useDefaultThumbs":true,"defaultThumbImgs":["\/\/stm-study.netdna-ssl.com\/stm\/images\/placeholders\/default_paper_1.png","\/\/stm-study.netdna-ssl.com\/stm\/images\/placeholders\/default_paper_2.png","\/\/stm-study.netdna-ssl.com\/stm\/images\/placeholders\/default_paper_3.png","\/\/stm-study.netdna-ssl.com\/stm\/images\/placeholders\/default_paper_4.png","\/\/stm-study.netdna-ssl.com\/stm\/images\/placeholders\/default_paper_5.png"],"thumb_default_size":"160x220","thumb_ac_size":"80x110","isPayOrJoin":false,"essayUpload":false,"site_id":1,"autoComplete":false,"isPremiumCountry":false,"userCountryCode":"US","logPixelPath":"\/\/www.smhpix.com\/pixel.gif","tracking_url":"\/\/www.smhpix.com\/pixel.gif","cookies":{"unlimitedBanner":"off"},"essay":{"essayId":35737669,"categoryName":"Organizations","categoryParentId":"3","currentPage":1,"format":"text","pageMeta":{"text":{"startPage":1,"endPage":3,"pageRange":"1-3","totalPages":3}},"access":"premium","title":"Designing an Excel Workbook for a Computer Store","additionalIds":[5,79,17,57],"additional":["Computer Science","Computer Science\/Software Development","Literature","Business \u0026 Economy\/Products"],"loadedPages":{"html":[],"text":[1,2,3]}},"user":null,"canonicalUrl":"http:\/\/www.studymode.com\/essays\/Designing-An-Excel-Workbook-For-a-988382.html","pagesPerLoad":50,"userType":"member_guest","ct":10,"ndocs":"1,500,000","pdocs":"6,000","cc":"10_PERCENT_1MO_AND_6MO","signUpUrl":"https:\/\/www.studymode.com\/signup\/","joinUrl":"https:\/\/www.studymode.com\/join","payPlanUrl":"\/checkout\/pay","upgradeUrl":"\/checkout\/upgrade","freeTrialUrl":"https:\/\/www.studymode.com\/signup\/?redirectUrl=https%3A%2F%2Fwww.studymode.com%2Fcheckout%2Fpay%2Ffree-trial\u0026bypassPaymentPage=1","showModal":"get-access","showModalUrl":"https:\/\/www.studymode.com\/signup\/?redirectUrl=https%3A%2F%2Fwww.studymode.com%2Fjoin","joinFreeUrl":"\/essays\/?newuser=1","siteId":1,"facebook":{"clientId":"306058689489023","version":"v2.8","language":"en_US"}}