Khaldoun Abu-Zeid
New Member
Hi everyone and i hope i am posting in the right place.... this is my first post..
I am preparing a sales budget for my boss. Main layout of my sheets is as follows: -
1- Sheet 1 is the basic data sheet will be the order booking forecast where in cell B3, i list forecasted projects name, C3, client name, D3 the material , E3 amount, F3 the expected order booking date & G3 the expected total delivery time for completing and closing the order and issuing the final invoicing date. cells H2 to S2 has the months of 2015 (Jan to Dec). The sheet should have a function to place the total of the forecasted order (E3) in the right cell (H3 to S3) based on the month of the expected date cell F3.
2- Sheet 2, shall include the forecasted Incoming CashFlow for each order from sheet 1 so that this incoming cashflow is shown in monthly totals Jan - Dec 2015. Column S shall have the total cashflow in 2015, balance shall be carried forward to 2016.
Remarks : Monthly incoming cashflow amounts do not follow general rule i.e. it could be a certain % based on progress / milestone (max. of 6 installments) and tied to certain dates. It could be 100% advance payment..... i.e. different scenarios.
Each row in Sheet 2 corresponds to one order from Sheet 1
3- Sheet 3 shall have the Outgoing Cashflow for each order projected on monthly totals (Jan Dec 2015) with Grand Total and Carry forward amount to 2016. These outgoing cashflows can be payments to suppliers, custom duties, shipping charges, logistics...etc.
4- Sheet 4 to show the net monthly cashflows and cumulative cashflows inorder to locate the approx. months where financing would be needed (when cashflow is negative)
5- Sheet 5 to show the forecasted invoicing dates based on order amounts and Expected Order Completion Dates from Sheet 1, showing monthly totals (Jan - Dec 2015), Grand Total for 2015 and C/F for 2016.
6-I need a Dashboard to show the monthly order booking forecasts, Cashflows (incoming / outgoing) and invoicing.
I need the sheet to be connected so that if i change the information any of the sheets, the change will be reflected on other worksheets and the whole workbook will re-calculate to give me accurate data.
Any Idea how to start this project ?! It difficult to have a separate sheet for each project because i have large number.
Apologies for lengthy description, i wanted to make it as clear as possible to all.
Best regards,
Khaldoun Abu-Zeid.
I am preparing a sales budget for my boss. Main layout of my sheets is as follows: -
1- Sheet 1 is the basic data sheet will be the order booking forecast where in cell B3, i list forecasted projects name, C3, client name, D3 the material , E3 amount, F3 the expected order booking date & G3 the expected total delivery time for completing and closing the order and issuing the final invoicing date. cells H2 to S2 has the months of 2015 (Jan to Dec). The sheet should have a function to place the total of the forecasted order (E3) in the right cell (H3 to S3) based on the month of the expected date cell F3.
2- Sheet 2, shall include the forecasted Incoming CashFlow for each order from sheet 1 so that this incoming cashflow is shown in monthly totals Jan - Dec 2015. Column S shall have the total cashflow in 2015, balance shall be carried forward to 2016.
Remarks : Monthly incoming cashflow amounts do not follow general rule i.e. it could be a certain % based on progress / milestone (max. of 6 installments) and tied to certain dates. It could be 100% advance payment..... i.e. different scenarios.
Each row in Sheet 2 corresponds to one order from Sheet 1
3- Sheet 3 shall have the Outgoing Cashflow for each order projected on monthly totals (Jan Dec 2015) with Grand Total and Carry forward amount to 2016. These outgoing cashflows can be payments to suppliers, custom duties, shipping charges, logistics...etc.
4- Sheet 4 to show the net monthly cashflows and cumulative cashflows inorder to locate the approx. months where financing would be needed (when cashflow is negative)
5- Sheet 5 to show the forecasted invoicing dates based on order amounts and Expected Order Completion Dates from Sheet 1, showing monthly totals (Jan - Dec 2015), Grand Total for 2015 and C/F for 2016.
6-I need a Dashboard to show the monthly order booking forecasts, Cashflows (incoming / outgoing) and invoicing.
I need the sheet to be connected so that if i change the information any of the sheets, the change will be reflected on other worksheets and the whole workbook will re-calculate to give me accurate data.
Any Idea how to start this project ?! It difficult to have a separate sheet for each project because i have large number.
Apologies for lengthy description, i wanted to make it as clear as possible to all.
Best regards,
Khaldoun Abu-Zeid.
