個案總覽

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.

The general manager of Craig Manufacturing Cambridge Branch felt that there was room to improve top-line growth through better utilization of plant capacity. The company was losing out on sales due to the highly seasonal nature of demand; the plant was fully loaded four months of the year, but it had unused capacity during the remaining months. The general manager had just attended a lecture where a more flexible approach to pricing had been suggested as a way to better manage supply chain and capacity issues. An idea began to emerge: Could Craig Manufacturing use pricing to better match demand to plant capacity? If so, would this practice boost profitability, or would it merely reduce revenues?

This case deals with the commercialization challenges that a premier research institute in an emerging economy faces despite conducting cutting-edge research. The case is set in 2009 in Hyderabad, India, soon after the appointment of CCMB’s new director, Dr. Mohan Rao. Rao has to decide how to proceed with incentivizing the scientists to find potential applications for their research when most scientists are merely interested in doing basic research and when, at the same time, the government is increasing its emphasis on the use of public science for societal good. The case also deals with the role of commercialization and technology transfer, publishing versus patenting, basic versus translation sciences, and incentives.

At the University of Wyoming, home games played by the men’s basketball team generated significant revenues for the athletics department through ticket and concession sales. With the 2009-2010 season ending, it was time to forecast revenues for the upcoming season. Even though ticket prices were already set, providing a revenue forecast was difficult, as the schedule of home games for the 2010-2011 season was not yet known, and both ticket and concession sales for each game were uncertain. A director of business operations for the athletics department needed to review data from the four most recent seasons and determine the best way to forecast revenues for the upcoming season.

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.

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.

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.

A student is trying to incorporate various analytical tools to assist in selecting National Hockey League (NHL) players for a fantasy hockey pool, using individual player data from the 2006-2009 NHL seasons. The student must come up with a strategy in which 16 players are selected for the 2010 season.

In this supplement to Student Plays Fantasy Hockey (A), the student is faced with an additional constraint regarding the total salary of the players selected. The student must come up with a strategy to select 16 players and not exceed $45 million in total salaries.

The president of Oakville Hydro Energy services needed to make a decision on the installation of an electricity-generating engine at the regional municipal Wastewater Treatment Plant in Burlington, Ontario. This engine installation was in support of the production of green power, and the Ontario Power Authority (OPA) had introduced a feed-in tariff that paid a premium for the generation of this type of green power. One of three specific engines would be purchased and installed to generate electricity from the combustible gases produced during wastewater treatment. The president had to decide which engine generator would produce the most favourable output and the best return for Oakville Hydro and the municipality. The president also needed to decide whether or not to integrate the existing on-site gas storage tank into the controls of the generator system.

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.

Yangarra Resources Limited, a Calgary-based junior oil and gas company, had several properties throughout Alberta comprising both solely controlled and joint ventures. The president and chief executive officer (CEO) was involved in the construction of a well on the Ferrier property — a joint venture between three companies, each holding roughly one-third of the stake. As part of the joint venture, Yangarra had signed an agreement that committed Yangarra to cover all expenses proportional to its working stake of 31.875 per cent. The well had been drilled at a higher cost than expected, with many charges not yet recorded or incurred. As a result of the cost overruns, Yangarra had been asked to provide more funding to the project. In deciding whether to commit additional resources to the Ferrier well, the CEO had to consider several factors including an existing gross overriding royalty revenue (GORR) agreement, uncertainty in estimating the recoverable quantity of oil, crown royalties, and a current legal dispute.

The China national image film “People Chapter” — officially a sub-series of the “Experience China” campaign — was launched by the Chinese government to coincide with President Hu Jintao’s visit to the United States in mid-January 2011. The one-minute promotional video was played on six giant electronic screens about 300 times per day, and had appeared approximately 8,400 times when the broadcast ended on February 14, 2011. The video showed a series of Chinese people, ranging from ordinary citizens to celebrities. It was a publicity effort aimed at promoting a truer image of China abroad, and signalling that China was opening to embrace the world. However, reactions from both Chinese and overseas audiences had been fairly mixed since the initial release of the promotional film. Experts from China and abroad were skeptical of the effectiveness of the campaign in promoting the national image of modern China to the world. This case presents the opportunity to examine the basic elements in the marketing communication process, analyze how decisions in marketing design affect outcomes, and understand the differences between nation and product promotion.

The chief credit officer (CCO) at Seoul National Bank has recently faced numerous defaults on its corporate loans. To address this problem, the CCO must re-evaluate how the bank responds to defaults and determine a default strategy. The CCO must evaluate the payouts and risk profiles of 1) collecting on collateral, 2) partially settling, and 3) waiting and seeing. Further, the CCO must determine a systematic way to approve loans from companies. Analyzing both qualitative and quantities factors, the CCO must determine which companies to approve for loans given a certain size of its loan portfolio. Also, the CCO must determine whether or not to increase or shrink the size of the bank’s loan portfolio based on the expected outcomes and/or simulated results.

An equity analyst at Nova Funds, an investment firm, is in the process of analyzing an investment in SIGA Technologies (SIGA), and potentially its competitor, PharmAthene Inc. (PIP). SIGA is currently the only company in the world that has developed a treatment for smallpox, with its ST-246. In late 2010, the U.S. Department of Health and Human Services (HHS) announced the intention to award SIGA a contract valued at up to $2.8 billion, which significantly exceeds SIGA’s current market value, making the company potentially an excellent investment. However, SIGA is prevented from realizing the profit on this contract due to two uncertainties. First, PIP, a partner throughout the development of the ST-246, has sued SIGA over the rights of the ST-246, with the payout of the lawsuit unknown. Second, since the HHS announcement is for the intention to award the contract, the actual contract for SIGA may take on various possibilities. Given these uncertainties, the equity analyst needs to find ways to invest in the situations that produce the most attractive outcome. She may potentially invest by buying or shorting the stocks of the companies involved or through the use of options. Moreover, she may do this over multiple timeframes, if that is justified.

The executive director of British Columbia’s Neonatal Services Program was thinking about her last meeting with the officials at the British Columbia Ministry of Health Services (MHS). Following a report indicating that from 2007 to 2008 the province sent 98 expectant mothers and newborns to the United States for treatment, the province was considering adding more neonatal intensive care capacity. Political pressure was mounting after one of Canada’s national newspapers covered the problem in a front-page story. The MHS asked the executive director to suggest bed allocations and other improvements to address the situation.

A market research company has collected a large dataset on brand equity for the fast food and travel sectors. It has come up with a way of measuring brand equity. Various statistical techniques are used to assess the concept of brand equity. These include descriptive statistics, crosstabs, ANOVA, and MANOVA.

The head of Patient Escort Services for Vancouver General Hospital (VGH) had to determine the best approach to improve efficiency of the hospital's porter system. Porters represented a vital part of various hospital processes, facilitating timely flow of patients, equipment and materials throughout the hospital. Recently, VGH had experienced incidents in which the capacity of the porters was insufficient to meet the demands of the hospital and, despite best-efforts at scheduling, these problems remained. The head of Patient Escort Services spent several weeks collecting data to determine where the problems existed, and wondered if his data could provide answers to some critical questions: How well did the current porter schedule meet daily demand? What is the optimum number of porters? The head of Patient Escort Services had one week to submit his report to VGH's board of directors.

Mustang Music (A) and (B) cases introduces students to data modeling, database implementation and database querying.