MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 236|Reply: 1

Financial Modelling - From deposit: display Bal @ var weeks

[Copy link]

1

Topics

2

Posts

9

Integral

Newbie

Rank: 1

Integral
9
Post on 2-9-2017 17:09:42 | All posts |Read mode
Financial Model to graph overview of expenditure, income  stock
Hello everybody, I could do with some help in determining how best to structure my variables and display columns in order to display results across 52 weeks, to allow the key elements to be graphed.
The idea is simple enough, and would be very easy, if cell data could be pushed to another cell - but I've read that this is not possible.
I'm sure that it must be simple to display data according to time, because this kind of modelling is old hat... but I'm lacking the knowledge to even discover what techniques that I should be learning.
Perhaps I need to create a calendar table of columns?
I have created 12 columns of data, each representing a potential order to be placed with a manufacturer, listing all the stages of payment, with variables to indicate when the different payments would be due.
Eg.
LabelOrder 1
Qty37
Unit Cost100
Total Cost3700
Deposit700
Balance3000
Shipping500
Transport300
weeks to Balance2
Weeks to shipping5
Weeks to transport1

Etc.
The Order 1 column has next to it 11 similar columns.
Each column contains all the information associated with each order.
I created 52 sets of 4 columns for labelled inputs and labelled outputs.
These were to be controlled by the Order No and weekly sales.
By entering Order 1 in week one... Deposit immediately appears (achieved via 12 IF nests) - Marvelous
But how to automatically display the balance, two weeks later, or three weeks later depending upon the balance payment variable?
I probably should re-format the weeks to single columns (to simplify counting).
This will be my next attempt.
Perhaps I can get the staged display of expenditure, and the appearance of stock say 8 weeks after paying the deposit.
However, I think that it is going to be a nightmare... and maybe all this has been done before, because it is never the case that an order number produces stock or payment on the same day.
Perhaps this should be done using a database?
Can anybody offer any guidance?
Reply

Props Report

1

Topics

2

Posts

9

Integral

Newbie

Rank: 1

Integral
9
 Author| Post on 2-10-2017 18:00:35 | All posts



To be honest, today I gave up on trying to create a fully automated data linked model.
I couldn't face another day getting nowhere.
However, the solution I've chosen isn't too bad at all.
In fact, with further development it will be excellent.
I'm simply copying and pasting hard links into the '52 week column calendar report'.
These can be easily dragged around.
Contrasting background colours for adjacent orders will prevent any confusion.
One other interesting thought, was to introduce artificial modifiers, in each week.
Eg Weekly overheads, capital purchases, and short-term product price adjustment.
This would save switching back and forth between the source data, and the calendar report.
I'm thinking 'drop down options'.
In this way, one could rapidly mod the charts, and have a visual record of the changes made.
The moral of the story is:
Don't persevere down a bad path.
Another path may be quicker and open up other opportunities.
Reply Support Opposition

Props Report

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 11-19-2017 16:23 , Processed in 0.131030 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!