{"id":4298,"date":"2025-07-07T15:04:49","date_gmt":"2025-07-07T15:04:49","guid":{"rendered":"https:\/\/skilledprofessors.com\/?p=4298"},"modified":"2025-07-07T15:04:49","modified_gmt":"2025-07-07T15:04:49","slug":"capital-expenditure-analysis","status":"publish","type":"post","link":"https:\/\/writezilas.com\/essays\/capital-expenditure-analysis\/","title":{"rendered":"Capital Expenditure Analysis"},"content":{"rendered":"\n

Due \u2013 3\/ 12<\/strong><\/p>\n\n\n\n

Introduction<\/strong><\/p>\n\n\n\n

This project is an application of capital budgeting and cash flow analysis. The Excel template is similar to what I might build in the so-called real world, and could be very useful to have on a flash drive somewhere for future reference \u263a<\/p>\n\n\n\n

To make the project easier, I recommend using the template to build the example problem<\/em> before trying the actual problem in the template.  Once you have the example built and the numbers match the illustrations below, you know your spreadsheet works correctly. Then substitute the assumptions for the actual project and the spreadsheet will do the computations. Watch for differences in the assumptions for Epsilon vs Aguilera.<\/em><\/strong><\/p>\n\n\n\n

The Example <\/strong><\/p>\n\n\n\n

EXAMPLE SCENARIO:<\/em><\/p>\n\n\n\n

Aguilera Acoustics, Inc., (AAI) projects unit sales for a new seven-octave voice emulation implant as follows:<\/em><\/p>\n\n\n\n

Year<\/em> <\/em>Unit Sales<\/em><\/p>\n\n\n\n

1<\/em> <\/em>67,500    <\/em><\/p>\n\n\n\n

2<\/em> <\/em>83,900    <\/em><\/p>\n\n\n\n

3<\/em> <\/em>98,700    <\/em><\/p>\n\n\n\n

4<\/em> <\/em>86,000    <\/em><\/p>\n\n\n\n

5<\/em> <\/em>72,000    <\/em><\/p>\n\n\n\n

Production of the implants will require $1,500,000 in net working capital to start and additional net working capital investments each year equal to 15 percent of the projected sales increase for the following year. Total fixed costs are $1,950,000 per year, variable production costs are $230 per unit, and the units are priced at $355 each. The equipment needed to begin production has an installed cost of $18,500,000. Because the implants are intended for professional singers, this equipment is considered industrial machinery and thus qualifies as seven-year MACRS property. The accounting department supplies you with the following depreciation figures:<\/em><\/p>\n\n\n\n

Year<\/em> <\/em>MACRS Depreciation<\/em><\/p>\n\n\n\n

1<\/em> <\/em>2,643,650    (14.29%)<\/em><\/p>\n\n\n\n

2<\/em> <\/em>4,530,650    (24.49%)<\/em><\/p>\n\n\n\n

3<\/em> <\/em>3,235,650    (17.49%)<\/em><\/p>\n\n\n\n

4<\/em> <\/em>2,310,650    (12.49%)<\/em><\/p>\n\n\n\n

5<\/em> <\/em>1,652,050    (8.93%)<\/em><\/p>\n\n\n\n

In five years, this equipment can be sold for about 20 percent of its acquisition cost, and will have a book value of $4,127,350. AAI is in the 35 percent marginal tax bracket and has a required return on all its projects of 15 percent.<\/em><\/p>\n\n\n\n

Determine the Payback Period, Internal Rate of Return and Net Present Value of this project.<\/em><\/p>\n\n\n\n

Here\u2019s how the template would be populated with the information for Aguilera:<\/strong><\/p>\n\n\n\n

First, organize the inputs into one easy-to-navigate area. This facilitates changing figures if your assumptions change.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Second, create the working area. This is where the computations will happen. Every cell in this area is either a computation or a cell reference. I do not enter any data or type any hard numbers in this area.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

Notice the payback period: I don\u2019t normally use a fancy formula to automate the computation, it is too much trouble for the time savings.  At the end of Year 3, the company has recouped all but $823,517.50 of the initial investment. That amount divided by the next year\u2019s cash flow (823517.50 \/ 7274228) tells me the remainder is 0.11. Add that to the 3 full years I counted and I get the payback period of 3.11<\/p>\n\n\n\n

NOTE ABOUT CHANGE IN NWC<\/p>\n\n\n\n

The author bases his net working capital need on the anticipated sales increase for next year:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The final year, however, is when the total amount of NWC is no longer needed, so the company \u201creleases\u201d that total amount. The Year 5 formula is different:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Year 5 has a cash inflow of the total amount of NWC involved in the project (Notice the minus sign before the word SUM in the formula).<\/p>\n\n\n\n

The Project<\/strong><\/p>\n\n\n\n

Consider the following scenario:<\/p>\n\n\n\n

Epsilon Products, Inc., (EPI) projects unit sales for a new device as follows:<\/p>\n\n\n\n

Year Unit Sales<\/p>\n\n\n\n

1 87,500    <\/p>\n\n\n\n

2 105,000    <\/p>\n\n\n\n

3 119,000    <\/p>\n\n\n\n

4 108,000    <\/p>\n\n\n\n

5 92,000    <\/p>\n\n\n\n

________________________________________<\/p>\n\n\n\n

Production of the device will require $1,000,000 in net working capital to start and additional net working capital investments each year equal to 9 percent of the projected sales increase for the following year. Total fixed costs are $1,450,000 per year, variable production costs are $250 per unit, and the units are priced at $350 each. The equipment needed to begin production has an installed cost of $24,000,000. This equipment qualifies as seven-year MACRS property, and the accounting department supplies you with the following depreciation figures:<\/p>\n\n\n\n

Year MACRS Depreciation<\/p>\n\n\n\n

1 3,429,600 (14.29%)<\/p>\n\n\n\n

2 5,877,600 (24.49%)<\/p>\n\n\n\n

3 4,197,600 (17.49%)<\/p>\n\n\n\n

4 2,997,600 (12.49%)<\/p>\n\n\n\n

5 2,143,200 (8.93%)<\/p>\n\n\n\n

________________________________________<\/p>\n\n\n\n

In five years, this equipment can be sold for about 15 percent of its acquisition cost, and will have a book value of $5,354,400. EPI is in the 35 percent marginal tax bracket and has a required return on all its projects of 16 percent.<\/p>\n\n\n\n

Determine the Payback Period, Internal Rate of Return and Net Present Value of this project.<\/p>\n\n\n\n

Required<\/strong><\/p>\n\n\n\n

    \n
  • Use the Excel template on the class webpage (Capital Budgeting Template.xls) and analyze The Project, for Epsilon, above.<\/li>\n\n\n\n
  • You must complete this in Microsoft Excel, not another spreadsheet program (i.e. Do not use Google Sheets).<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"

    Due \u2013 3\/ 12 Introduction This project is an application of capital budgeting and cash flow analysis. The Excel template is similar to what I might build in the so-called real world, and could be very useful to have on a flash drive somewhere for future reference \u263a To make the project easier, I recommend…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"footnotes":""},"categories":[16],"tags":[],"class_list":["post-4298","post","type-post","status-publish","format-standard","hentry","category-blog"],"_links":{"self":[{"href":"https:\/\/writezilas.com\/essays\/wp-json\/wp\/v2\/posts\/4298","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/writezilas.com\/essays\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/writezilas.com\/essays\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/writezilas.com\/essays\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/writezilas.com\/essays\/wp-json\/wp\/v2\/comments?post=4298"}],"version-history":[{"count":0,"href":"https:\/\/writezilas.com\/essays\/wp-json\/wp\/v2\/posts\/4298\/revisions"}],"wp:attachment":[{"href":"https:\/\/writezilas.com\/essays\/wp-json\/wp\/v2\/media?parent=4298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/writezilas.com\/essays\/wp-json\/wp\/v2\/categories?post=4298"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/writezilas.com\/essays\/wp-json\/wp\/v2\/tags?post=4298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}