+ Reply to Thread
Results 1 to 3 of 3

Financial Modelling - From deposit: display Bal @ var weeks, transport @ var weeks etc.

  1. #1
    Registered User
    Join Date
    12-21-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    28

    Financial Modelling - From deposit: display Bal @ var weeks, transport @ var weeks etc.

    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.

    Label Order 1
    Qty 37
    Unit Cost 100
    Total Cost 3700
    Deposit 700
    Balance 3000
    Shipping 500
    Transport 300
    weeks to Balance 2
    Weeks to shipping 5
    Weeks to transport 1
    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?


  2. #2
    Registered User
    Join Date
    12-21-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Financial Modelling - From deposit: display Bal @ var weeks, transport @ var weeks etc

    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.


  3. #3
    Registered User
    Join Date
    12-21-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Financial Modelling - From deposit: display Bal @ var weeks, transport @ var weeks etc

    The project is going well

    I need help on 'MOVING' formulae and named ranges between worksheets.
    But first here's how it's gone, and details of a beautiful solution to a very tricky problem:

    ******************
    I've used dropbox lists linked to the ranges of each different stage of payment.
    It's not ideal but it's workable.

    Displaying a loan payment schedule from different loan options
    I've also worked up six loan types that can be swapped in and out, with a dropbox list.

    This is so useful for all projects and any multiple data lists.... it's beautiful!

    Here's the code:
    Please Login or Register  to view this content.
    Create your loan data in columns.
    Select the column of each of your loans, that you are interested in displaying in a row, and name the range.

    You can see that I want to display payments from 6 different loans.

    Make a column list of text names relevant to the different loan payments eg.
    L12P
    L18P
    L24P
    C12P
    C18P
    C24P

    Select that list and name the range say loan_payment_list
    Place a dropbox list at the start of the row, and select the list of names.
    Paste the above code into the first cell of the row, then drag the cell as far as you want, Then press Ctrl+shift.

    Boom!
    Different loan payments can be swapped in at the click of the dropbox.

    I perfected it by adding zeros in each loan table down to 24 to match the longest table (each range must match the longest range).

    It was further perfected by adding 3 spaces between each payment, so that the payments appear every 4 weeks.
    Note: this requires adjustment of the range refs.

    For a payment holiday, I added 7 rows between the principal sum and the first payment.
    The interest might be charged marginally different, but this is an information model, not an audited account.

    Calendar vs Weeks
    Calendar payments don't match the 4 week cycle required for container shipments.
    It can be solved, once a start date has been established, by adding an extra row between payments, to match 5 week months.
    **********************************

    How To MOVE formulae between worksheets?

    For ease of development, all the above work was entered into the same worksheet.
    I presumed that I could simply cut certain elements and paste them to another worksheet.

    After searching.... all the answers avoid this issue... only 1 answer stated it couldn't be done, as the links would break.

    Apparently, the ranges should have referred to the worksheet name.
    I naively presumed that the statement 'scope = workbook' meant that it could be moved anywhere in the workbook.
    Alas... no.

    I need to move the formulae to allow adding and subtracting cells above.
    I can move the data section to a distant part of the worksheet
    However, it should ideally be immediately accessible, and a worksheet called 'loans' would be ideal.

    Here is a range ref
    =Model!$A$150:$A$155

    Model is the worksheet name.
    I guess if I moved these ranges, all my formulas would have to be changed, with Model! added to any cell reference.

    Is there not an easy way of doing this, so that everything is updated?

    Perhaps it would be easier to setup 'GoTo' links and keep the calcs on the same spreadsheet
    Last edited by Markexsel; 02-14-2017 at 09:42 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Add 12 weeks to date to return week number turn over at 52 weeks
    By nigelog in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2017, 05:58 AM
  2. Count the last full weeks wages for the last 12 weeks only
    By shorie7 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-25-2016, 01:59 PM
  3. Dividing monthly values into weeks automatically (some months have 5 weeks)
    By massimoo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-07-2012, 03:20 AM
  4. difference between two dates in working weeks(5 day weeks)
    By AWilderbeast in forum Excel General
    Replies: 3
    Last Post: 08-31-2010, 09:21 AM
  5. Calculating financial weeks
    By Alice21 in forum Excel General
    Replies: 4
    Last Post: 05-25-2010, 08:23 AM
  6. Calculating Averages based upon weeks not include Bye Weeks
    By Kfetterman1 in forum Excel General
    Replies: 2
    Last Post: 09-27-2006, 11:18 AM
  7. Turn excel weeks into finacial weeks
    By dragonfly in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-20-2005, 10:07 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1