Excel Busn Math 46: Cash Discounts

Properties increase by 10% of the original price with each new purchase.

Current Price:

Code:

F37*(1+E37/10)

Properties are built on a required property. If the required property has not been purchased, the Current Price is:

Code:

IF(metropolis<2,VLOOKUP(metropolis,shipping_metropolis,2,FALSE)+F37*(1+E37/10),F37*(1+E37/10))

If I sell this property, the Cash Received:

Code:

E37*(G37+F37*0.9)/4

However, it's incorrect if the required property is "not owned" in the spreadsheet. I need help figuring out the formula to exclude the price of the required property (the VLOOKUP) for the Cash Received.

Hi,

I'm new to excel and have been asked to model a rather complex investment structure. If anyone could help me with this I would be forever grateful. The cash flows are distributed between the limited partner (LP) and the managing partner (MP). There are three hurdles though with different divisions of cash for each. The structure is as follows

For cash flows generating an IRR less than 15%
LP gets 75%
MP gets 25%

For cash flows generating between IRR 15%-20%
LP gets 50%
MP gets 50%

For Revenue reaching over 20%
LP gets 25%
MP gets 75%

So I have my NPV cash flows laid out and have calculated the IRR for each year but can't visualize how to calculate the relevant sums of money for each cash flow. For example, the amount of cash that generates a 15% IRR in year 3 will be different than the amount of revenue that generates a 15% IRR in year 4.

So say there is cash flow for a year that generates a 30% IRR. I would need to first split the cash into three separate sums. The first would be the cash that generates an IRR of up to 15%, then another sum of the cash on top of that that generates between 15-20% IRR, then all the cash over 20%. The sum of these three chunks will equal the total cash flow for given year. Then I can apply the relevant structure to each cash sum (LP gets 75% of first sum, 50% of second sum, and 25% of third sum)

I need to split up the cash flows into the three different groups based on the hurdles so I can apply the correct structure to the relevant part of the cash flow.

Can I get excel to spit out the relevant sums of money for each hurdle level for each year?

Sorry if this is not clear, I am completely new to this and will elaborate if need be.

Thanks,

Colin

Hi I'm very new to this & hoping you can help ? I have supplier price lists which have 1310 rows of stocklines & 4 columns. Column A is Part numbers, Column B is part description, & column C is list price exclusive of gst & column D is price including gst.
How do we add a prefix to to each part no. in each cell from A1 thru to A1310 eg. pool0001,pool0002,pool0003.
How do I in column c apply a trade discount to each cell individually eg -62% for c1 thru to c1310 to the end results is my net cost per part no.
thanks hope you can help me.
Mark

How can I calculate cash on cash return on an investment (how many months it takes to recoup a cash investment) from a stream of income payments? The problem is that the calculation is part of a model, so investment amounts are not static. How can I make my cash-on-cash calculation vary based on different investment amounts?

Thanks in advance

I am having trouble figuring what function (calculation) to use.

First, an overview of why I need this. I run fun promotions in schools. When a child sells widgets in a fundraiser, I let them draw cash out of a cash bag as a reward. Here are the exact instructions to the students...

For every 3 widgets you sell in our fundraiser, I will let you draw 1 bill out of a cash bag. The cash bag will be filled with $1's, $5's, $10's and $20's. Example: If you sell 9 widgets, you get THREE pulls out of the cash bag (one bill per draw).

Given the TOTAL NUMER OF DRAWS to be done within a school, I want an Excel formula (or calculation) to tell me how many $1's, $5's, etc to put into the cash bag.

As a general rule I want more lower denominations (such as $1's) and fewer high denominations (such as $20's).

I want to spend an exact predetermined dollar amount TOTAL on this promotion.

For example:

GIVEN: I need 891 draws at a school.
GIVEN: I want to spend $2,219 total.

How many $20 bills should I put in the bag?
How many $10 bills should I put in the bag?
How many $5 bills should I put in the bag?
How many $1 bills should I put in the bag?

I know that there could be MANY answers to this question, depending on exactly how many of each denomination I wish- but I don't really care about the exact amount. I am content to let Excel determine that for me as long as there are MORE lower denominations (such as $1's) and FEWER high denominations (such as $20's).

My brain just isn't big enough to make Excel work for me on this. Is it even possible?

I have a deposit worksheet we use every day to count our deposits and balance our drawer. I am looking for a way to have excel calculate how many bills and coins of each currency need to go into our deposit based on how many bills and coins of each we had in our drawer. We start off with $100 in our drawer, so when we make our deposit we subtract $100 from the total drawer count to keep in the drawer (keeping the smallest denominations possible).

I have attached the worksheet I am using with an example of the drawer count. The count of each bill and coin is entered into the yellow cells on the right, and the value of the count is transferred to the cells on the left. The "Drawer 1" column is the only column that will be used for the cash/coin deposit.

The drawer is counted and entered into yellow cells N4:N19. I would like the calculation result of each cell in yellow cells R4:R19.

If you have any other questions as to how the spreadsheet is used, please let me know.

Thanks in advance!

Hi all,

I'm trying to make a simple Cash Register with Excel. The problem is how to get an end-of-the-day-report!?

The main problem I have is communicating with the Windows OS! I could then maybe use the ">>" (incrementing) command in DOS and get excel to add the the value rows to the end of the Report-file?!

I've searched the Web a lot but can't find anything suitable - except ready made programs of course. But I want to use excel cause I can later do better data-handling if neccessary!

Heard there's a way to export to Access and use VBA etc. But I've never used Access and dont know VBA-programming .

Greatful for any help!

Thanx,
Nasaja/

Hi There

Has anyone seen a neat solution where Excel has been set up like a touch screen Point Of Sale/Cash Register?

Im looking for a simple solution to use excel as a cash register type app where it tracks the sales of ice creams (about 40 products)

Ideally the screen could be used as a touch screen pressing some buttons for each of the items sold.
Then press other buttons to say how much cash received while instantly calculating the amount of change to be given

Once transacted this record gets written to an excel list that can be used for business analysis/record keeping

Look forward to any handy hints on how to achieve this

Thanks in advance

Pembo (Queensland - Australia)

I have created a graphed curve in excel based on data I have entered. I now want to take the formula for the graph, and use it as an excel computation where I can plug in X and get Y, or plug in Y to get X. However, when i try to enter in variations of these equations, I end up with just the wrong answers (based on the data that went into the graph to begin with.

The formulas excel gives me are these:

y = -1.1288x 3 + 23.209x 2 - 166.76x + 430.07 (preferred), or alternately


y = 579.38e -0.574x


So how can I enter this formula so that I can have it pull x from a cell and spit out the answer for y (and the reverse)?