1) Combine both attached workbooks. This should take about 1 minute to do.
2) The financial modeling workbook needs to have every attached worksheet tie into the respective areas of the workbook. Please scroll entire worksheet pages.
3) The 2nd tab in the Financial Modeling Template is an Assumptions tab. These assumptions will need to be incorporated into the Excel workbook where needed. For examples row 21 is the AR Days Collected and it has 30 days noted. That means the cash amount in the balance sheet / cash flows statement should reflect that on all customers. I also want to know if we can also have it so that if a customer is set on 60 or 90 day terms, the cash balance would reflect that. I would imagine there would need to be an “If” “Then” formula but I could be wrong. The same would hold true for AP. If we can negotiate 30 day terms with a vendor or 60 day terms, I’d want to make sure the respective worksheets reflect the flow of money going out vendor by vendor so we can calculate cash properly and account for cash properly. Lines 15 and 16 take into consideration if we have fixed and/or revolving debt as well so that would need to be reflected in the workbook as well. I can explain the purpose of this worksheet more if you need additional notes.
4) The Notes worksheet has a note that we need to account for
5) Tab 5 is a use of proceeds tab so whatever is filled in here needs to flow into the financial modeling
6) The ratios tab is very simple and pulls in information from various areas of the workbook and should be set to change as items throughout the workbook changes.
7) The Conversion tab is to help manage cash flow and get a better understanding of where sales are. This takes into consideration things such as column 3 or 6. You will note column 4 is the actual close date and column 5 is the estimated so the respective areas on the worksheets need to reflect this. You will not the column headings explain what is needed and everything needs to carry over. WE SHOULD PERHAPS DISCUSS NOT USING THIS PARTICULAR SPREADSHEET
8) The Core P&L tab is the Profit and loss taking into consideration the actual for the past 12 months and then project out monthly for 5 years. You will note as you scroll down, percentages are applied to the line items to calculate the projected amounts above in the actual P&L.
9) The Core BS tab is the balance sheet tab. You will note if you scroll down, it calculates day sales and you can see info is pulled from the Core P&L for the actual months but pulled/calculated differently for the projected months. You will note that there are items being pulled for the days inventory calculation as well as days payable. Note Actual is different than projected.
10) The Core CFS is the core cash flow statement and formulas need to be added to correctly account for case based on the assumptions tab as well as info pulled from other areas such as the balance sheet and income statement.
11) The CFS-13 week is a 13 week cash flow statement and needs to calculate accordingly.
12) The 5 yr P&L Summary is no different that the Ratios worksheet. Simple formulas to pull in the respective items. The same for 5 YR BS and 5YR CFS. The information for these cells is simply pulled from the respective areas within the workbook.
13) Once you incorporate the KPI Metric – LTV – CAC Ratio worksheets which is simply just copying and pasting the worksheets from that workbook into the Financial Modeling Template 12-2018 Workbooks. The formulas will need to be verified to calculate properly and once you have that done for one column, all you have to do is copy and paste the other columns. This should hold true for much of the workbook where projections are done. The amounts in the actual columns will need to manually be entered. The data that needs to be input for the LTV-CAC worksheets will need to be pulled from the other worksheets.
19 freelancers are bidding on average $466 for this job
Hello! I can help you with your Excel Financial Model Tool. You can be assured of a high quality and accurate output. To better assess my capabilities please do see reviews from my previous projects. thanks!
I am an experienced financial analyst with the expertise in financial modeling. I am also CFA (Chartered Financial Analyst) qualified. Therefore, I can aptly undertake your work. For more details, contact me.
I have read your description onFINANCE and would like to work for you.I can complete your task within the deadline. For further details please inbox [login to view URL] you
I will complete it in 4 days Do you need dashboard analytics for this as well? The actual 12 months data to be needed in order verify that the final financial model work properly.
Hi, I can help you to realise your project because of Masters degree in Financial ingeneering and 5 years of experience in modeling department of banking industry. Please let me know if ok. Kind regards, Julia