Question

Income Statement Project

2018 2019 2020
Revenue:
Book Sales
Ticket Sales
Total Revenue:
Expenses:
Salary
Depreciation
Supplies
Rent Insurance
Total Expense:
Net Income/Loss:
Directions: Build an income statement using the steps provided below.
1) The book store received $50,000 in book sales for 2018, with a 20% increase in revenue each year.
2) Jack's book store received $15,000 each year in ticket sales to book signing events.
3) Find the Total Revenue each year for 2018-2020 using cell referencing.
4) Jack's book store paid $16,000 in employee salaries in 2018. Each year his employee salary cost increased by 25%.
*5) Jack purchased store furniture for $25,000 that is expected to be used over the next 5 years.
6) Jack bought $3,000 in supplies in 2018 and supplies costing $1,000 were used up each year.
'7) Jack signed a contract to pay $800/month for rent between 2018-2020.
8) Jack's book store pays $500 each month to cover insurance.
9) Find the Total Expense each year for 2018-2020 using the SUM function.
10) Find the Net Income/Loss using cell referencing.

Answer 1

Jack's Bookstore

Income Statement Projection:

2018 2019 2020

Revenue:

Book Sales $50,000 $60,000 $72,000

Ticket Sales 15,000 15,000 15,000

Total Revenue: $65,000 $75,000 $87,000

Expenses:

Salary $16,000 $20,000 $25,000

Depreciation 5,000 5,000 5,000

Supplies 1,000 1,000 1,000

Rent 9,600 9,600 9,600

Insurance 6,000 6,000 6,000

Total Expense: $37,600 $41,600 $46,600

Net Income/Loss: $27,400 $33,400 $40,400

Step-by-step explanation:

a) Data and Calculations:

Book Sales for 2019 = $60,000 ($50,000 * 1.20)

Book Sales for 2020 = $72,000 ($60,000 * 1.20)

Salaries for 2019 = $20,000 ($16,000 * 1.25)

Salaries for 2020 = $25,000 ($20,000 * 1.25)

Depreciation expense per year = $5,000 ($25,000/5) using the straight-line method

Supplies Expense per year = $1,000 ($3,000/3)

Rent Expense per year = $9,600 ($800 * 12)

Insurance Expense per year = $6,000 ($500 * 12)