The amount is $1256,
Cash Denomination will be $500, $100, $50,$10,$5 and $1. I need to split the amount into the number of pieces of each domination to present to the bank when i need to withdraw salary for the workshop workers.
500100501051
1256221011
Is there an "easy" way (a formula) to split an amount into different cells (periodes) (see the example under)?:
Amount Periode Jan Feb Mar
900 USD 01.01.10-31.03.10 300 300 300
Formulas?
I have a group trip coming up where we took in a $1000 deposit per person. The total amount is only in the main members cell. There will be two more payment amounts coming. I need to make certain each person has paid the total amount they are required for the trip. There are two possibilities for total trip amount. Double occupancy is $15,500.00 and Triple is $14,500 per person.
There is a unique Identifier for each registrant, except when there is a multiple person under one registration the identifier has all the same number except at the end. I figured out a way to strip the number down to just show the same number in one registered group. There could be up to 10 people under one registration. I might not have the best option for breaking down the Identifier number, but thought it would be a good start. Used the code Left(column, number).
Issue is this. How can I now take the total number of people under one group take the total deposit and split it between just that unique group so I can then determine what is still owed after payments are made.
I have contarct for amount $5000 with a start date of 1/1/2014 - 3/31/2014 (3 months). I would like to equally split my amount based on my number of months between 1/1/2014 and 3/31/2014 ie $1666.66/per month.
View 4 Replies View RelatedI need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:
I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
For the below macro.
ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030
A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.
For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.
I am new to macros on excel but I am trying to create a cash up workbook. This is what I want it to do:
First Sheet should be a summary of the month with each day. From there I want it to generate 4 worksheets for every day's cash up. (which should be Daily Cash up, Credit Card Sales, Cheque Sales & Account Sales).
The Daily Cash up Sheet must pull info from the other 3 and must allow me to input cash values manually. But the cc sales, cheques sales and account sales must be pulled from the other sheets.
After completion of cash up there should be a button on the Daily cash up sheet to say Finalise, where when it is pressed it summarises all totals on the first sheet. So when I open the workbook every morning, there should be a button on the first sheet to create a new 'cash up'.
Then there should also be a button on the first worksheet for month end, so that when you press month end it finalises the month and generates a new work book for the next month.
i am trying to set up a spreadsheet for petty cash so will have a description and an amount on the left of screen and numerous columns on the right for the transactions to be split between ...... Is there any way for me to tell it that if A2=fuel truck then the amount in column B2 needs to also be in column G2?
View 9 Replies View RelatedI want to build a cash register from excel.
I want to put many buttons, but lets say one on A1 that says $2 dollar item and name and lets say this $2 doillar item button is pushed(clicked on), it will add $2 to lets say the total say A15.
Now I can't even get a Macro to add $2 dollars to A15(or any macros to run at all, anybody have code I can copy, what I understand is you create macro name(give it letter, so yo can press letter and Ctrl to start) then start recording, then you do key strokes or mouse? Then press stop recording and you have macro?).
To get buttons to be viewed I have to go to Tools - Macros - script editor, and drag and drop(button on coding) or double click on button tool bar then File - View in Browser - . I get button but can't get it to do anything.
How do you put say A1 in code to A1=A1+5 or make a memory location out of A1. Now you can go to(while A1 is selected cell) "Insert" then "Name" then "Define" and give A1 a "Name". I can't get this name to work in coding(but I can get the name to work in cells like "Name" +5, will add cell 1 and 5, if you do it in different cell than A1).
I have a row which shows date by month and then a row which shows cash flow. I would like a formula to calculate the break even point in the cash flow row and return the month which this occurs. I would also like to show the number of units required to break even.
View 2 Replies View RelatedI am building a Discounted Cash Flow.
I have monthly cash flows rolling for 10 years, which I need to sum up into quarterly cash flows.
I am looking for a discounted cash flow worksheet where I can set the period of time to a certain number of years, set the terminal value at the end of that period of time, as well as having the ability to set the discount rate. When all three things are set, I would like to see the annual cash flows as well as the present value amount.
View 1 Replies View RelatedI'm working on a sheet where I can specify sales by selection of month (Sheetname= ZoekenMaand).
Everything works fine for the months "januari" and "februari" (yes, it's in dutch ) but from "maart" it doesn't seem to work any more.
I have Created an Excel Spreadsheet to work as a EPOS system. The only Problem i am having finishing it off is getting the cash Drawer to open at the end of a sale.
I have my Cash Drawer connected via a Rj 45 in the Back of my Receipt Printer. The Printer i have is a Star Tsp-600. I have Found out that the Open Drawer Codes are 7 or 27,7,10,50,7. I am not entirly sure what they mean though. The code that i have at the moment doesnt work it brings an error "53" file not found. This is my code
i have rec data in which I need a macro that will filter for CASH NOMS and keep everything thats in the criteria but delete everthing else in cash Noms
criteria is named range "Criteria" (I5:K10)
So from this
Sheet1 ABCDEFGHIJKL4 Criteria 5RecTeamAmountRef RecTeamRef 6CASH NOMSAMP1132.36CAETRN CASH NOMS*AMP*.AIMS 7CASH NOMSAMP215.36.AIMS546.3 *WTRN96 8CASH NOMSINTEREST915.12WTRN96 *AMPTRH 9CASH NOMSFA5-65.36AMPTRH *.WSTFGH 10CASH NOMSFA6-9.01CBWSSG *WTWHAT 11CASH NOMSCA-9.01CBWSSG 12CASH NOMSDES-9.01MSFAST 13CASH LIFEUUTU-9.01CBWSSG 14CASH LIFEAMP1-9.01CBWSSG 15CASH LIFEAMP232.12.WSTFGH 16CASH LIFEAMP315975.36CBWSSG 17CASH LIFEAMP4-9.01OPTMOD 18CASH NOMSFA1-9.01CASH WTWHAT Excel tables to the web >> Excel Jeanie HTML 4 .........................
I am trying to model the distribution of cash flows from a real estate investment. This is a bit complicated, but basically A1 through A37 are a series of cash flows after payment of debt service from month 0 (closing) to month 36. The real estate project has two investors, the Institutional Investor and the Developer. In this case, the Institutional Investor contributes 90% of the required project equity and the developer contributes the remaining 10%. The cash flow is distributed according to a set of parameters specifying cash flow percentage allocations and IRR targets, collectively known as a "cash flow waterfall"." As with all for-sale residential development deals, the early cash flows are negative and then turn positive as unit sales occur. The order of distribution of the cash flow remaining after debt service is paid is as follows (the waterfall):
1. Both investors receive the return of their invested capital from cash flow distributions.
1. Hurdle 1 (after the return of equity to both investors), the Institutional Investor receives 90% of the monthly cash flows and the Developer 10%, proportional to their respective overall equity contributions, until both investors achieve a 12% IRR (all IRR's to be calculated from deal inception/first dollar invested).
2. Hurdle 2, (after Hurdle 1 is satisfied), the Institutional Investor receives 60% of the monthly cash flows until the Institutional Investor achieves an 18% IRR.
3. The Final Split (after satisfying Hurdle 2) allocates monthly cash flow 50% to each investor.
On an overall project basis, the Developer should realize a disproportionate share of the cash flow after the second hurdle is hit. This is what is known as the "promote." The most recent 36 month (37 including Closing) projected cash flows are as follows:
($1,315,567) - Closing
($113,697) - Month 1
($178,397)
($168,667)
($150,365)
($36,506)
$16,662
$120,283
$107,443
$107,443
I realize this is complicated, but this structure is quite common to real estate, private equity, venture capital and hedge fund investments (along with most any joint venture deals with multiple classes of equity participants). The canned software that is out there that performs this type of anlysis is both expensive and rigid, and doesn't typically enable the required sensitivity analysis in structuring a deal.
I have got three period like this
Period: 2010 2015 2018
Cash: 100 200 300
I would like to use a function (i am thinking offset will do the work) to put these cashflows on a timeline i.e. 2010,2011,2012 etc...
The three periods described above will be changeable, so the cashflows will adjust accordingly...
With reference to Cash Register example that I downloaded from Roy Cox; is it possible to link the numerical keypad that is created in the file to the PC Keypad.
I realise I would need to use keypress functions but am unaware of the ascii for these keys. Also; how do I limit that the keypad writes it to the label? Otherwise, would I need to create a new userform instead of what was created by Roy.
I am trying to put together a projection spread sheet for a client for cash flow purposes. I have it laid out but I cannot wrap my head around the formula and I've tried a few. Here it is.
A1: Client Name B1: Invoice Date C1: Projected Due Date ( I have 45 days added to B1) D1: Invoice Amount E1: Jan F1: Feb G1: March H1: April I1: May J1: Juneetc...
I am trying to forcast out the invoice collection date (C1). So basically in the month of June column I am trying to say if C1 falls between June 1 & June 30th then it should equal D1 (the amount due in that month). So if the invoice has a due date (C1) that falls in that particular month than I am forecasting the cash flow in the month it's collected.
formula in excel to calculate payback and discounted payback periods with uneven cash flows.
EX.
Yr CF
0 ($2,000)
1 160
2 200
3 350
4 395
5 432
6 440
7 442
8 444
How can I total overtime based on a "Reason Code" and whether the employee chose "Cash" or "Time" compensation?
View 2 Replies View RelatedI have a petty cash sheet set up:
A1 - Date
B1 - Receipt No.
C1 - DEPT CODE
D1 - NOMINAL CODE
E1 - Reason
F1 - Amount
G1 - Running total (I've put a little formula in there to keep the balance - clever eh!)
So - this is fine - except I have to add the total amount for each department/nominal code each month.
What I'm doing at the moment is copying and pasting, sorting the sheet by:
Department
Nominal Code
Date
Then doing a manual add up of each and adding it to my month end report.
I'm really not an expert at all - but I thought there must be an easier way for me to do it. After inputting the petty cash there MUST be a way that it can automatically calculate it?
Or is this going to be a difficult thing to do requiring expertise in pivot tables etc?
I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.
Part 1:
#########################################
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".
I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.
So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".
#########################################
Part 2:
####################################################
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"
There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.
I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.
So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".
####################################################
I am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.
AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?
How do I split numbers!
I have two problems/challenges!
Part I...
I got the answer 1987, and now I want Excel to take the numbers out and display...
1 in one box then i set + in the next, then 9 in the 3ed. box, next box +, then 8, then +, then 7 in the last so that i can have Excel make a SUM of it all to 25.
How do I split 1987 and put the numbers in different boxes?
Part II...
I want to make A=1 B=2... all the way up to 9, then start over again with J=1 K=2... up to 9 again and then over again.
So that if I write my name it comes out as a value of 14 (Odd = O=6 D=4 D=4 =14)
(AJSØ=1 BKTÅ=2 CLU=3 DMV=4 ENW=5 FOX=6 GPY=7 HQZ=8 IRÆ=9, It's the Norwegian alphabet, that's why there are some extra letters)
So how do I set up my Excel so that is ANY name is typed in I can get it out into a number from the values assign?
In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001
On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?
I need some code to do the following.
Look at worksheet 1. Find column "Amount". Insert column next to amount.
For all highlighted cells in column "amount" insert text "R" in adjacent (inserted) column.
Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g:
A2: aa,ab,ac
String Value
aa 1
ab 1
ac 3
What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.
How can i sum up the amount of all "case" sold in the second month of 2009.. so the output should appear in C11 as i have already done manually.
View 9 Replies View Related