VBA Help in Cash flow forecast

[euleriscool]

Hi, I need help in creating a cash flow forecast in vba. I have already done this is in excel however i would like vba code in order to give a table of the monthly investment Cash flow forecast of revenues, production costs andexpenses for a randomised scenario. Essentially, you need to reproduce using VBA the CFF layout/calculationsshown in the illustrations, subject to the chosen starting date and length ofterm. If you could help i would be grateful,
Thanks.

[SamT]

Start by reviewing all numbers in Column A and assign a Name to each, for example: A15 could be BaseAnnualRevenue.
Then move to Column B and Find all Parameters, such as Annual (random) adjusment. and add a named Constant AnnualRandomCashFlowAdjusment = 0.10
In Column C find Formulas and create functions like RandomisedSalesRevenue = BaseAnnualRevenue * AnnualRandomCashFlowAdjusment

Now BaseAnnualRevenue. is actually a Formula that depends on two other Named Items: WholeSalePricesPerCase and MonthlySalesQuantities
WholeSalePricesPerCase = Array(sheets("Parameters").Range("C6:C8") and the same for MonthlySalesQuantities
BaseAnnualRevenue = WorkSheetFunction.=SUMPRODUCT(Parameters(WholeSalePricesPerCase, MonthlySalesQuantities)

You now have only three "Magic Numbers": AnnualRandomCashFlowAdjusment; WholeSalePricesPerCase; and, MonthlySalesQuantities. They are the "Random" viaribles for your code.

all Formulas such as BaseAnnualRevenue; WholeSalePricesPerCase; and, MonthlySalesQuantities can/should be Functions.

Do this for Sheets "Parameters" and "CFF." With Named Constants/Variables and Named Functions, your code will Write itself.

Then you can use Loops (For M = 1 to 12) and Cell Offset(, M) to create the entire Forecast.

[euleriscool]

Thanks for your responsse and help.

BaseAnnualRevenue = WorkSheetFunction.=SUMPRODUCT(Parameters(WholeSalePricesPerCase, MonthlySalesQuantities)

For this line it says for me to define Parameters. How should i define it?

[euleriscool]

Hi I have this code, also file attached. How do i set the cell A15 as this Ba() value, I want A15 to equal the revenue that the code below calulates.

Function Ba(AC As Range, AD As Range)
Dim Am As Variant, det As Double, Al As Variant
Am = AC.Range(Cells(1, 1), Cells(3, 1)).Value
Al = AD.Range(Cells(1, 1), Cells(3, 1)).Value
det = Am(1, 1) * Al(1, 1) + Am(2, 1) * Al(2, 1) + Am(3, 1) * Al(3, 1)
MsgBox "Determinant of " & det
End Function
' -----------------------------------------------
Sub run_A61()
Call Ba(Range("C6"), Range("C11")) ' ActiveCell
End Sub

28109

[p45cal]

In cell A15:
=SUMPRODUCT(C6:C8,C11:C13)

[macropod]

euleriscool: Please don't start multiple threads on the same topic. I've merged the two you started on this one.

[euleriscool]

Ok sry. Thanks for your response. But that is not what i wanted to know. I wanted to know how i can set cell A15 to the variable det thats on the 'CFF ex1' worksheet when i click on the button on the parameter worksheet.
Thanks

[p45cal]

I'm really not sure what you're wanting but at a guess get the button to run this snippet:
With Sheets("Parameters")
Sheets("CFF Ex 1").Range("A15") = Application.SumProduct(.Range("C11:C13"), .Range("C6:C8"))
End With

[Paul_Hossler]

Maybe ...?





Option Explicit


Sub Ba(AC As Range, AD As Range)
Dim Am As Variant, det As Double, Al As Variant
Am = AC.Range(Cells(1, 1), Cells(3, 1)).Value
Al = AD.Range(Cells(1, 1), Cells(3, 1)).Value
det = Am(1, 1) * Al(1, 1) + Am(2, 1) * Al(2, 1) + Am(3, 1) * Al(3, 1)
MsgBox "Determinant of " & det

Worksheets("CFF Ex 1").Range("A15").Value = det
End Sub
' -----------------------------------------------
Sub run_A61()
Call Ba(Range("C6"), Range("C11")) ' ActiveCell
End Sub

[euleriscool]

Thx for your responses yh it works. Thanks thats it for now!