Allocating Costs Across A Row
Oct 27, 2008
I'm developing up a spreadsheet to conduct a feasibility on a 50 unit townhouse development and I was wondering if there was any way of allocating costs across the project without physically typing in the data in each month? (96 months in cashflow!)
ie: - In a row headed "Roads & Drains" can I allocate the say $500,000 worth of works across the months by using a formula or other methods?
My current method is very time consuming when I want to conduct a sensitivity analysis,
View 9 Replies
ADVERTISEMENT
Jul 28, 2014
I'm dealing with Projects that have an X amount of Units used in a certain period of time. The amount of units are evenly spread among the time the project runs. The problem is that many projects start in 1 month and end in another.
To clarify, the code in my head is like this:
Total Units in project: 80
project starting on 12 jan, project ran 19 days in Jan.
Project ending on 14 feb, project ran 14 days in Feb.
Cost of a unit in Jan = 200
Ofcourse, the code needs to be smart, since the data will be filled in to a table, so the code has to change automatic every time a new project is entered. I cant use pivots 'cause of a standard format sheet.I have the costs per month in a raw data sheet. Start date and end dates in different cells next to each other.
View 3 Replies
View Related
Feb 4, 2010
I got the following table.
http://img163.imageshack.us/img163/2972/oldnf.jpg
What formula could I use so that the amount under STP premium would display at the right cell under different time periods depending on the time of the trade date, just like the following images?
http://img687.imageshack.us/img687/5720/newcg.jpg
http://img168.imageshack.us/img168/413/new1zk.jpg
View 2 Replies
View Related
May 19, 2009
I have project's start date and end date and total cost. I want to spread the total cost across month on top using the total number of days[calculated using the end and start date] that project will run and the number of days that project is in that month. I have highlighted the area in yellow where I want formulas to work.
View 3 Replies
View Related
Jul 16, 2014
I have a sheet which is just a list of product codes and a sum of every order placed for each code. i.e.
Sheet2 AB1Prod codeOrdered2123100345650478960
then I have another sheet which is the orders placed by our customers. These orders are to go out in 6 periods each of 2 weeks.
For various reasons the amount ordered each time won't match the periods.
The sheet beforehand will look like
Sheet1 ABCDEFGHIJKLM1Prod codeP1 REQALLOCP2 REQALLOCP3 REQALLOCP4 REQALLOCP5 REQALLOCP6 REQALLOC212327 20 35 20 12 11 345633 40 50 15 25 11 478915 20 20 20 20 10
What I need to do is look up the quantity ordered and then allocate the ordered quantity to the periods carrying over the remainder to the next period and so with the table above the result would be
Sheet1 ABCDEFGHIJKLM1Prod codeP1 REQALLOCP2 REQALLOCP3 REQALLOCP4 REQALLOCP5 REQALLOCP6 REQALLOC2123272720203535201812 11 34563333401750 15 25 11 478915152020202020520 10
Both sheets are sorted by the product code ascending and it doesn't matter if it is a formula or VBA based solution as I am already using both.
The number of product codes is currently just over 400 but will grow to about 550 by the end of period 6.
View 5 Replies
View Related
Oct 31, 2008
I have been given a database with peoples names that require a unique number to hide/replace the name to hide their identity for a meeting and to act as a ghost system.
The problem i have is that some individuals names on the list appear more than once so i cannot simply call Joe Bloggs number 3 as he can appear further down the list at number 120.
I was wondering is there is a way to do this without manually replacing each name with a number and making and logging the persons name that corresponds to the number.
as there are 600 names and some can appear up to 4 - 5 times!
View 9 Replies
View Related
Aug 13, 2014
I am new to VBA macros and trying to create display the searched items in the TEXT boxes for e.g
I have the data in one sheet as below.
Messgae CodeProgram NameError Description
asdsasdsqwewe
w987oiuhad2343
789ARU100Praveen Code
789ujiikluiooqwe
[Code]...
i have created one text box so that user enter the data to search by giving Message code. My concern is
From the example,
If the user enter 789 in the text box, and click on te search button( On user form) then it should dynamically create displays 2 occurance in each text box.i.e if the 789 found in 1 col then it should create 4 test boxesto display Program name data and 4 text boxes to display Error Description side by side. if not text boxes if will be if we display in lables also.
View 1 Replies
View Related
Jan 9, 2013
I have 3 columns
Column A is a list of suppliers
Column B is a list of purchase orders (there may be several purchase orders for each supplier)
Column C is a list of values
I am trying to show the top 10 suppliers in terms of value, not sure on the best way to go about it
View 3 Replies
View Related
Sep 7, 2008
I am trying to put together a sheet to calculate item cost after shipping and taxes. To be honest I may have complicated the whole thing but what I have is two
order volumes that need to be changed to calculate the most cost effective solution.
For example: Item 1 (a1) is $40 and item 2 (a2) is $10, if I was to order 100 (a3) of (a1) and 50 (a4) of (a2), then multiply them in (a5) and (a6) to give me total
order values without taxes etc. I then have (a7) which is the shipping (a8) the import tax and (a9) the VAT.
What I am stuck on is how to work out how much a1 and a2 costs me per item, if I was to order 100 of each then I think it would be simpler by calculating
the total cost and divide by 100 then again by 25% which is the cost difference between $40 and $10. So although I may have gone around the houses on this
one, I want to be able to change cells a3 and a4 and then see how much it is costing per item after shipping and taxes.
View 9 Replies
View Related
Mar 6, 2014
I am having trouble calculating multiple full packages in the attached spread sheet, at the moment the formula is multiplying the number of packages by the price of one full package instead of looking for the correct price for that number of packages. 2014 rates checker formula.xlsx
View 5 Replies
View Related
Oct 7, 2008
Plan numbers are contained in Row 1 (1518, etc). Options are listed in Column A (L101, L102, etc). I want to add the costs of each option, resulting in the total and each option code only listed once, with that total, below each plan. Example:.......
View 2 Replies
View Related
Oct 29, 2008
Is there a possibility to make a sum off the amount of a cell (C59 in my example) of all the sheets in the workbook ?
just like =sum(sheet1!c59,sheet2!c59) but with all sheets.
View 4 Replies
View Related
Apr 12, 2013
I am going to convert different costs during life time of a product to Net Present Worth (NPW), consisting Maintenance and Rehabilitation costs (M&Rj) and Salvage Value (SV), according to the following formula: [URL] .......
SV occurs only once at the end of life time, it's NPW in Excel can be calculated as following:
=PV(i discount, AP,0, SV,1)
But M&Rj costs occurs J time during life cycle, and formulating manually their mathematical formula most times takes much time. Exactly what should I write in the cell to get their total NPW? In other words, how can I get the following formula by Excel functions: [URL] .......
Reminder: NPW= FV( 1/(1+i Discount))^n
FV: future value
i Discount: discount rate
n: numbers of years (periods)
View 1 Replies
View Related
Jan 7, 2014
I am trying to set up a spreadsheet to record business costs, I have a column [A] and [B] with start times and finish times respectively. [C] is the shift length say 10 hours but I always get an error if its a night shift as the start time is invariably later than the finish time. Is there a way around this without inputting the dates into the cells as I am trying to not duplicate data entry.
View 2 Replies
View Related
Dec 3, 2013
See attached. (I added in some arrows for the first month so it is easier to follow the logic)
I am working with monthly revenues. Revenues are earned in a specific month but only a % is received in that month and over the next 4 months. I need a formula that will spread these revenues earned in 1 month over the next 4 months.
Currently, I have a complex Index/Match formula which works until September. September, October, November, and December do not work correctly because once the months roll over from month 12 (December) back down to month 1 (January), my formula cannot recognize the range.
Essentially, I need the formula to return the correct amount that is being paid, even across different years.
I don't know if my formula can be modified or a new formula would be more efficient.
(This post originated from Commercial Services, which I just requested be removed from that forum)
View 3 Replies
View Related
Oct 25, 2007
I am trying to set up an IF formula for real estate operating costs. I am guessing that this is going to work best with an IF statement.
(Sample of the spreadsheet is attached)
I know this is wrong but it expresses what I am trying to do.
=IF(C5="Gross",B10=following year of B9 with month/Day being 1/1), if not B10=the folling year of B9 with same month/day)
1Cell C5 can either be Gross or Net from a drop down list.
2Cell B9 lists 3/1/2008
3If cell C5 is gross, cell B10 should be the following year from cell B9 but with starting with 1/1/??? As the month and day(year start).
B11 would then be the following year from B10
(following month/day pattern).
4If Cell C5 is NET, cell B10 should be the following year from cell B9 using the same month and day 3/1/????
View 2 Replies
View Related
Nov 18, 2009
I need to change cells to reflect new costs. Then have these costs roll into new totals. I attached a worksheet to demonstrate how it works. The numbers in gray should be adjustable. The numbers in tan are summing up incorrectly with my "SUMIF" formula.
View 2 Replies
View Related
Dec 20, 2007
I have downloaded .csv files with my phone call costs. I want to compare phone companies, so I need to calculate the h:mm:ss amounts to $ based on flagfall and rate per 30 seconds. I can open the .csv files in Excel but I do not know anything about Excel.
View 4 Replies
View Related
Feb 9, 2010
I have a form for calculating costs of products ordered which imports quantity and descriptions from another sheet. The imported info and basic calculation of appropriate cost based on ordered quantity seems to work okay. These formulas are as follows:
First column: =IF(ORDER!A5>0, ORDER!A5, "") inserts the quantity ordered
Second Column: =IF(ORDER!A5>0, "Part Description and Part Number", "") inserts the product description
Both those formulas return a blank cell if nothing is ordered. Then using an outlying cell (G3) I enter the following formula to determine appropriate cost based on total order quantity e.g. $2.57ea if total ordered quantity is less than 50 and $2.37 if total quantity is greater than 50.. {Q: How do I put a third pricing level in here??}
=IF(SUM(A5:A27)>50,"$2.57","$2.37")................
View 2 Replies
View Related
Jan 8, 2014
So I can easily put together a formula that maintains a consistent markup price across varying costs. Is there a formula that will give me a consistent gross margin % as I drag down a series of costs?
So if the cost is 158 a 42% mark up would be 224.36 but the Gross Margin % is only 29.58. I want to be able to hit a 42% mark up across the board.
View 5 Replies
View Related
Dec 1, 2008
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.
I have a series of packages that are denoted by text phrases. Examples:
4/6/12
2/12/12
6/4/12
18/12
24/12
For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).
I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
View 9 Replies
View Related
