Creating a spreadsheet

[Guest]

Hi there,
I am a beginner at using Excel.
I have a scenario for all of you experienced Excel users out there and I
hope that someone can help me.
I need to assess the benefits and potential cost savings associated with
installing videoconferencing capabilities at each of a firm's six locations.
The cost to establish each video conference facility is $60,000.00 and each
facility will have an annual operating and support cost of $20,000.00. In the
first year the videoconferencing system will save approximately $400,000.00
in travel expenses.
The savings are expected to increase at a rate of 5 percent per year.
Now - I have been asked to develop a spreadsheet to analyze the costs and
savings over a three-year period.
What would be the most efficient way of developing a spreadsheet for the
company? Can I do it all on one spreadsheet or should I develop a workbook?

 

[Fred Smith]

Do it the same way you would do on paper.

Create columns with the year, expense, savings, net benefit.

One spreadsheet will definitely be sufficient for this exercise.

 

[Guest]

Hi Decorte. You're doing a classic capital investment analysis. Use column
A for your capital, expense and revenue items. Columns B, C and D and E will
be years. Use Row 1 for Column Headings. For example:
A B C D
E
Row 1 - Items Year 0 Year 1 Year 2 Year 3
Row 2 - Annual Savings $400K $420K $441K
Row 3 - Annual Costs 120K 120K 120K
Row 4 - Annual Return 280K 300K 321K
Row 5 - Initial Investment 360K
Row 6 - Annual ROI 78% 83%
89%
Row 6 - Total ROI 250%
Row 7 - Payback Period 15 Months

This project is a sure fire winner, if your assumptions are correct. Hope
it was your idea. These analyses can get as complicated as you'd like by
breaking down each of the items into their component parts. Also, Excel has
a number of functions for analyzing return on investments. Click on the
function wizard and choose Financial. HTH

 

[Guest]

Thanks to both of you for your wonderful replies. It is greatly appreciated!