ACCTG 331, Intermediate Accounting I

Simulation #1: Creating Balance Sheet and Income Statement in Excel (40 PTS)

REQUIRED: Given the data and information on the following page, use Excel to prepare a

balance sheet as of December 31, 2017 and an income statement using multi-step for the year

ended December 31, 2017 for Sprouts Farmers Market. Use the SUM function to total columns

or rows of data when appropriate. Otherwise use =, + or functions. Format the statements with

leading $ signs, underscore and double-underscore as appropriate. You are required to use the

attached template to complete your work.

GRADE RUBRIC: 30 points for properly completed statements and 10 points for use of Excel.

All students in your group will receive the same grade unless someone doesnt fully participate.

PARTICIPATION GRADE: Identify any members of your group who did not fully participate

in the assignment. If someone did not fully participate, identify the student and assign either 0 or

50% to their effort. Their score will be reduced accordingly.

SUBMISSION: Through Canvas Assignment tab.

Shown below is an adjusted trial balance for Sprouts Farmers Market, Inc. for the period ending

December 31, 2017

(with the exception of Accumulated Deficit, which shows the balance at December 31, 2016):

ACCOUNT BALANCE

COGS $ 1,264,514

Loss on sale of equipment 993

Accumulated Deficit (*) 20,351

Accumulated Depreciation 94,034

Inventory 98,382

Interest income 562

Property and equipment (gross) 393,200

Goodwill 408,078

Sales Revenue 1,794,823

Accounts Receivable (gross) 9,015

Additional paid-in-capital 435,480

Accumulated other

comprehensive income 2,000

Interest Expense 35,488

Loans from Bank (*) 584,802

Investments (*) 9,521

Note Receivable (*) 45,156

Selling, general and administrative expenses 93,495

Accounts Payable 82,721

Prepaid Advertising (*) 6,535

Intangible Assets (net) 196,772

Common stock 126

Amortization of trademarks 4,937

Cash and cash equivalents 67,211

Allowance for doubtful accounts 600

Direct store expenses 368,323

Accrued salaries 48,958

(*) Additional information:

The note receivable is structured such that Sprouts will receive the balance in two equal

annual equal installments with the first payment to be received on August 1, 2018 and the

second installment on August 1, 2019.

Management intends to hold onto the companys investments until December 2019 at

which point the investments will be sold.

The company paid out $10,000 in dividends to its shareholders in 2017.

Almost all of the loans from the banks are due in 3 years or more with the exception of a

90-day line of credit that has a balance of $5,167 on December 31, 2017.

Prepaid advertising is for newspaper marketing services that will be provided over the

first eight-months of 2018.

The company had 97,000 common shares outstanding on January 1, 2017. It issued an

additional 46,000 common shares on July 1, 2017.

Sprouts has a 43.91% income tax rate

Sprouts incurred aggregate losses since its inception, which resulted in an Accumulated

Deficit at the end of 2016.

Sprouts Farmers Market, Inc.

Balance Sheet On December 31, 2017

ASSETS

Current Assets:

Total current assets =

Noncurrent Assets:

TOTAL ASSETS =

LIABILITIES AND STOCKHOLDERS' EQUITY =

Current Liabilities:

Total current liabilities =

TOTAL LIABILITIES =

STOCKHOLDERS' EQUITY =

TOTAL LIABILITIES AND STOCKHOLDERS' EQUITY =

Beginning Retained Earnings =

Current Retained Earnings =

Dividends=

Retained Earnings =

all 4 of us in our group are getting different answers in various places, explanations with calculations would be especially helpful