For this assignment, you need to complete this spreadsheet.

Please make your OWN COPY of this spreadsheet in order to edit it. Please see Completing Assignments from Google Sheet Template for how to make your own version of this sheet that you can edit and submit.

See bottom for tips on how to use functions in a spreadsheet to make these calculations quick, easy, and accurate.

Part 1: Financial Statements & Ratios

See Acme Corp's financial statements for last year and industry ratios are provided in 202 Part 1 & 2 Assignment Template.

(1-1) Calculate and fill in Acme Corp's Financial ratios.

• If you used spreadsheet formulas to calculate your answers in the "Acme Corp's Ratios" column, you do not need to complete the "Calculation" column. If you use a calculator, please type in the formula and numbers you used to find your answer.

(1-2) Compare each of Acme Corp's ratios with the industry average - given the ratio, is Acme Corp doing better or worse than the rest of the industry? (One word in this column is acceptable.)

(1-3) Interpret and explain what each ratio means - what does this ratio tell you about a company?

(1-4) How is Acme doing compared to the rest of the industry? What are their strengths or what can they do to improve?

Part 2: Time Value of Money

See the Time Value of Money tab in 202 Part 1 & 2 Assignment Template.

(2-1) Gringotts Bank is offering a $50,000 amortized loan. The loan is to be repaid in five equal payments at the end of the next five years. Gringotts will charge 7% interest on the amount of the loan balance that is outstanding at the beginning of each year.

Complete the Loan Amortization Schedule. How much needs to be repaid each year? (Highlight or bold your answer.)

Hint: Review section 4-6 and table 4.2 in the textbook.

(2-2) Today (January 1), you deposit $500 into a savings account that pays 7% annual interest.

Complete the Savings Balance table by calculating how much money you would have in your savings account each year for 10 years if interest is compounded:

1. Annually

2. Quarterly

3. Monthly

Hint: Dont forget to change the rate and n for parts b and c! (See section 4-5a.)

Tips and Hints for using Spreadsheet Formulas

The best way to find the payment (part 2-1) and future value (part 2-2) is to use a function within the spreadsheet to automatically calculate your answers.

How to type in a function:

Type in the function and then fill in the parameters (these are variables included within the parenthesis). The parameters in brackets are optional. Then hit enter and the cell will display the result of your function.

The best way to add in the parameters is to use a reference. A reference is the code of another cell. For example, the first cell on the top right is labelled A1. Lets say your rate is in cell A1. After typing in the first part of your function =PMT( you can click on cell A1 and this will be inserted into your formula. The number in this cell will then be used in your calculation. See links below to learn how to use $ to lock your cell references.

How to use Formulas

How to Copy a Formula to Multiple Cells

Functions used for this assignment:

2-1) Here is the format of the PMT function

=PMT(rate, numer_of_periods, present value, [future_value], [end_or_beginning])

• Use dollar signs in the cell identifier for the rate so that this reference will be locked.

• The number of periods should reflect the number of compounding periods (this will change if the savings is compounding annually, quarterly, or monthly).

• The present value is the negative balance of the beginning of the year.

• You can leave future_value and end_or_beginning blank.

2-2) Here is the format of the FV function:

=FV(rate, nper, pmt, [pv], [type]

• If you use this function, you should enter the PV as a negative number (you are giving this money out).

• The payment will equal 0 because you are not adding money in every year.

• You can leave type blank.

For parts B and C, you need to divide your rate by the number of compounding period per year so that your function will use the rate per period for calculations (see section 4-5a for why this needs to be done). You can use this with a / within the formula. If your rate is 8%, compounding monthly, and it is in cell B12, you will type in B12/12 as the rate in your formula (or $B$12/12 if you are locking the reference).

How to use FV function

> First Project Assignment 5 202 Part 1 & 2 Assignmen X + Copy of 202 Part 1 & 2 AS X PDF - Google Drive C Get Homework Help With x | + docs.google.com/spreadsheets/d/1J17oqjyXbo4zALGLKVRUkHOp7y|ZR72B5Vn9n57A3hM/edit#gid=149153185 Copy of 202 Part 1 & 2 Assignment Template File Edit View Insert Format Data Tools Extensions Help ^ Ha Share J C5 Last edit was made on March 20 by Cornelia Alsheimer-Barthel Y 50% View only 31 A1:E1 fx Acme Corporation E G K L M N . 2 Acme Corporation Balance Sheet December 31, 2019 Acme Corportation Income Statement For the Year Ended December 31, 2109 3 (1-1) Calculation (1-2) Compare (1-3) Explain Sales COGS Gross Profit $4,290,000 3,580,000 710,000 o Assets Current Assets Cash A/R Inventory Total current assets $72,000 439,000 894.000 1,405,000 Liabilities Current Liabilities Accts & Notes Pay. Accruals Total Current Liabilities Long-term debt Total Liabilities $432,000 170,000 602 0001 404,290 1,006,290 Financial Ratios (1-1) Industry Ratios Acme Corp's Ratios Quick ratio Current ratio 2.7 Inventory turnover 5.8 Days sales outstanding 32 days Fixed assets turnover 13 Total assets turnover 26 Return on assets 9.10% Return on equity 18.20% Debt ratio 50% Profit margin on sales 3.50% 10 11 Operating Expenses Selling Expenses Administrative Expenses Depreciation Misc. Expenses Operating Profit (EBIT) 12 121,320 115,000 159,000 134.000 180.680 Long-term Assets Land and bldg Machinery Other Fixed Assets Total Long-term Assets 1a + 238,000 132.000 61,000 431,000 Owner's Equity Retained earnings Common stock Total Owner's Equity 15 16 17 254,710 575,000 829,7101 Taxes 72,272 Industry average ratios have been constant for the past four years. Based on year-end balance sheet figures (1-4) How is Acme doing compared to the rest of the industry? What are their strengths/what can they do to improve? 19 19 Total Assets $1,836,000 Total Liabilities & Owner's Equity $1,836,000 Net income $108,408 Add 1000 more rows at bottom. > Financial Statements & Ratios Time Value of Money > First Project Assignment 5 202 Part 1 & 2 Assignmen X + Copy of 202 Part 1 & 2 AS X PDF - Google Drive C Get Homework Help With x | + docs.google.com/spreadsheets/d/1J17oqjyXbo4zALGLKVRUkHOp7y|ZR72B5Vn9n57A3hM/edit#gid=149153185 Copy of 202 Part 1 & 2 Assignment Template File Edit View Insert Format Data Tools Extensions Help ^ Ha Share J C5 Last edit was made on March 20 by Cornelia Alsheimer-Barthel Y 50% View only 31 A1:E1 fx Acme Corporation E G K L M N . 2 Acme Corporation Balance Sheet December 31, 2019 Acme Corportation Income Statement For the Year Ended December 31, 2109 3 (1-1) Calculation (1-2) Compare (1-3) Explain Sales COGS Gross Profit $4,290,000 3,580,000 710,000 o Assets Current Assets Cash A/R Inventory Total current assets $72,000 439,000 894.000 1,405,000 Liabilities Current Liabilities Accts & Notes Pay. Accruals Total Current Liabilities Long-term debt Total Liabilities $432,000 170,000 602 0001 404,290 1,006,290 Financial Ratios (1-1) Industry Ratios Acme Corp's Ratios Quick ratio Current ratio 2.7 Inventory turnover 5.8 Days sales outstanding 32 days Fixed assets turnover 13 Total assets turnover 26 Return on assets 9.10% Return on equity 18.20% Debt ratio 50% Profit margin on sales 3.50% 10 11 Operating Expenses Selling Expenses Administrative Expenses Depreciation Misc. Expenses Operating Profit (EBIT) 12 121,320 115,000 159,000 134.000 180.680 Long-term Assets Land and bldg Machinery Other Fixed Assets Total Long-term Assets 1a + 238,000 132.000 61,000 431,000 Owner's Equity Retained earnings Common stock Total Owner's Equity 15 16 17 254,710 575,000 829,7101 Taxes 72,272 Industry average ratios have been constant for the past four years. Based on year-end balance sheet figures (1-4) How is Acme doing compared to the rest of the industry? What are their strengths/what can they do to improve? 19 19 Total Assets $1,836,000 Total Liabilities & Owner's Equity $1,836,000 Net income $108,408 Add 1000 more rows at bottom. > Financial Statements & Ratios Time Value of Money