Excel Infrastructure Purchase/Upkeep Cost Calculators

[quigon jinn]

Those of you who've worked with your own functions in Excel may find these valuable for nation planning in Excel - much easier to use when you can figure out the costs in your sheet realtime.

Note: the total discount factor is the percent off you get. So with 5 Labor Camps, Iron, Uranium, and Lumber, for the TotalDiscount you would put 0.5*0.97*0.9*0.92 = 0.402 in that particular example. Other variable names are straightforward.

Let me know if you have questions, otherwise enjoy!

[code]
Function InfrastructureCost(CurrentLevel As Single, NumbToBuy As Single, TotalDiscount As Single) As Single

Dim endTotal As Single
Dim curPurchased As Single
Dim curCost As Single
Dim curIncAmount As Single
Dim totalCost As Single

endTotal = CurrentLevel + NumbToBuy
curPurchased = CurrentLevel
totalCost = 0

Do
If curPurchased < endTotal Then

'set buy amount
If (endTotal - curPurchased) > 10 Then
curIncAmount = 10
Else
curIncAmount = endTotal - curPurchased
End If

'find total cost
Select Case curPurchased
Case 0 To 19.99
curCost = curIncAmount * (500) * TotalDiscount
Case 20 To 99.99
curCost = curIncAmount * (12 * curPurchased + 500) * TotalDiscount
Case 100 To 199.99
curCost = curIncAmount * (15 * curPurchased + 500) * TotalDiscount
Case 200 To 999.99
curCost = curIncAmount * (20 * curPurchased + 500) * TotalDiscount
Case 1000 To 2999.99
curCost = curIncAmount * (25 * curPurchased + 500) * TotalDiscount
Case 3000 To 3999.99
curCost = curIncAmount * (30 * curPurchased + 500) * TotalDiscount
Case 4000 To 4999.99
curCost = curIncAmount * (40 * curPurchased + 500) * TotalDiscount
Case 5000 To 7999.99
curCost = curIncAmount * (60 * curPurchased + 500) * TotalDiscount
Case 8000 To 14999.99
curCost = curIncAmount * (70 * curPurchased + 500) * TotalDiscount
End Select

totalCost = totalCost + curCost
curPurchased = curPurchased + curIncAmount

Else
Exit Do
End If

Loop

InfrastructureCost = totalCost

End Function

Function BillCost(ByVal CurrentLevel As Single, ByVal TotalDiscount As Single) As Single


Select Case CurrentLevel

Case 20 To 99.99
BillCost = CurrentLevel * (0.04 * CurrentLevel + 20) * TotalDiscount
Case 100 To 199.99
BillCost = CurrentLevel * (0.05 * CurrentLevel + 20) * TotalDiscount
Case 200 To 299.99
BillCost = CurrentLevel * (0.06 * CurrentLevel + 20) * TotalDiscount
Case 300 To 499.99
BillCost = CurrentLevel * (0.07 * CurrentLevel + 20) * TotalDiscount
Case 500 To 699.99
BillCost = CurrentLevel * (0.08 * CurrentLevel + 20) * TotalDiscount
Case 700 To 999.99
BillCost = CurrentLevel * (0.09 * CurrentLevel + 20) * TotalDiscount
Case 1000 To 1999.99
BillCost = CurrentLevel * (0.11 * CurrentLevel + 20) * TotalDiscount
Case 2000 To 2999.99
BillCost = CurrentLevel * (0.13 * CurrentLevel + 20) * TotalDiscount
Case 3000 To 3999.99
BillCost = CurrentLevel * (0.15 * CurrentLevel + 20) * TotalDiscount
Case 4000 To 4999.99
BillCost = CurrentLevel * (0.17 * CurrentLevel + 20) * TotalDiscount
Case 5000 To 7999.99
BillCost = CurrentLevel * (0.1725 * CurrentLevel + 20) * TotalDiscount
Case 8000 To 14999.99
BillCost = CurrentLevel * (0.1755 * CurrentLevel + 20) * TotalDiscount
End Select

End Function
[/code]

[AmberIgnativs]

You do not have permission to reply to locked topics.