Excel 4 Ur Business

Excel is Microsoft’s spreadsheet program. It is not a home finance program, so why am I using it in this website? Excel is a very flexible program that has applications in many different areas, and one of them is finance. Excel might not be designed specifically for personal finance, but it certainly has the tools you need. Let’s take a look at the fundamentals of Excel. If you already have some familiarity with Excel, you might want to skim or skip this post.

Read the rest of this post »

Few people can make it though life without taking out one or more loans. By extending payments over a period of time, a loan enables you to buy something now that otherwise you would have to save for over a long time. Big ticket items, such as a car or house, are perhaps the most common uses of loans.

Read the rest of this post »

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?\

Read the rest of this post »

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:

Read the rest of this post »

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.

Read the rest of this post »

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?

Read the rest of this post »

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?

Read the rest of this post »

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?

Read the rest of this post »

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.

Read the rest of this post »

Overview

Suppose I have a list of songs that are played on the radio. For each song, I know the singer, the date the song was played, and the length of the song. How can I answer questions such as

• How many songs were sung by each singer?
• How many songs were not sung by Eminem?
• How many songs lasted at least 4 minutes?
• How many songs were longer than average?
• How many songs were sung by a singer whose last name begins with S?
• How many songs were sung by a singer whose last name contains six letters?
• How many songs were sung after June 15, 2005?
• How many songs were sung before the beginning of 2009?
• How many songs lasted exactly 4 minutes?

Read the rest of this post »