個案總覽

Excel spreadsheet for instructors.

Whenever an investment is made, there are always costs and benefits. This case set in 2013 explores, based on quantitative and qualitative factors, a decision that a typical student at university might face. The decision to rent a house or purchase one in order to rent out the additional rooms is a difficult one. All of the pros and cons of each option need to be carefully considered. An Excel spreadsheet is available for students (see 7B14E013).

Sinofert Holdings Limited, the largest comprehensive fertilizer enterprise in China, is trying to improve the profitability of its urea business. The company has invested a great deal of time and money but still reported losses in 2007 and 2009 and only a small profit in 2008. Sinofert both manufactures urea and purchases it from external suppliers, as well as distributing it to the provinces. Manufacturing costs, transportation costs, market prices, demand forecasts and manufacturing constraints are all known. An optimal distribution plan using linear programming can be compared to the plan derived by Sinofert management. Substantial profitability increases are shown to be possible, although the optimization reveals some issues with contract constraints. If the company is to make its urea business profitable, it needs a fresh look and a change in the way of doing business. The company’s chief analytics officer has been asked to look at the urea business and to provide recommendations to increase profitability.

This note is an introduction to using Analysis ToolPak, a Microsoft Excel add-in program. The note describes most of the tools available in Analysis ToolPak, illustrates the steps through screenshots and provides examples of the program’s utility in Excel and Visual Basic for Applications (VBA). The note also shows how to automate use of these tools with VBA. The tools explained in the note are ANOVA, correlation, descriptive statistics, exponential smoothing, F-test two-sample for variances, Fourier analysis, random numbers, histogram, moving average, random number generation, rank and percentile, regression, sampling, t-test and z-test.

A loop is used in Microsoft Excel to repeat a piece of code, while an array is a group of variables with the same data type and name. This note is an introduction to different types of loops and their use with arrays in Excel Visual Basic for Applications (VBA), with brief definitions and examples. Loops types included are for-next, for-each, do while (do until), do-loop (with while and until), and nested loops. For arrays, the note presents static and dynamic arrays with examples.

A sub, function, or event procedure is a set of commands to perform a task in a Visual Basic for Applications (VBA) code. One important reason for subs and functions is the reusability of VBA code. For example, if an Excel VBA application requires a calculation to be performed multiple times in its code, the user can enter a reference to a sub or function, rather than rewriting the same code for each calculation.<br><br>This note is an introduction to sub, functions, and event procedures in Excel VBA with brief definitions, examples, and screenshots. The note demonstrates how to use them in a variety of settings.

Excel spreadsheet for students, for use with 9B11E013.

Excel spreadsheet for instructors, for use with 8B11E013.

Excel spreadsheet for instructors, for use with 8B11E014.

Excel spreadsheet for instructors - outputs. For use with 8B11E014.

Excel spreadsheet for students, for use with 9B11E014.

Visual Basic for Applications (VBA) is a programming language built into Microsoft Office and other applications. In Microsoft Excel, VBA can be used to develop models, simplify repetitive tasks, and increase user-friendliness. This note is designed to provide students with a basic understanding of the building blocks of VBA programming. It is intended as an introductory reading for students with no prior experience in VBA programming.

This note is an introduction to simulation in Excel and VBA. The note uses examples to demonstrate different methods of doing simulation in Excel and VBA (Visual Basic for Applications). Concepts explained in this note include random number generation, probability distributions, data tables, loops and arrays, histograms, and descriptive statistics.

This note is an introduction to Solver in Excel and Visual Basic for Applications (VBA). Solver is a Microsoft Excel tool that determines variables and objective function values of linear, integer, binary, and non-linear optimization models. These models are used in insurance, finance, operations, logistics, engineering, and medicine. This note uses screenshots to help demonstrate various examples of the use of Solver, including assembling a portfolio of ideal stocks and assigning the location of fire stations in cities.

Microsoft Excel offers a suite of graphical user interface (GUI) tools that can be used with Microsoft’s Visual Basic for Applications (VBA) code. These tools enable the creation of programs with a customized appearance and function. Thus, highly complex models can be made simple to use.<br><br>This note, using explanations and examples with screenshots, is designed to give readers an overview of the available GUI and debugging tools within Excel VBA. It is intended for students who already have experience with basic VBA programming.

Variables are used to keep changeable information or values such as text, dates, and numbers used in a macro or Visual Basic Application (VBA) code. A logical statement is a collection of Boolean-type expressions that can be either true or false, and logical operators are used in Excel VBA to connect logical statements. <br><br>This note is an introduction to variables and logical statements in Excel VBA with brief definitions and examples. It includes different data types for variable declaration, logical statements, logical operators, if-then statements, and case structures.

This note is designed to give readers an overview of some useful tools within Microsoft Excel and Visual Basic for Applications (VBA), including offset, vlookup, dynamic charts, form controls, and pivot tables. These tools have purposes such as eliminating errors in data entry or extracting insights from large sets of categorical data. This note contains explanations followed by examples and screenshots for each tool.

The web query feature of Microsoft Excel can be a great help in retrieving data from the web. A web query can be set up in Excel in a similar way to browsing a web page. In this note, historical stock prices are obtained from the web for use in Microsoft Excel, and a macro recorder is used to process the data and view and modify the corresponding Visual Basic for Applications (VBA) code.

An Excel macro is a series of commands that can perform certain tasks automatically, such as obtaining data from another Excel file or the web, updating computations, generating reports, and running models. Macros can save time, reduce user errors, and complete tasks that would be extremely difficult for a user to do otherwise. This note uses examples and screenshots to introduce macros and macro recording in Microsoft Excel.

A large financial institution has acquired a sizable portfolio of new clients of travel (corporate expense) cards. The bank must decide on the optimal mix of clients to retain in order to achieve the goals of maximizing profitability, entering a new product market successfully, and maintaining reputation. The optimal mix depends on a number of different factors, including annual account spending level, complexity of serving the account, the number of cards in each account, account risk, and account retention level. The selection and number of clients chosen will affect the bank’s future profitability and long-term strategy. The bank is limited by attempting to achieve a three-year payback, and facing costs that can vary significantly and are not in the bank’s control.