$12: Monthly Invoicing Excel : Partially built-need further help...

  • Hi Guys,


    I have paid 10% to Ozgrid....Unique Transaction ID 7U923233U0229431V. Deadline is 72 Hrs.


    Attached is a forum.ozgrid.com/index.php?attachment/56958/ which I am building.


    Below are the rules and logic based on which I want it to work. I have already been able to cover points 1 to 4 by using vlookups and index-match functions that I am familiar with. But need help on the remaining.


    I tried a little bit of VBA myself...but could not make it all come together.


    Rules
    1)In the 'Monthly Invoice Input' sheet, first month will be chosen.
    2)Next TID will be chosen
    Once TID is chosen, it should populate everything else in col's C to I against that TID from "Employee Records" tab
    3) Col K, 'Rate' should be calculated as per rates in "Rate Card" tab. Rate is a function of Role and Type.
    4) The rules for normalised days is that one resource can only work for a maximum of 228 days in a year(Financial year Apr-Mar) - or average 19 days a month
    Also, if actual days worked is 19 or below the accrued days(actual -normalised) cannot be utilised for averaging.
    5)Therefore, in the 'Normalised Days' col, the cell against each resource should provide a validation such that the dropdown should contain maximum allowable days to maintain an average of 19 days and also days below that number till 0. The actual days worked are in tab 'Days Worked In the Year'.
    Say for Example, in April I worked for actual 21 days. So my normalised days for Apr will be 19, and I will have an accrual in my name of 2 days(21-19) which can be used later to maintain the average.
    Now, say I avail a vacation in May and my actual Days worked for May is 17. Then I am allowed to bill only 17 days. I cannot use the earlier 2 accruals for averaging since I have worked less than 19 days.
    At this stage the 2 month average is 18(19,17). Now if I work for more than 19 days in June, say 21 days, then I am allowed to use this for averaging as per rule in 5).
    So in June I will have normalised days as 21, so average will be maintained at 19(average of 19,17,21).
    6)There are some people who work Partly on a Fixed Price(FP) model of billing and some others on Time and Material(T&M) -see tab Days Worked In the Year. While averaging their total days(FP+T&M) should be considered. Also, for months where there are 0 days worked, that month should not be considered for averaging.
    7) I want an option to supress the above averaging rule for extraordinary situations. So if in col M 'Supress Rule(Y/N?)', the flag is set at 'Y', the user should be able put in free text value.
    8)col D 'End Date' of 'Employee Record' Tab shows when the person was taken of a project. If end date is populated then his /her record upto that date should
    be preserved, but his/her entry in sybsequent month should not be allowed. An error msg shouls be displayed if entry is attempted.
    9)Net Invoice Amount(col P)= Normalized Days * Rate
    10)Total Invoice Amount = Net Invoice Amount + Over Time Pay + Other Charges
    11) There may be people who work on two or more projects. Their actual days should be aggregated to arrive at the average.
    12)In tab "Monthly Invoice- By Director", the combination of Director and Month should give an extract of these entries(pulling from "Monthly Invoice Input"). If "Display in New Workbook?" flag is Y, then same extract should open in new workbook.
    13) The 'Summary' tab has 2 tables a)Total by Director b) Total by contract. The running total for these should keep populating automatically.
    Also, if in table "Total by Director", the aggregate in "Invoiced Thus far" exceeds 'Total PO Amount', then an alert/warning should be displayed.



    Thanks.

  • Re: $15: Monthly Invoicing Excel : Partially built-need further help...


    Hi All,


    I did not get any response to my query.


    Which might mean:


    • Either the quote was too low.
    • OR The problem statement was not well articulated by me.
    • Or maybe both.



    I am willing to raise the quote to $15. Cannot go any higher as I cannot afford :(


    There is no fixed deadline as such...although it would be great if I anyone is able to help me with it by Wednesday.


    And further, I have trimmed the problem statement to be more specific and hopefully more lucid.


    Attached is my worksheet : forum.ozgrid.com/index.php?attachment/56993/


    The idea is to restrict the average of any person to 19 days.


    So in col N(Controlled Average) of Tab 'Monthly Invoice Input' , say Andy(TID:T11345) actually works for 19 days in April and 21 days in May. Then his average will be 20 days which is not acceptable. Since the average should be restricted to 19 , therefore the user should be restricted to enter a set of values which will maintain the average of 19.


    I had got a few good suggestions from our members on the free forum, but not quite what I wanted to achieve.



    Thanks.

  • Re: $12: Monthly Invoicing Excel : Partially built-need further help...


    Hi All,


    I was able to find a way to built the spreadhsheet and hence closing this thread and offer.


    Thanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!