Premium Essay

MIS-505

Questions for Final Exam

1. What is name range? How can ranges be named? What are its uses? 2. What is lookup in Excel? What are the different kinds of lookup functions? What are its uses? 3. What is Index function? Write down the procedure of index function. What are its uses? 4. What is Match function? How you can write an Index and Match functions in a nested way? What are its uses? 5. Describe different kinds of text functions in Excel (such as left, right, mid). 6. How can you put date in Excel? What are its uses? 7. What is NETWORKINGDAYS function in Excel? What is NETWORKINGDAYS.INT function in Excel? What are its uses? 8. What is workday and WORKDAY.INT functions in Excel? What are its uses? 9. What is Net Present Value? How do NPV and XNPV function work in Excel? What are its uses? 10. What is internal rate of return (IRR)? How do IRR, XIRR, and MIRR work in Excel? What are its uses? 11. What is present value? How does PV function work? What are its uses? 12. What is Future Value? How does FV function work? What are its uses? 13. Write down the functions of PMT, PPMT, IPMT. How can you use these functions to prepare a loan amortization schedule? 14. Write down the function of IF Statement, AND, OR and XOR. 15. Describe different Time functions. What are its uses? 16. What is What If Analysis? Describe different kind of what if analysis. What are its uses? 17. What do you mean by Sensitivity Analysis? What are its uses? 18. Describe difference between What if analysis and sensitivity analysis. 19. What is data table? How do one-way and two-way data tables work in Excel? What are their uses? 20. What is scenario manager? Describe the process of using scenario manager. 21. What is the importance of sensitivity analysis?

Premium Essay

...Strengths and Weaknesses of Each Spreadsheet (Teacher) Aims to help the teacher: * Recording Results * Tracking the Learners’ Progress Strengths: One of the strengths of the teacher’s spreadsheet was the use of conditional formatting. Conditional formatting was helpful by easily presenting data, by formatting a range of cells depending on certain grades or scores means that analysing and categorising data will much easier with a visual representation and the teacher can make more accurate conclusions. The way the tables are laid out, and by defining a range is very useful for a teacher when it comes to neatly organising a spreadsheet with many tables of class’s results. By defining a range means that tables are easy to input into formulae, and will help reduce errors. AutoSum is very beneficial to a teacher because it simply adds up an entire column without having to add each cell individually. This saves a lot of time, and helps reduce errors, especially if there is lots of students’ data. Weaknesses: Macros are not included in the spreadsheet, which would’ve been very useful for a teacher. Macros stop repetitive tasks being completed over and over again. This is useful as it saves a lot of time to the teacher, e.g. if they click a customised button which filters data or changes sheets. This will also improve the usability of the spreadsheet and make the data input easier. Goal seek, which also isn’t included in the spreadsheet, would also be very beneficial...

Words: 1576 - Pages: 7

Free Essay

...Used: * Microsoft Excel 2007 (Home edition) * Microsoft Word 2008: (License No: 92464-513-2506215-12994) * Microsoft Expression Web (Student version) Attached Files: * Business Report.docx * Spreadsheet Assignment.xlsm * Website files * home.html * about.html * sit.html * work.html * registration.html * locate.html * contact.html * Formulae sheets Attempted the High Distinction Level Table of Contents 1.0 Executive Summary 3 2.0 Business Problem Opportunity 4 3.0 Recommendations (based on ‘What-if’ analysis) 5 3.1 Production targets 5 3.2 Fixed costs 5 3.3 Sales 6 4.0 Appendices 7 4.1 User instructions 7 1.0 Executive Summary Classic Offices is a company that manufactures and sells chairs. The recent recession of 2009 had a negative global effect on markets all over the world and Classic Offices was adversely affected too. Along with vital information such as income and commission figures, the increment of fixed costs and reduction of profits have been captured in the form of this user-friendly spreadsheet. This spreadsheet has been designed with the consideration of Classic Offices’ internal users in mind. To make interpretation of the spreadsheet easier: * Different years have been colour coded * Appropriate command buttons (e.g. ‘Home’, ‘Print’) are accompanied with icons Internal users of Classic Offices (mainly employees) will be able to view...

Words: 1137 - Pages: 5

Premium Essay

...Use of Spreadsheets in Business and Technology Marty Montgomery August 25, 2013 Many corporations depend on spreadsheets as a fundamental (Institute of Corporate Auditors, 2006) device in their ﬁnancial reporting and operational processes. As a result, the use of spreadsheets is an essential part of the information and decision-making structure for these organizations. In developing and using spreadsheets, organizations need to balance their ease and ﬂexibility against the value of reliable information for management’s application. As users of spreadsheet applications such as Microsoft Excel® or Lotus 1-2-3® have become more sophisticated, so have spreadsheets. Once used to support simple functions such as logging, tracking and totaling information, spreadsheets with enhanced formulas and built-in advanced features are now used to support such business functions as complex valuation models. The use of macros and multiple spreadsheets which are linked together allows users to build very complicated—and sometimes convoluted—models and other business functions with minimal or no documentation. In addition, these complex spreadsheets are not usually supported by the same control environment as formally-developed, purchased applications. For example, the developers and users of spreadsheets are usually not trained in structured programming, testing, version control or systems development life cycles, and spreadsheets are rarely restricted from unauthorized access by security...

Words: 949 - Pages: 4

Premium Essay

...Relative vs. Absolute Cell References in Spreadsheets In working with spreadsheets, you need to know about relative vs. absolute cell references. Here is the issue: when you COPY A FORMULA that contains cell references, what happens to the cell references? Usually the CELL REFERENCES will CHANGE! If you copy a formula 2 rows to the right, then the cell references in the formula will shift 2 cells to the right. If you copy a formula 3 rows down and 1 row left, then the cell references in the formula will shift 3 rows down and 1 row left. These are called "relative" cell references, since they change relative to where you copy the formula. If you do not want cell references to change when you copy a formula, then make those cell references absolute cell references. Place a "$" before the column letter if you want that to always stay the same. Place a "$" before a row number if you want that to always stay the same. For example, "$C$3" refers to cell C3, and "$C$3" will work exactly the same as "C3", expect when you copy the formula. Note: when entering formulas you can use the F4 key right after entering a cell reference to toggle among the different relative/absolute versions of that cell address. The trick in creating spreadsheets is deciding before you copy a formula what cell references in the formula you want to be relative and what you want to be absolute. If some cell references refer to input cells in the spreadsheet, you usually want those cells to be absolute. The...

Words: 849 - Pages: 4

Premium Essay

...Database program Vs. Spreadsheet program When choosing which type of program you should use to manage your customer records, you need to know what the difference is between the two. Spreadsheets are designed to analyze data and sort list items, not for long-term storage of raw data. A spreadsheet should be used for ‘crunching’ numbers and storage of single list items. Spreadsheet programs provide the means for keeping inventory, statistical data modeling, and computing data. They also include graphing functions that allow for quick reporting and analysis of data. Spreadsheet programs are relatively easy to use, require little training to get started, and have the advantage that most data mangers are somewhat familiar with them. The disadvantages of spreadsheets include having to re-copy data over and over again to maintain it in separate data files, the inability to efficiently identify data errors, the lack of detailed sorting and querying abilities, and sharing violations among users wishing to view or change data at the same time. Additionally, spreadsheets are restricted to a finite number of records, and can require a large amount of hard-drive space for data storage. Databases require little or no duplication of data between information tables, and changes made to the data do not corrupt the programming. Databases offer better security to restrict users from accessing privileged information, and from changing coded information in the programming. Furthermore, the two...

Words: 415 - Pages: 2

Free Essay

...Cyrus Brown Manufacturing (CBM) Sales Budget March April May June July August September October November $100,000 $275,000 $320,000 $450,000 $700,000 $700,000 $825,000 $500,000 $115,000 Cyrus Brown Manufacturing (CBM) Schedule of Cash Collections March April May June July August September October November Month of Sale (25%) $25,000 $68,750 $80,000 $112,500 $175,000 $175,000 $206,250 $125,000 $28,750 Following Month (55%) $55,000 $151,250 $176,000 $247,500 $385,000 $385,000 $453,750 $275,000 Second Month (20%) $20,000 $55,000 $64,000 $90,000 $140,000 $140,000 $165,000 Total Collections $25,000 $123,750 $251,250 $343,500 $486,500 $650,000 $731,250 $718,750 $468,750 Cyrus Brown Manufacturing (CBM) Schedule of Cash Disbursements March April May June July August September October November Direct Manufacturing Costs $187,500 $206,250 $375,000 $337,500 $431,250 $640,000 $395,000 $425,000 Administrative Salaries $35,000 $35,000 $35,000 $35,000 $35,000 $35,000 $35,000 $35,000 $35,000 Lease Payments $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 Depreciation Charges $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 New Plant Investment $95,000 Income Tax $55,000 $55,000 Miscellaneous Costs $10,000 $10,000 $10,000 $10,000 $10,000 $10,000...

Words: 397 - Pages: 2

Premium Essay

...the attached spreadsheet after the data was analyzed and dropped into the Excel Spreadsheet and the Data Solver was utilized to obtain the stated highlighted results. The cost is $2,822 to ship. 2. Solve the model given in 1 and describe the results. The results of the model are as follows: 35 barrels per week from Kingsport Plant to Whitewater Waste Plant 26 barrels per week from Danville Plant to Duras Waste Plant 42 barrels per week from Macon Plant to Duras Waste Plant 1 barrel per week from Selma Plant to Whitewater Waste Plant 52 barrels per week from Selma Plant to Los Canos Waste Plant 29 barrels per week from Columbus Plant to Whitewater Waste Plant 28 barrels per week from Allentown Plant to Los Canos Waste Plant 10 barrels per week from Allentown Plant to Duras Waste Plant 3. Develop a transshipment model in which each of the plants and disposal sites can be used as intermediate points. Please see tab two (Intermediate Shipping) of the attached spreadsheet after the data was analyzed and dropped into the Excel Spreadsheet and the Data Solver was utilized to obtain the stated highlighted results. 4. Solve the model given in 3 and describe the results. The results of the model are shown on Row 4 and details the quantity of barrels that can be shipped per week to the intermediate points and then to the destined Waste Plants as highlighted in the spreadsheet. The cost is...

Words: 328 - Pages: 2

Free Essay

...Consulting Assessment HRM/326 Amanda L Conley August 17, 2015 Rick Lab Consulting Assessment The company that I am going to discuss is called Professor Plumb. This is a company that I have recently began working for and it is a well-known plumbing company that is also a sub-contractor for two large home improvement stores. The bulk of the company business is the work obtained from the sub-contracts, in performing installs of plumbing and appliance needs. There are two large markets in the Alabama area, there is the Birmingham region and the Montgomery region. We have a total of five technicians that are scheduled to take care of the servicing needs of the customers. The one thing that the company makes the most money on is going to be the installation and sales of hot water heaters, and therefore those calls take priority. The Birmingham region is the most recent acquired by the company owner, and there are not as many stores and business in this area. We as a company are focusing on the growth in sales of the hot water heaters in both regions and the building of more jobs in the Birmingham region. When looking at the focus and the needs of the company it is very important to ensure that the technicians are well trained and know and understand what is needed and expected of them. The company is struggling at this time with the acquisition of the new region and trying to make money and provide the funds for the overhead and more. We as a company have been trying...

Words: 819 - Pages: 4

Premium Essay

...SPREADSHEET MODELING IN CORPORATE FINANCE To accompany Principles of Corporate Finance by Brealey and Myers CRAIG W. HOLDEN Richard G. Brinkman Faculty Fellow and Associate Professor Kelley School of Business Indiana University Prentice Hall, Upper Saddle River, New Jersey 07458 To Kathryn, you’re the inspiration, and to Diana and Jimmy, with joy and pride. Craig CONTENTS Preface PART 1 TIME VALUE OF MONEY Chapter 1 Single Cash Flow 1.1 Present Value 1.2 Future Value Problems Chapter 2 Annuity 2.1 Present Value 2.2 Future Value 2.3 System of Four Annuity Variables Problems Chapter 3 Net Present Value 3.1 Constant Discount Rate 3.2 General Discount Rate Problems Chapter 4 Real and Inflation 4.1 Constant Discount Rate 4.2 General Discount Rate Problems Chapter 5 Loan Amortization 5.1 Basics 5.2 Sensitivity Analysis Problems PART 2 VALUATION Chapter 6 Bond Valuation 6.1 Basics 6.2 By Yield To Maturity 6.3 System Of Five Bond Variables 6.4 Dynamic Chart Problems Chapter 7 Stock Valuation 7.1 Two Stage 7.2 Dynamic Chart Problems Chapter 8 The Yield Curve 8.1 Obtaining It From Bond Listings 8.2 Using It To Price A Coupon Bond 8.3 Using It To Determine Forward Rates Problems Chapter 9 U.S. Yield Curve Dynamics 9.1 Dynamic Chart Problems PART 3 CAPITAL BUDGETING Chapter 10 Project NPV 10.1 Basics 10.2 Forecasting Cash Flows 10.3 Working Capital 10.4 Sensitivity Analysis Problems Chapter 11 Cost-Reducing Project 11...

Words: 49278 - Pages: 198

Premium Essay

...Business Spreadsheets. Name: Course: Instructor: Institution: Date of submission: 1) Calculate X-Bar-Bar, R-Bar, and associated control limits using the data in the table above. From the data we have, the X-Bar-Bar number can be seen at the top set of results titled Centre x-bar bar = 5.1 a. The R-Bar result is the “Center” result the bottom set of data is R-Bar. Therefore R-Bar = 1.083 2) Create X-Bar•R (Average & Range) Control Charts from the data in the table above. The X-Bar Control Chart is shown below Control limits for the x - chart are: Control limits = x- bar ± A2 (MR) Control limits = 5.1 ± 0.729 *1.083 Control limits = 4.31 to 5.89 Control limits ranges between 4.31 to 5.89 The R Control Chart is shown below Control limits for R- chart are: Upper control limits = (MR) * D4 Upper control limits = 2.282 *1.083 Upper control limits = 2.47 Lower control limit = D3 * MR Lower control limit = 0 * 1.083 Lower control limit = 0 3) Discuss notable out-of-control conditions displayed in the completed X-Bar•R (Average & Range) Control Charts. We consider points outside the control limits. We do not consider runs, set of points within certain regions. From the X-Bar control chart, we can see that two points 19 and 21 are above the upper control limit. From the R-Chart we can see that one point, 18 is above the upper control limit. 4. If...

Words: 890 - Pages: 4

Premium Essay

...Word Count: 587 This is my own unaided work . SPREADSHEET mODELLING Word Count: 587 . SPREADSHEET mODELLING Spreadsheet modelling As a future financial analyst my work will be based on analysing data from company’s gathered data spreadsheets. Hence it is crucial for my own career success to understand the importance of spreadsheet modelling and its implementation. As according to (Susan Coles, Jennifer Rowley, 1996) “The decision maker’s judgement must be exercised in the interpretation of the data and the final decision making.” There is no better way of benefiting my future career than developing advanced skills in modelling Excel. As I have learned from few articles I read about this subject, there are many advantages of excelling my skills so as problems I should look out for. Firstly, studying advanced Excel modelling not only helps students to build technological knowledge but “also largely improve their practical analytical skills and abilities.”(Junying, 2010). By studying how to model spreadsheets students get a real chance to see how actual data is analysed by spreadsheet build in models and help them to further their own analytical knowledge and see how it works in real life. I think it is hugely beneficial for students in their further real...

Words: 715 - Pages: 3

Premium Essay

...[pic][pic] FIN 222 Corporate Finance Individual Major Assignment [80 marks] This manual outlines the followings. Objective of the assignment Details of questions (Instructions will be provided wherever necessary.) Format requirements Printing requirements Submission requirements ➢ Objective of the Assignment In Lecture 5 (CH11 and CH12), you were introduced to the concept of systematic risk, as measured by beta. Beta is an indicator of an asset’s exposure (or vulnerability) to systematic risk and evaluates the degree to which an asset’s return is correlated with the market return. Beta is a key parameter in the Capital Asset Pricing Model (CAPM) which is most commonly used asset pricing model for the estimation of required rate of returns on a share (i.e. cost of equity=rE). This assignment consists of two parts. Part I directs you to work with real financial data in Excel and aims to take you through the basic exercise of estimating a company beta and computing statistics which can be evaluated as part of investment decision-making process. Part II requires you to write a report based on interpretations of the outputs obtained from the first part. In this manual, you will also find out how to download historical share price, market index data and interest rates from the web portal. ➢ Details of Questions PART 1 [30 marks] To successfully complete PART 1, please read each section carefully. You must read “Format and printing requirement” before you...

Words: 3262 - Pages: 14

Premium Essay

...Implementing an LP Model in a Spreadsheet: 1. 2. 3. 4. Organize the data for the model on the spreadsheet. Reserve separate cells in the spreadsheet for each decision variable in the model. Create a formula in a cell in the spreadsheet that corresponds to the objective function. For each constraint, create a formula in a separate cell in the spreadsheet that corresponds to the left-hand side (LHS) of the constraint. PANKAJ DUTTA IMTCDL Chapter – 11 Linear Programming LPP solution through Excel Solver: Max Z = 350X1 + 300X2 Subject To 1X1 + 1X2 ≤ 200 9X1 + 6X2 ≤ 1566 12X1+16X2 ≤ 2880 X1 , X 2 ≥ 0 1. Organize the data for the model on the spreadsheet. PANKAJ DUTTA IMTCDL Chapter – 11 Linear Programming LPP solution through Excel Solver: Max Z = 350X1 + 300X2 Subject To 1X1 + 1X2 ≤ 200 9X1 + 6X2 ≤ 1566 12X1+16X2 ≤ 2880 X1 , X 2 ≥ 0 1. Organize the data for the model on the spreadsheet. Changing cells Target cell PANKAJ DUTTA IMTCDL Constraint cells Chapter – 11 Linear Programming Max Z = 350X1 + 300X2 Subject To 1X1 + 1X2 ≤ 200 9X1 + 6X2 ≤ 1566 12X1+16X2 ≤ 2880 X1 , X 2 ≥ 0 LPP solution through Excel Solver: 1. Organize the data for the model on the spreadsheet. – Target cell - the cell in the spreadsheet that represents the objective function – Changing cells - the cells in the spreadsheet representing the decision variables – Constraint cells - the cells in the spreadsheet representing the LHS formulas...

Words: 798 - Pages: 4

Premium Essay

..." Using Spreadsheets and Graphing • in Excel In this case the independent quantity is Concentration and the dependent variable is Signal (we know the concentration, but we have to measure the signal that it causes). This means that Concentration should be on the X-axis and Signal should be on the Y-axis. In Excel, the X-axis values need to be to the left of the Y-axis values. Procedure 1: Basic Spreadsheet Entry 1. Open MS Excel on your computer. It will help to maximize the window so it fills up the screen. Your instructor will lead you through the steps in the following exercise. Follow along on the instructions as well as listening closely to your instructor and following directions. Computer programs are not very forgiving. If you are sloppy or make mistakes, your results will have· errors. You really have to pay attention to detail. 2. qreate a data table by copying the data from the Example on the previous page into the spreadsheet. t'JJ The first issue you have to deal with is the heading "Concentration (mol/L)" is too large to fit in • j cell AI. This must be fixed. You can either increase the column width or wrap text. l'j The second issue is that by default, data in the spreadsheet is not "centered" in the cells as shown in the figure. This must also be fixed. Follow the instructions of your instructor. o Notes: • 3. initial here: • 0 Your spreadsheet should look exactly like that in the example, including capitalization...

Words: 1308 - Pages: 6

Premium Essay

...payables, and receivables, and other accounting functions that we need to have interoffice transaction done between our other office in Philadelphia, Washington D.C.; New Castle, DE; Baltimore, MD; and Bordentown, NJ. The other information system that we use is Microsoft Excel we use it for all spreadsheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations like arithmetic operations. It has a battery of supplied functions to answer statistical, engineering and financial needs. In addition, it can display data as line graphs, histograms and charts, and with a very limited three-dimensional graphical display. It allows sectioning of data to view its dependencies on various factors for different perspectives (using pivot tables and the scenario manager). It has a programming aspect, Visual Basic for Applications, allowing the us to employ a wide variety of numerical methods, for example, for solving differential equations of mathematical physics, and then reporting the results back to the spreadsheet. It also has a variety of interactive features allowing user interfaces that can completely hide the spreadsheet from the user, so the spreadsheet presents itself as a so-called application, or decision support system (DSS), via a custom-designed user interface, for example, a...

Words: 325 - Pages: 2