Select Page

Your Perfect Assignment is Just a Click Away

Starting at \$8.00 per Page

100% Original, Plagiarism Free, Customized to Your instructions!

## Excel project. Complete in 6 hours

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 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.

"Place your order now for a similar assignment and have exceptional work written by our team of experts, guaranteeing you A results."

## Our Service Charter

1. Professional & Expert Writers: Eminence Papers only hires the best. Our writers are specially selected and recruited, after which they undergo further training to perfect their skills for specialization purposes. Moreover, our writers are holders of masters and Ph.D. degrees. They have impressive academic records, besides being native English speakers.

2. Top Quality Papers: Our customers are always guaranteed of papers that exceed their expectations. All our writers have +5 years of experience. This implies that all papers are written by individuals who are experts in their fields. In addition, the quality team reviews all the papers before sending them to the customers.

3. Plagiarism-Free Papers: All papers provided by Eminence Papers are written from scratch. Appropriate referencing and citation of key information are followed. Plagiarism checkers are used by the Quality assurance team and our editors just to double-check that there are no instances of plagiarism.

4. Timely Delivery: Time wasted is equivalent to a failed dedication and commitment. Eminence Papers are known for the timely delivery of any pending customer orders. Customers are well informed of the progress of their papers to ensure they keep track of what the writer is providing before the final draft is sent for grading.

5. Affordable Prices: Our prices are fairly structured to fit in all groups. Any customer willing to place their assignments with us can do so at very affordable prices. In addition, our customers enjoy regular discounts and bonuses.

6. 24/7 Customer Support: At Eminence Papers, we have put in place a team of experts who answer all customer inquiries promptly. The best part is the ever-availability of the team. Customers can make inquiries anytime.