Homework Assignments Week 1 – BSA105 Summer 2020

This week, we’ll create and format standard financial statements for the company.

Part1:  Prepare an Income Statement, a Cash Flow Statement, and a Balance Sheet.  Remember to save frequently as you work!

These financial statements are common to every business and while details may vary, each has a standard presentation format. In a new Workbook:

·  Prepare an income statement that looks like the one below.  Use the name you gave your business at the top.  Be sure to adjust your column widths.

o  The total and subtotals should be entered as formulas using relative cell references as we learned last week. Remember formulas start with =

§  Gross profit is revenue – COGS

§  Total expenses should be done using a function rather than writing a formula

§  Earnings before tax is gross profit – total expenses

§  Taxes is equal to 21% of the earnings before tax

§  Net earnings is the difference of earnings before tax and taxes

o  Use the border feature in the Font command group to create the lines

o  Note that some numbers are formatted as currency with no decimal places while others are simply numbers

·  Prepare a Balance Sheet that looks like the one below.

o  Assets are things the business owns, liabilities are things the business owes to others, and the shareholders equity section reflects the ownership capital of the business. Use columns E through K of the same worksheet as the Income Statement and Cash Flow Statement.

o  Use formulas for the Totals, do not just type in the figures shown.  Total liabilities & shareholder equity is the total of total liabilities + equity capital + retained earnings

·  Prepare a Cash Flow Statement that looks like the one below.  

o  Use columns M through O of the same worksheet as the Income Statement. 

o  Net Earnings was calculated on our Income Statement, use a cell reference to enter the information on the Cash Flow Statement. (=cell)

o  Depreciation & Amortization was also on our Income Statement, use a cell refence to enter it here

o  Use a formula to calculate Cash from Operations

Oops!  Looks like we have some errors to correct:

·  The heading for the Income Statement should say “For the Year Ended”  (not Ending)

·  There is an error in Sales Revenue, it should be $15,650,000

·  Look at the net earnings.  They are different than what we used in our projected executive compensation last week.  We need to update that.  

o  Add a new tab and label it Rev Executive Compensation

o  Open last week’s work and use the arrow in the upper left to select the whole worksheet.  Click copy.

o  Return to the new tab in this week’s workbook and click in cell A1.  Click Paste.

o  Change the number for projected annual net Revenue by using a cell reference to the Net earnings number in the Income Statement on the 2020 Financials tab. (notice that the spreadsheet should automatically recalculate the Bonus column and Total Annual Compensation Columns.  This is a key reason to use cell references rather than typing in numbers every time.

Now, let’s add some finishing touches:

·  Change the Label on the tab from Sheet1 to 2020 Financials

·  Next, let’s set the printing page breaks so that each statement prints on one page by itself. Set the print area, then adjust page breaks. (hint, use the print preview option next to the zoom slider in the bottom right corner).  Be sure to save the document with these print settings (I will look for them in your submission).

Part 2:  Prepare a 5-year horizontal presentation of the key financial statements.

·  Add a new tab and label it:  Financial Statements 5 years.

·  Set up the format for the new presentation.  You can copy/paste the text in column A from the financials you have already created:

·  Next, use cell reference to link the 2020 information on the new tab from the 2020 financial statements – BUT DO NOT DO THIS FOR THE CALCULATING FIELDS.  For things to work well, easiest, you will want to use copy/paste for the formulas (totals).  

·  Now you need to enter the information for the prior years:

·  Each cell that says “FORMULA” is where you should copy/paste the formula from the 2020 financials.  THEN, copy it across the row to the other four years so that all the figures will populate.

·  You also need to add the Prepared by:  with your name at the bottom in column A

·  You have a 5-year presentation of financials, but is really boring to read.  Let’s do some formatting!  Select the Heading and years (columns A – G) for each financial statement (not the content just the title and years) and make the ‘fill’ a nice medium to dark green color.

·  So that we read the words and years, re-select those areas and change the font color to white

·  Oh no!  The controller just found another error in the information we were provided.  Retained Earnings, should be 12,008,980.  Make the change on the 2020 balance sheet on the first tab and our use of cell references should mean everything updates automatically!

·  Well we need to be able to print this worksheet too.

o  Change the page orientation to landscape

o  Add a header:  Financial Statements: 5-year Presentation

o  Add a footer:  Page # of #

·  We also need to fix those page breaks, don’t we?  Let’s put each type of financial statement presentation on its own page (we’ll end up with 3 pages). You can check the print settings without actually printing by going to “file” “print” and looking at the print preview that displays on the screen.

·  SAVE!!!

Submit the new workbook to the assignment link for Week 2.