10 Year Price Model

[kelvinlow39]

Hello,
I am looking at creating a Price Model, and I am looking at the best way to solve the problem other than having lots of columns.

My data Set looks like This

[TABLE="width: 866"]
<tbody>[TR]
[TD="align: center"]Cost Type[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Unit Mth Cost[/TD]
[TD="align: center"]Margin[/TD]
[TD="align: center"]Unit Sell[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]End Date[/TD]
[TD="align: center"]Total Mths[/TD]
[TD="align: center"]Total Cost[/TD]
[TD="align: center"]Total Sell[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$80[/TD]
[TD="align: center"]30%[/TD]
[TD="align: center"] $114[/TD]
[TD="align: center"]1/01/2016[/TD]
[TD="align: center"]31/12/2025[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]$96,000[/TD]
[TD="align: center"]$137,143[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]$100[/TD]
[TD="align: center"]30%[/TD]
[TD="align: center"] $143[/TD]
[TD="align: center"]1/07/2016[/TD]
[TD="align: center"]30/06/2024[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"]$115,200[/TD]
[TD="align: center"]$164,571[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]$200[/TD]
[TD="align: center"]30%[/TD]
[TD="align: center"] $286[/TD]
[TD="align: center"]1/07/2019[/TD]
[TD="align: center"]31/12/2022[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]$117,600[/TD]
[TD="align: center"]$168,000[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]$1,000[/TD]
[TD="align: center"]30%[/TD]
[TD="align: center"] $1,429[/TD]
[TD="align: center"]1/01/2018[/TD]
[TD="align: center"]31/01/2023[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]$976,000[/TD]
[TD="align: center"]$1,394,286[/TD]
[/TR]
</tbody>[/TABLE]

And I am looking to Extract 2 Tables that look like this



[TABLE="width: 930"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="colspan: 2, align: center"]Fiscal or calendar year[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Yr1[/TD]
[TD="align: center"]Yr2[/TD]
[TD="align: center"]Yr3[/TD]
[TD="align: center"]Yr4[/TD]
[TD="align: center"]Yr5[/TD]
[TD="align: center"]Yr6[/TD]
[TD="align: center"]Yr7[/TD]
[TD="align: center"]Yr8[/TD]
[TD="align: center"]Yr9[/TD]
[TD="align: center"]Yr10[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 930"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2, align: center"]Fiscal or calendar year[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Price[/TD]
[TD="align: center"]Yr1[/TD]
[TD="align: center"]Yr2[/TD]
[TD="align: center"]Yr3[/TD]
[TD="align: center"]Yr4[/TD]
[TD="align: center"]Yr5[/TD]
[TD="align: center"]Yr6[/TD]
[TD="align: center"]Yr7[/TD]
[TD="align: center"]Yr8[/TD]
[TD="align: center"]Yr9[/TD]
[TD="align: center"]Yr10[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[TD="align: center"]$xxxx[/TD]
[/TR]
</tbody>[/TABLE]


have got this to work but is does use lots of columns I was hoping for a simple way to extract the above reports

Regards
Kelvin

 

[oldbrewer]

[TABLE="width: 1061"]
<colgroup><col span="2"><col span="2"><col><col><col><col><col><col span="2"><col span="3"></colgroup><tbody>[TR]
[TD]Cost Type[/TD]
[TD]Qty[/TD]
[TD]Unit Mth Cost[/TD]
[TD]Qty monthly cost[/TD]
[TD]Margin[/TD]
[TD]Unit Sell[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Total Mths[/TD]
[TD]Total Cost[/TD]
[TD]Total Sell[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]10[/TD]
[TD] $ 80.00[/TD]
[TD] $ 800.00[/TD]
[TD="align: right"]30%[/TD]
[TD] $ 104.00[/TD]
[TD="align: right"]01/01/2016[/TD]
[TD="align: right"]31/12/2025[/TD]
[TD="align: right"]120[/TD]
[TD] $ 96,000.00[/TD]
[TD] $ 124,800.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]12[/TD]
[TD] $ 100.00[/TD]
[TD] $ 1,200.00[/TD]
[TD="align: right"]30%[/TD]
[TD] $ 130.00[/TD]
[TD="align: right"]01/07/2016[/TD]
[TD="align: right"]30/06/2024[/TD]
[TD="align: right"]96[/TD]
[TD] $ 115,200.00[/TD]
[TD] $ 149,760.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]14[/TD]
[TD] $ 200.00[/TD]
[TD] $ 2,800.00[/TD]
[TD="align: right"]30%[/TD]
[TD] $ 260.00[/TD]
[TD="align: right"]01/07/2019[/TD]
[TD="align: right"]31/12/2022[/TD]
[TD="align: right"]42[/TD]
[TD] $ 117,600.00[/TD]
[TD] $ 152,880.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]16[/TD]
[TD] $ 1,000.00[/TD]
[TD] $ 16,000.00[/TD]
[TD="align: right"]30%[/TD]
[TD] $ 1,300.00[/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]31/01/2023[/TD]
[TD="align: right"]61[/TD]
[TD] $ 976,000.00[/TD]
[TD] $1,268,800.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]And I am looking to Extract 2 Tables that look like this[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Fiscal or calendar year[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cost[/TD]
[TD]Yr1[/TD]
[TD]Yr2[/TD]
[TD][/TD]
[TD]Yr3[/TD]
[TD]Yr4[/TD]
[TD]Yr5[/TD]
[TD]Yr6[/TD]
[TD]Yr7[/TD]
[TD]Yr8[/TD]
[TD]Yr9[/TD]
[TD]Yr10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Fiscal or calendar year[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Price[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]is the cost the same every year for each row[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]and my numbers at the top do not agree with yours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[kelvinlow39]

The Cost Per Mth stays Static, in the first Line the Total Cost is $80 * 10 * 120 Mths = $96,000

The reason You are different is you are Using Markup Not Margin, Margin is calculated Cost/(1-Margin) so it is $80/(1-.3) = $114

 

[Oeldere]

your range is not for all years the same

for product A
1/01/2016 31/12/2025 - 1/07/2019 31/12/2022

what is the range of the fiscal year.

 

[kelvinlow39]

your range is not for all years the same

for product A
1/01/2016 31/12/2025 - 1/07/2019 31/12/2022

what is the range of the fiscal year.

The Start date and End date for Product A are meant to be different, for the 2 Rows
The Fiscal Year I was after is Jul to Jun