Aggregate amount lookups

[exceltrainee314]

Hello, I have attached a sheet that is designed to track the proceeds from sales of items and to recognize the cost of those items in the period in which they are sold. Payments for a sold item can be received in partial chunks.

As you will see there are 2 different methodologies and I would like the workbook to be able to do both.
Method #1 - recognizes the full “Current Cost” of the item during the first period in which the item has any payment (full or partial)
I think the trick here will be finding a way so that you only recognize the full cost in the first period of a partial sale and don’t recognize the cost again during the period of the second partial sale (ie we want to avoid double counting things)


Method #2 recognizes only the proportion of the “Current Cost” in line with the proportion of the payment that is received. So for example if an item is sold and 25% of the total proceed are received in a given time period then we would recognize 25% of its associated cost.

I was hoping for some help filling in rows 186 and 188 with those methodologies using an appropriately simple formula. Its important that this be one in a single row (rather than with a row for each item and summing that row). Is there a way to use the AGGREGATE function or something similar to do that? btw, i left some manual examples in this workbook for reference in addition to these questions again in red text

Also, as you will see in rows 35 to 39 I have built a rather long and not-so-elegant formula involving the SUM of several SUMIFS. Is there a simpler way to do that?


thank you!

[ELeGault]

My eyes are going crossed -

Lets tackle one item at a time shall we?

Is there a reason you are attempting to house everything on the same sheet when there is clearly a large data set you are attempting to house?... yet its a summary table of daily values...


The better way to go about this is to make a Table of Sales

RecNo | Name | Date | Amount



Then we start building these summaries.

ROI : Where does this come into play. Is it always a set 2X? or is there a mechanism in which one would lock that in...
Does that need to be dynamic or will this be set on the Item Level one and done?

Previously sold, where are we getting that there was 25 previously sold for Item 1, is this just a known fact that Item 1 has a history of 25 or can this be deduced in the data provided.

What constitutes a period... each DAY? the series of dates between item 1 to item 5 in the progression of sales dont really make sense... perhaps this was a quick example and consistency was not priority but I find it hard to go Jun25, Dec25, Jun26, Dec26, Jun27... are we to assume that we sale 6 months apart each day we go back and forth?

The more I look at this the more questions I have...so lets start over...

Dont give us the summaries just yet... lets look at what data is FACT and how you are storing that.

Sales Records
Names (Items), Dates, Sales(Counts), Amounts

Investment Profiles
Names(Items), ROI %, Date(s) Start/Stop if applicable

Dates Table
Date, DayNumber, MonthNumber, WeekNumber, YearNumber and Most importantly "Period" defined in a way that the grouping has boundaries that are not overlapping
This should be a matrix of time that is beholden to your program, product...idea


Once we can establish the facts of the data we can start to bend around the corners to generate a view you wish to have... what I am seeing in rows 32 through 188 is a table summary... which should just be a pivot table really unless you can justify having a calendar view where only 30 days are utilized and the rest are just fluff space and terrible on the eyes for any end user.


If you can clean up the start then we can talk about the end.

END -

You are looking for a few summary views

By Item an actual ROI report.
Shows who, Total In, Total Recurring, Current Costs (Still not really seeing how that is evaluated but here nor there), Current Value

Then a progression report (Sales listed Bi Annually by Named Item) showing the Proceeds gained at each junction to project what can be made IF one is to make their sale or influential buy every 6 mo...

I have only spent about 5 min looking at this and the last 5 writing this about what I just saw... so maybe I am over simplifying or calling complications to things that may not be...

But if you want some assistance fast... I would suggest dialing it down to one issue at a time and not your think tank poured out on a cork board.

Cheers

- Eddie