Marissa's Discounters

Problem 1

This income statement is not formatted according to Generally Accepted Accounting Principles. It is set up to test knowledge of spreadsheet functions.

Year

2019

2020

Income

Cash Sales

250000

Credit Sales

Investments

8500

Rental Income

4500

Total

Expenses

Cost of Goods Sold

Operating Expenses

Sales and Marketing

Loans

Total

Gross Income

Taxes

Net Income

Net Income as % of Sales

General Requirements:

1. No numbers in ANY formulas. You must use cell references in your formulas.

2. No sub-formulas outside of the table. Formulas must be in the table only!!

3. Format all of your values and variables. Make them consistent with the information you are trying to describe so it is easy for your audience to read your information.

4. Make sure your charts have proper titles, dates, and unit of measure indicated in the titles.

Requirements:

A. Credit Sales for 2019 were 500% of cash sales.

B. Cost of Goods Sold for both years are 59% of Sales.

C. Sales and Marketing expenses for both years are 15.75% of Sales.

D. Operating expenses are 12.5% of Sales for both years.

E. Taxes are 21% of Gross Income, if Gross Income is more than $0. (Use an IF statement to calculate taxes.)

F. Determine what the percent of Net Income is to Total Sales.

G. In 2020 we expect to take out a short term loan for renovations for $225,000 at 16% interest per year. We should be able to pay it

back within 4 years. (Use the Payment function (pmt ) and Absolute value function (abs) to calculate the Loan payment for 2020.)

H. Cash sales are expected to increase by 4.9% in 2020. We expect credit sales to remain in the same proportion as 2019.

I. Due to the volatility of the stock market and diversity of investments in our portfolit we anticipate a modest 3.75% gain in investments from 2019 to 2020.

J. Rental income will increase by 2.5% for 2020 by contract.

I. Our goal for net income as a % of sales is 7.5%. If we don't reach that goal we want to see that cell highlighted with a yellow fill background. (Use conditional formatting.)

J. What are some recommendations you would make to improve the bottom line for 2019? (Type in your answer in the rows below this question.)

K. Create a column chart to compare Total Income, Total Expenses, and Net Income for the 2 years.

This income ding lowly Preporod preken Yaar 2019 2020 no Cash 210000 282280 Credil Sale 1260 ment RO Retrome 600 Tot 1612000 BOX SPX 167 12.09 Expo Cal Goo Sold Opening Experien SwedMarting 2000 187800 Z250 TO 1308700 B0409 Total 2005 Gross noore To Net Income 16135 Menece a Sales 10 MON General Requirements: 1. No numbers in ANY formu You must use coll references in your formules 2. Ne sub formules outside of the table Formulas must be in the table only Forma of your and variable. Make them consistent with the information you we trying to describe to is easy for your audience to read your information Make sure your charts have propertiedad unit of measure indicated in the tale Requirements: A Credit Sales for 2019 were 500% of cash sales. B. Cost of Goods sold for both years e 50% of Sales a. Sales and Marketing expenses for both years we 15.75% of Sales D. Operating expenses are 12.5% of Sales for both years. E. Taxes are 21% of Gross income, Gross Income is more than 50. (Use an IF statement to calculate taxes.) F. Determine what the percent of Net Income is to Total Sales G. In 2020 we expect to take out a short term loan for renovations for $225.000 a 16% interest per year. We should be able to pay back within years. Use the Payment function () and Absolute value function (abs) to calculate the loan payment for 2020.) H Cash sales are expected to increase by 4.9% in 2020. We expect credit sales to remain in the same proportion as 2019, Due to the volumity of the stock market and diversity of investments in our portfot we anticipate a modest 3.75% gain investments from 2019 to 2020. Rental income increase by 2.5% for 2020 by contract 1. Our goal for net income a % of sales 7.5%. If we don't reach the gow we want to see that coll nigged with a yelow background. Use condition formatting 2. What are some recommandations you would make to prove the bottom line to 2019 Type your answer in the row below this question.) K. Create a comm chart to compare total income To Expenses and Net Income for the year Problemi Prihlam Del This income ding lowly Preporod preken Yaar 2019 2020 no Cash 210000 282280 Credil Sale 1260 ment RO Retrome 600 Tot 1612000 BOX SPX 167 12.09 Expo Cal Goo Sold Opening Experien SwedMarting 2000 187800 Z250 TO 1308700 B0409 Total 2005 Gross noore To Net Income 16135 Menece a Sales 10 MON General Requirements: 1. No numbers in ANY formu You must use coll references in your formules 2. Ne sub formules outside of the table Formulas must be in the table only Forma of your and variable. Make them consistent with the information you we trying to describe to is easy for your audience to read your information Make sure your charts have propertiedad unit of measure indicated in the tale Requirements: A Credit Sales for 2019 were 500% of cash sales. B. Cost of Goods sold for both years e 50% of Sales a. Sales and Marketing expenses for both years we 15.75% of Sales D. Operating expenses are 12.5% of Sales for both years. E. Taxes are 21% of Gross income, Gross Income is more than 50. (Use an IF statement to calculate taxes.) F. Determine what the percent of Net Income is to Total Sales G. In 2020 we expect to take out a short term loan for renovations for $225.000 a 16% interest per year. We should be able to pay back within years. Use the Payment function () and Absolute value function (abs) to calculate the loan payment for 2020.) H Cash sales are expected to increase by 4.9% in 2020. We expect credit sales to remain in the same proportion as 2019, Due to the volumity of the stock market and diversity of investments in our portfot we anticipate a modest 3.75% gain investments from 2019 to 2020. Rental income increase by 2.5% for 2020 by contract 1. Our goal for net income a % of sales 7.5%. If we don't reach the gow we want to see that coll nigged with a yelow background. Use condition formatting 2. What are some recommandations you would make to prove the bottom line to 2019 Type your answer in the row below this question.) K. Create a comm chart to compare total income To Expenses and Net Income for the year Problemi Prihlam Del