Excel 4 Ur Business

Overview

• How can a drug company determine the locations at which they should produce drugs and from which they should ship drugs to customers?

Many companies manufacture products at locations (often called supply points) and ship their products to customers (often called demand points). A natural question is what is the least expensive way to produce and ship products to customers and meet customer demand? This type of problem is called a transportation problem. A transportation problem can be set up as a linear Solver model with the following specifications:

• Target cell Minimize total production and shipping cost.
• Changing cells The amount produced at each supply point that is shipped to each demand point.
• Constraints The amount shipped from each supply point can’t exceed plant capacity. Each demand point must receive its required demand. Also, each changing cell must be nonnegative.

How can a drug company determine the locations at which they should produce drugs and from which they should ship drugs to customers?\

(more…)

Overview

• How can I determine the monthly product mix for our plant that maximizes corporate profitability?
• Does a Solver model always have a solution?
• What does it mean if a Solver model yields the result Set Values Do

Not Converge?

How can I determine the monthly product mix for our plant that maximizes corporate profitability?

Companies often need to determine the monthly (or weekly) production schedule that gives the quantity of each product that must be produced. In its simplest incarnation, the product mix problem involves how to determine the amount of each product that should be produced during a month to maximize profits. Product mix must often satisfy the following constraints:

(more…)

Overview

• How can a large drug company determine the monthly product mix at their Indianapolis plant that maximizes corporate profitability?
• If Microsoft produces Xbox consoles at three locations, how can they minimize the cost of meeting demand for Xbox consoles?
• What price for Xbox consoles and games will maximize Microsoft’s profit from Xbox sales?
• Microsoft would like to undertake 20 strategic initiatives that will tie up money and skilled programmers for the next five years. They do not have enough resources to undertake all 20 projects. Which projects should they undertake?
• How do bookmakers find the best set of ‘ratings’ for NFL teams to set accurate point spreads?
• How should I allocate my retirement portfolio among high-tech stocks, value stocks, bonds, cash, and gold?

In all these situations, we want to find the best way to do something. More formally, we want to find the values of certain cells in a spreadsheet that optimize (maximize or minimize) a certain objective. The Excel Solver helps you answer optimization problems.

(more…)

Overview

• How can I highlight monthly stock returns so that every good month is highlighted in one color and every bad month is highlighted in another?
• Given quarterly corporate revenues, how can I highlight quarters in which revenues increased over the previous quarter in one color and quarters in which revenues decreased from the previous quarter in another?
• Given a list of dates, how can I highlight the weekend dates in a specific color?
• Our basketball coach has given each player a rating between 1 and 10 for her ability to play guard, forward, or center. Can I set up a spreadsheet that highlights the ability of each player to play the position to which she’s assigned?

(more…)

Overview

• My spreadsheet formulas often contain references to cells or ranges or both. Rather than change these references in my formulas, I’d like to know how I can place the references in their own cells so that I can easily change my cell or range references without changing my underlying formulas.
• Each worksheet in a workbook lists monthly sales of a product in cell D1. Is there an easy way to write and copy a formula that lists each month’s product sales in a single worksheet?
• Suppose I’m adding up the values in the range A5:A10 with the formula SUM(A5:A10). If I insert a blank row somewhere between row 5 and row 10, my formula updates itself to read SUM(A5:A11). Can I write a formula so that when I insert a blank row between row 5 and row 10, my formula still adds the values in the range A5:A10?

(more…)

Overview

• How can I create a reference to a rectangular range of cells that is a specified number of rows and columns from a cell or another range of cells?
• How can I perform a lookup operation by keying off the right-most column instead of the left-most column in a table range?
• I often download sales of a software product listed by country. I need to track revenues from Iran as well as costs and units sold, but the data about Iran isn’t always in the same location in the spreadsheet. Can I create a formula that will always pick up Iran’s revenues, costs, and units sold?
• Each drug developed by my company goes through three stages of development. I have a list of the cost by month for each drug, and I also know how many months each development stage took for each drug. Can I create formulas that compute for each drug the total cost incurred during each stage of development?
• I run a small video store. In a spreadsheet, my accountant has listed the name of each movie in stock and the number of copies in stock. Unfortunately, for each movie this information is in a single cell. How can I extract the number of copies of each movie in stock to a separate cell?
• I am charting my company’s monthly unit sales. Each month I download the most recent month’s unit sales. I would like my chart to automatically update. Is there an easy way to accomplish this?

(more…)

Overview

• I’m a sales manager for a makeup company and have summarized for each sales transaction the following information: salesperson, date of sale, units sold (or returned), total price received (or paid out for returns). How can I answer the following questions?
  • What was the total dollar amount of merchandise sold by each salesperson?
  • How many units were returned?
  • What was the total dollar volume sold in 2005 or later?
  • How many units of lip gloss were sold?
  • How much revenue did lip gloss sales bring in?
  • What dollar amount of sales were not made by Jen?

If you want to sum all the entries in one column (or row) that match criteria-that depend on another column (or row), the SUMIF function gets the job done. The syntax of the SUMIF function is SUMIF(range, criteria, [sum range]).

• Range is the range of cells that you want to evaluate with a criterion.
• Criteria is a number, date, or expression that determines whether a given cell in the sum range is added.
• Sum range is the range of cells that are added. If sum range is omitted, it is assumed to be the same as range.

The rules for criteria you can use with the SUMIF function are identical to the rules used with the COUNTIF function. For information about the COUNTIF function, see post The COUNTIF, COUNT, COUNTA, and COUNTBLANK Functions.

(more…)