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

Cost Type	Qty	Unit Mth Cost	Margin	Unit Sell	Start Date	End Date	Total Mths	Total Cost	Total Sell
A	10	$80	30%	$114	1/01/2016	31/12/2025	120	$96,000	$137,143
B	12	$100	30%	$143	1/07/2016	30/06/2024	96	$115,200	$164,571
A	14	$200	30%	$286	1/07/2019	31/12/2022	42	$117,600	$168,000
B	16	$1,000	30%	$1,429	1/01/2018	31/01/2023	61	$976,000	$1,394,286

<tbody>
</tbody>

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

	Fiscal or calendar year
Cost	Yr1	Yr2	Yr3	Yr4	Yr5	Yr6	Yr7	Yr8	Yr9	Yr10
A	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx
B	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx

<tbody>
</tbody>

	Fiscal or calendar year
Price	Yr1	Yr2	Yr3	Yr4	Yr5	Yr6	Yr7	Yr8	Yr9	Yr10
A	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx
B	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx	$xxxx

<tbody>
</tbody>

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]

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

<colgroup><col span="2"><col span="2"><col><col><col><col><col><col span="2"><col span="3"></colgroup><tbody>
</tbody>

 

[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