J
Jan
I am looking for advice to streamline creating a capital budget summary and
not sure of the best method.
Current format: Each business unit (BU) in the division has a separate
worksheet in the workbook. Each worksheet is designed for BU to list their
capital equipment requests for the coming year. The worksheets are not
identical; at least in regards to number of rows. Column labels are
identical: fleet code, equipment item, acquisition cost, annual depreciation
or lease cost. The worksheet has been designed to list equipment needs by
month.
Example:
Row 3 = January.
Beginning with row 4, BU lists equipment items to be acquired in January.
Each piece of equipment is listed in a separate row. After the January
equipment needs are listed, next row will be titled February (A?); however
other cells in same row will sum acquisition cost, depreciation and least
costs for January in respective columns. Next row lists equipment needs to
be acquired in February and the process continues for each month of the year.
I have thought about redesigning in a list format. Columns to capture BU,
fleet code, equipment item, acquisition cost, month of acquisition, etc. BU,
Fleet Code, and equipment item columns would have data validation to insure
consistent data entry. The summary worksheet will not have to show BU name,
but will need to count all like equipment (fleet code), and sum acquisition
cost, depreciation or lease cost.
Is it possible in Excel to extract from the list and place on a separate
worksheet, the count of like equipment (fleet code) with equipment item, and
sum acquisition, annual depreciation or least costs? In other words, if
there are 6 rows for air compressors with an acquisition (acq) cost of $1000
and an annual depreciation of $48 each, the extract would show 1 row with
Fleet code, equipment item and the sum of acq ($6000) and sum of annual
depreciation of $288. Would this be possible with and Advance Filter?
Any advice would be greatly appreciated.
TIA
Jan
not sure of the best method.
Current format: Each business unit (BU) in the division has a separate
worksheet in the workbook. Each worksheet is designed for BU to list their
capital equipment requests for the coming year. The worksheets are not
identical; at least in regards to number of rows. Column labels are
identical: fleet code, equipment item, acquisition cost, annual depreciation
or lease cost. The worksheet has been designed to list equipment needs by
month.
Example:
Row 3 = January.
Beginning with row 4, BU lists equipment items to be acquired in January.
Each piece of equipment is listed in a separate row. After the January
equipment needs are listed, next row will be titled February (A?); however
other cells in same row will sum acquisition cost, depreciation and least
costs for January in respective columns. Next row lists equipment needs to
be acquired in February and the process continues for each month of the year.
I have thought about redesigning in a list format. Columns to capture BU,
fleet code, equipment item, acquisition cost, month of acquisition, etc. BU,
Fleet Code, and equipment item columns would have data validation to insure
consistent data entry. The summary worksheet will not have to show BU name,
but will need to count all like equipment (fleet code), and sum acquisition
cost, depreciation or lease cost.
Is it possible in Excel to extract from the list and place on a separate
worksheet, the count of like equipment (fleet code) with equipment item, and
sum acquisition, annual depreciation or least costs? In other words, if
there are 6 rows for air compressors with an acquisition (acq) cost of $1000
and an annual depreciation of $48 each, the extract would show 1 row with
Fleet code, equipment item and the sum of acq ($6000) and sum of annual
depreciation of $288. Would this be possible with and Advance Filter?
Any advice would be greatly appreciated.
TIA
Jan