Starter File: A6 Starter.xlsxDownload A6 Starter.xlsx
For the past few chapters we have been teaching you the fundamentals of Excel (like basic formatting and the proper usage of absolute and mixed cell referencing) and we are now counting on you to remember them and use them throughout the remainder of the semester. For instance, if you create a formula or a series of formulas that could use Mixed referencing but you instead use Absolute referencing even though it isn’t required then you will lose a point or two even though the step may not explicitly state to “Use proper cell referencing to do blah…”. Also, we are counting on you to know how to format cells with big numbers and/or those containing money. Also, please make sure to double-check your submitted file to make sure that you submitted the file that you meant to. If you submit an empty or almost empty file and we grade it, you will end up with a score that corresponds to what you submitted. An empty file will result in a score of 1/50.
The Beverage Holder division at Armstrong Manufacturing is looking into a new piece of equipment that was developed in Europe to manufacture their new insanely hotline of Zeusy Tumblers more precisely and less expensively than the current technology being used at Armstrong. The cost of the machine plus installation is estimated to be $93,925. The projected cost savings are expected to be $2.08 per tumbler (put this positive value into B13 on your starter file).
You have been asked to estimate a projected cash flow analysis that will be generated by this proposed project over the next four years. Will this cost expenditure be a good idea? Will the purchase of it be a good idea this year? How about 4 years down the road? How will the cost of this loan affect their cash flow over the next 4 years?
Complete the following:
1. Download the starter file and name it A6 LastName FirstName.xlsx (where LastName is YOUR last name and FirstName is YOUR first name).
Formatting: As far as how to format the numbers on this CashFlow page, Please use Accounting format with two decimal places and $ signs on the summary rows (rows 6, 17, 20, and 23). All other cells should be accounting with NO $ signs and 2 decimal places. There is no requirement to use the ROUND formula to round any numbers in this assignment unless specifically asked to do so (like in step 2).
Also, for most of the financial calculations in this module, there is a parameter at the end of the formula called “Type”. This parameter tells the formula whether the loan payment is timed to be made at the end of the loan period (parameter setting = 0) or at the beginning of the period (parameter setting = 1). For the purposes of this course, for simplicity sake, always assume a value of 0 (except for the extra credit problem where you use the value of 1).
2. (3 pts) The sales volume for the first year is estimated to be 2,083 units. Enter this value into C4. This value is projected to rise by 41% each year. In the Name Manager section of the Formula ribbon, create a named range called ProjectedGrowth that refers to this value of 41% (without referring to any actual cell that contains that value). In cells D4, E4, and F4 create formulas that calculate the estimated sales volume for each subsequent year. Since it is an estimate it should be rounded to the nearest 10. For example, if your formula in D4 estimated a sales volume such as 3008.7, your round function would be round this calculated value to 3010. Note that all values should be calculated by formulas for full credit!
The selling price for the first year (cell C5) is estimated to be $22.03 and is projected to rise by 3.5% each year due to inflation. Note that the values should be calculated by formulas and the 3.5% should be another named range named “Inflation”. The selling price values for years 2, 3 & 4 should be rounded, not just formatted, to the nearest penny (to 2 decimal places as opposed to nearest 10 like in the previous paragraph for the Sales Volume!).
Calculate the Gross Revenues for the 4 years in cells C6:F6.
3. (4 pts) Enter the values of -$11.93 for the cost of goods sold in B10, -$0.83 for the marketing cost in B11, -$1.24 for the shipping cost in B12. The purchase of this equipment is expected to cut your manufacturing costs by $2.08 per tumbler so enter that amount into cell B13. Note this is a POSITIVE number since it is additional new money coming into the company. For formatting, do not include the $ signs in these cells, I provided them here just for the clarification (that the costs (B10:B12) are negative dollar amounts and the savings (B13) is a positive dollar amount). Please note that some of these values are negative and some are positive due to their cash flow directions.
For each of these rows 10-13, calculate the yearly costs associated with each item out into columns C through F. If you create your formula in C10 correctly, with proper referencing, that formula will be able to be copied down and across to F13!
4. (6 pts) In cell C15, calculate the cumulative interest expense for year 1 (the interest portion of the 12 loan payments for the corresponding year). You should reference cells J3 through J7 as needed as opposed to hard-coding values into your formulas. Assume the loan will start at the beginning of year 1, and all payments will be made at the end of each period. Write your formula so that it can be copied across the row to automatically calculate these values for years 2 through 4. Make sure to format the cells so that they are accounting format with 2 decimal places, no $ signs and that negative numbers appear in parentheses. For instance, (2582.92)
5. (4 pts) Depreciation: The equipment is assumed to have a 5-year life with a salvage value of $55,000 at the end of that 5 year period. You will use the Straight Line Depreciation (SLN) formula in cell C16 of your cashflow worksheet to do this.There is a separate worksheet named Depreciation that contains the cells needed for this depreciation calculation. First name each of the cells containing the original equipment cost (B3), the salvage value (B4), and the expected life of the equipment, B5, as Capital, Salvage and Life respectively. For the “Capital” cell, B3, have this cell refer to cell J3 on the CashFlow worksheet so that if the loan amount changes on the front page this depreciation calculation automatically updates as well.
After naming these 3 cells, back on the cash flow worksheet on row 16, calculate the depreciation for this new equipment using the Straight Line depreciation method.
As in the prior step, make sure to format the cells so that negative cashflows are calculated as negative numbers and appear in parentheses. Hint, these values should be negative (and identical numbers!) since we get to deduct the cost of the depreciation of this piece of equipment for each of these 4 years.
6. (5 pts) On the cash flow worksheet on row 17, calculate the Taxable Income for all years. For a particular year, this will be the Gross Revenue in row 6 as well as all of the costs and costs savings in rows 10-16. You can just include all rows C6 through C16 in your SUM function. Make sure to format the cells and create the formulas so that negative cash flows are calculated as negative numbers and appear in parentheses. If you get all of the cash flow directions correct then all you SHOULD have to do here is simply SUM the cells in rows 6 through 16 for each year to get the right taxable income!
7. (4 pts) Corporate Income tax calculations: If you add up the current federal and the Florida state income taxes for a business these days, the tax rate can be roughly approximated at 33%. This value is already included in cell B18 for your convenience.
On the CashFlow worksheet, on row 19, for each year, calculate the corporate income tax that would be owed for each year by multiplying the tax rate by the taxable income. Please keep in mind that IF (Hint: it sounds like you’ll need an IF statement here) your taxable income for any given year is 0 or a negative number you will owe $0 in taxes. If you have a POSITIVE value for the taxable income you will owe that 33% of your income to the government. Create the formula that accomplishes this and copy it across to F19.
8. (4 pts) In row 20, calculate the income after taxes for each year. Make sure to format the cells so that negative cash flows are calculated as negative numbers and are formatted to appear in parentheses.
9. (6 pts) Complete the worksheet by (in row 21) adding back in the depreciation that was previously deducted. Then in row 22, calculate and add in the cumulative principal payments for the corresponding year in order to arrive at a final projected cash flow estimate for each of the four years. Use the correct absolute/mixed/relative cell referencing so that your formulas will work for each of the cash flow years by referring to the Loan Information cells in J3 – J7. Make sure to format the cells so that negative cashflows are calculated as negative numbers and appear in parentheses for example… (17,012.04)
In cell C23 create a formula that finalizes the Projected Cash Flow for each year by adding C20 through C22. Copy this formula to the right for all years.
10. (10 pts): Amortization Schedule:
10.1 As we may or may not know, you CAN get a mortgage with a zero/non-existent credit rating so it is a myth that you “have to go into debt in order to build your credit rating”. However, if you DO have credit cards/loans etc. and you do have a credit rating, the better your credit rating you have, the lower your interest rate on your loan. To this end, cell B2 contains text that you should put in to indicate your current credit rating (either Poor, Fair, Good or Excellent). For the purposes of this exercise put in the word Good (not case sensitive).
(10.2) In cell B4 now create a VLOOKUP formula like you have done a few times this semester that takes that value, finds it in the lookup table contained in cells G2:H5 and returns the second column of that table that contains the interest rate needed for the remainder of this exercise. You will need to include the last parameter that indicates an EXACT lookup.
(10.3) Cells B2:B7 contain all the input data needed for this exercise. You will need to change the Loan Duration (yrs) cell B4 in a later step but make sure to change this cell back to 30 prior to submission your grader will be looking for values associated with a 30 year loan.
(10.4) In cell B9 create a formula that calculates the monthly payment for input values provided. Format cells B3 & B9 with Accounting format with $ signs and 2 decimal places.
(10.5) Per the video demonstrating how to make an Amortization Schedule (FSU Spreadsheets Lesson6 Level2 Part1) create a formula in B12 that references B3 and format it for Accounting and 2 decimal places (with a $ sign since it is on the top row)
In cell B13 create a formula that adds B12 to the value in D12 (the formula for D12 will be calculated in a step below. Wrap this formula in an IFERROR formula that will return a 0 in case either B12 or D12 return an error. Format D13 as Accounting with no $ sign and copy it down to 360th pay period (row 371).
(10.6) In cell C12 create a formula that, for the 1st payment, calculates the amount of interest that is paid on that monthly payment. Wrap this formula in an IFERROR so that if the formula returns an error it will return a 0. Copy the formula down from C12 to the bottom of the table (row 371). Format C12 as Accounting with $ signs and 2 decimal places. As we have done on prior assignments I only want the $ sign on the top row of this table so format C13 on down as the same but without the $ sign.
(10.7) In cell D12 create a formula that, for the 1st payment, calculates the amount of principal that is paid on that payment. Wrap this formula in an IFERROR so that if the formula returns an error it will return a 0.
Copy the formula down to the 360th payment period.
Format D12 as Accounting with $ signs and 2 decimal places. Format D13 on down as the same but without the $ sign.
(10.8) In the totals row at he bottom of your masterpiece in B372 copy the formula down from B371 so that B372 adds the last monthly remaining principal in B371 to the principal paid in D371. If you do everything right to date that value should calculate to a value of 0 since the amount remaining at the end of the loan SHOULD be 0!
(10.9) It the adjoining cells C372 and D372 create formulas that sum the above cells in the amortization schedule. C372 will add up all of the interest that you paid over the life of the loan and if you performed all of your calculations correctly then D372 should equal the original loan amount. For both of these values ROUND these values to the nearest 1 cent (2 decimal places).
(10.10) In cells E2:E5 We want to compare and contrast the amount of interest we will pay for 4 different loan durations. Using the copy and then paste special values function that we learned back in Module 2, copy the Total Interest Paid value from C372 and paste just the value into E5 so that E5 contains the interest paid on a 30 year loan. Change the loan duration in cell B5 to 20 years and then copy the freshly recalculated interest paid amount in C372 and paste it’s value into E4. Repeat for loan durations of 15 and 10 years into E3 and E2 respectively.
(10.11) Please give some thought to how much interest you will pay on a 30 year loan vs a 15 year loan. The quicker that you can pay off your loan, the sooner you truly be in a position to win with money. Life without a house payment is a truly liberating feeling that you can have sooner than you think if you make wise decisions along the way!
11. (4 pts) On the sheet titled Personal, the Armstrong Manufacturing wants to make sure that all of their employees know how important it is for their future that they know some basics about personal finance. According to bankrate.com, the average car payment in America today is now up to an astounding $735/month (for 68 months!). This is up from $579/month just last year. How much is that car payment costing you over the course of your life? Calculate what you would have in your retirement account if you instead of paying the bank that amount each month, you instead invested that $735/month into a mutual fund that gains 10% annually for 40 years. Remember that since that $735 is flowing away from you, that value in B1 should be a negative number. Assume that the payment is made at the end of the month (i.e. again make the “type” parameter = 0). Hint, don’t freak out if the amount that this perpetual car loan is WAY OVER $4,000,000!!! I wish I had learned this when I was your age 🙂
Now for some fun (and stretch your algebra skills a little lol): To help visualize how much money this is, you need to calculate how many stories tall this huge pile of money wasted on car loans woule be. I found that 1 million $1 bills is 4,300 inches tall. Given that fact, in cell B12 calculate how many feet tall your future stack of money will be if you don’t blow it on car loans. Format B12 to be Numeric with comma and 2 decimal places. In cell B14 create a formula that tells me how many stories tall this pile of money is. Round (nor just format) this value to the nearest whole number (0 decimal places).
The following example doesn’t use the exact numbers but illustrates the math for you. If B6 is $4,000,000 then that would be 17,200 inches or 1,433.33 ft tall. At 10′ per story that would be a 143 story building!
If I had only listened to Einstein when he stated “COMPOUND INTEREST IS THE EIGHTH WONDER OF THE WORLD. HE WHO UNDERSTANDS IT, EARNS IT. HE WHO DOESN’T, PAYS IT.”:
Step 12 – Extra Credit! Yet ANOTHER EXTRA CREDIT OPPORTUNITY – 5 points: As an example of how the compounding of interest can benefit you greatly, especially at an early age, see if you can figure out the following:
Zeus, on his 20th birthday started investing $2,000 annually on his birthday for just 9 years into an account earning 10% interest per year. If he doesn’t put in another penny after that initial 9 years, how much will he have in 40 years (at the ripe old age of 60)?
However, Ken, like most folks, found things to spend his money on besides saving for his retirement for a few years. Let’s say that he didn’t start investing that $2,000 until his 29th birthday. Even if he put in the whole $2,000 for the next 32 years how much will you have at the age of 60? What is the difference and what should you do in order to take advantage of this mathematical phenomenon? For this extra credit you will have to show your work and how you used the future value formula to figure out the path to success.
I used to do this in class so you can watch the F2F lecture for 2 different ways to do this one. You can do either one. You don’t have to do both.
Option 1) Create formulas in cells C4:C44 to come up with Zeus’ total when he wants to retire then repeat in cells G4:G44 to see how much Ken will have upon retirement. In cell K6 calculate the difference between the Zeus’s and Ken’s strategies.
Option 2) Create FV formulas in cells I3, J3, and K3. In cell K6 calculate the difference between the Zeus’s and Ken’s strategies (cells J3 & K3).
FYI… If you do both options 1 & 2, J3 should have the same number in it as C44 and K3 should be Ken’s total in G44.
Also, remember to set your “type” parameter to 1 for this Extra Credit.
11. Save and close the A6 – Cash Flow LastName FirstName.xlsx workbook. Submit this file into this assignment. Please note that if you put it into the wrong bucket, or you submit the wrong file, it will not be graded or may be graded as a 0 without the opportunity to make it up so please follow through with your assignment and verify that you uploaded what you meant to and it went where you meant it to go. There are too many of you for us to try to guess what you meant to do!
