Need excel help. Angela Zithern is the owner and founder of Ye Olde Sweets, an online retail business. Angela buys candy that seems to have disappeared from shelves in stores but not from memories. She purchases the candy wholesale, sells it on her website, and then ships the sweets to customers.

Business is booming, and Angela needs to expand the business. She went to the bank to get a loan, and the loan officer told her that they first needed to see some financial statements. Angela is great at running the business, but she had no clue what the loan officer meant. She figured it was time to hire an accountant, and you were lucky enough to get the job.

Angela says to you, "Business is doing great, but it would be much better if we can get this loan so we can expand. Can you help me with making these financial statements the bank wants?"

"Sure," you reply, "You must have some sort of accounting system where you keep track of purchases, sales, and such. Do you have a printout showing your revenues and expenses?"

"Yes, I just printed it this morning," Angela says. "This one says it is a trial balance. Will this work?" "Perfect! I'll get right to work on those financials," you announce.

Financial Statements (40 points)

Your first task as the new accountant of Ye Olde Sweets is to make a income statement and balance sheet from the trial balance that Angela gave you (which is in a separate file on Blackboard). This is what is required for the financial statements:

Name of the company

Name of the financial statement

Date of the financial statement

oIncome statement would be "Year Ended December 31," and then use the year as the column header

oBalance sheet would be "As of December 31," and then use the year as the column header

The income statement needs to be multiple-step, and the balance sheet needs to be classified. (Do you still have your Financial Accounting 1 book? We learned about these in that class.)

Accounts need to be in the proper place for their respective statements.

Include headers where appropriate (e.g. Assets, Liabilities, Equity, Expenses)

Include totals where appropriate (e.g. Total Assets, Total Liabilities, Total Operating Expenses)

Formatting

oBold/italicize where appropriate

oAccounting format, 2 decimals

oDollar sign on first number in column and on totals

For the balance sheet, you will also want a dollar sign on the first liability since it is the first number after the asset total.

oIndent account names where appropriate

oUse either borders or underlines where appropriate (Your choice between borders or underlines)

oAppropriate column widths and row heights

oOverall, make it look professional

Formulas

oThis task will probably only require SUM formulas. Use them where needed.

oThe balance sheet must balance.

Financial Statement Analysis (40 points)

You are really going to impress Angela with your analysis skills. You are going to make a horizontal, vertical, and ratio analysis using the financial statements you just made. This is what is required:

Make a copy of the financial statement workbook. (You don't want to overwrite what you just turned in).

oIn this copy, you will also need to copy the income statement and balance sheet worksheets. (You will need to make a vertical analysis on one and a horizontal analysis on the other.)

oRename the four worksheets to something like IS - Vertical, IS - Horizontal, BS - Vertical, and BS - Horizontal. They don't need to be exactly like this - the goal is to have it short but still descriptive.

Using one copy of the income statement and balance sheet, make a vertical analysis

oAdd a header to the column. Formatting should be consistent with the other headers (e.g. centered, bold, underlined)

oBorder/underline formatting should be consistent with the rest of the spreadsheet

oFormat the numbers as percentages with no decimals

On the income statement vertical analysis, add a pie chart showing the operating expenses for the current year

oThe title should be appropriate

oThe legend should have the proper series names

oYou can add labels if you would like, but it isn't required

oYou can use any style pie chart that you want - you don't need to use the one the book made you use

oPlace the pie chart below the income statement and make it an appropriate size

Using one copy of the income statement and balance sheet, make a horizontal analysis

oAdd a header to the column. Formatting should be consistent with the other headers (e.g. centered, bold, underlined)

oBorder/underline formatting should be consistent with the rest of the spreadsheet

oFormat the numbers as percentages with no decimals

On the income statement horizontal analysis, add a column/bar chart comparing the operating expenses from the prior year to the current year

oThe title should be appropriate

oThe legend should have the proper series names

oYou can add labels if you would like, but it isn't required

oYou can use any style bar chart that you want - you don't need to use the one the book made you use. Other styles, in my opinion, are actually better suited to displaying the information.

oPlace the chart below the income statement and make it an appropriate size

Add a new worksheet in the workbook for the ratios. You can name the sheet Ratios or something similar.

oPut a header similar to the financial statements on this sheet (company name, date, title)

oPut the ratios in three groups similar to what the book does (profitability, liquidity, solvency)

oUsing the financial statement values, calculate the 10 ratios that the book shows. Some you can calculate for both years; others you can only calculate for the current year.

Return on owners' investment (current year only)

Return on total investment (current year only)

Profit margin (both years)

Gross margin (both years)

Current ratio (both years)

Quick ratio (both years)

Receivable turnover (current year only)

Inventory turnover (current year only)

Debt-to-equity (both years)

Liability (both years)

oFormat the ratios appropriately, whether it is decimal, percentage, or something else

What Does It Mean?

"You did a good job with this," says Angela. "I like the vertical and horizontal analyses. The vertical shows me where my money is going. The horizontal easily shows me if I am improving from year to year. I'm a little confused on the ratios, though. What do they all mean?"

"Let me tell you..."

Explain the Ratios (30 points)

Explain the three different categories of ratios and what they mean generally

oWhat do they measure?

oHow can Angela use them to analyze her business?

Explain the following ratios in more detail:

oProfit margin

oCurrent ratio

oReceivable turnover

oLiability

What are the strengths of the business? Are there any problems or red flags?

Use correct grammar, punctuation, and spelling

Capital Expenses

"Thank you for helping me understand those ratios," says Angela. "We have some good news! The bank approved the loan. We can now get the equipment that we need to expand the business."

"That is good news," you respond.

"I suppose our net income this year will be negative since the equipment is going to be a huge expense this year. Next year, though, we'll have income again because we won't have this big equipment expense."

You say, "Not necessarily..."

You explain to Angela the concept of spreading out the expense over several years and tell her that this is called depreciation.

"Wow, I'm glad I have you. I had no idea about this," Angela says. "Is the expense going to be the same every year, or does it change?"

"Well, there are different methods of spreading the expense out. You can make it the same every year, or you can choose a different method that makes it different every year. I'll put together a spreadsheet to show you the different ways you can choose."

"I look forward to seeing it. Here is a list at what I am looking at buying."

Depreciation Schedules (40 points)

Here is the list of things Angela is looking at purchasing:

1.Angela needs a new laptop computer that will cost $1,500. You guess that the computer will last about 3 years. At the end of the 3 years, it is just going to be tossed (no salvage value).

2.Along with the computer, she needs a printer. She needs a pretty nice one that can handle a large load, so it is going to cost $2,600. It will most likely last 5 years, and then she will throw it away and get a new one (no salvage value).

3.She is also looking at a small warehouse to store the candy. She is looking at basic economical models; a 30x40 foot pre-engineered one will cost $16,900. After doing some research, you figure the building would last around 25 years. The salvage value of that much steel is about $500.

This is what is required:

Create depreciation schedules for the three assets assuming they are all purchased on 1/1/2018. Create schedules for the straight-line, double-declining balance, and sum-of-the-years-digits methods.

Make a summary sheet showing the depreciation for 2018-2022 (5 years). The individual depreciation schedules must show all years that the asset will be in service. The summary sheet must be linked to the individual depreciation schedules.

Make line graph on the summary sheet showing the difference in depreciation methods for the printer. Put the graph below all of the summaries and make it an appropriate size/format.

Ye Olde Sweets T rial Balance As of December 31, 2016 2017 Dr. Cr. Dr. Cr. Cash 25,369.38 19,446.64 Accounts Receivable 55,627.27 66,544.60 Allowance for Doubtful Accounts 5,510.00 6,310.00 Supplies 2,560.30 6,338.50 Inventory 40,250.69 48,564.25 Prepaid Rent 3,071.43 3,072.50 Prepaid Insurance 4,037.33 4,037.33 Short-term Investments 24,200.00 31,650.00 Equipment 2,550.00 2,550.00 Leasehold Improvements 10,200.00 10,200.00 Accumulated Depreciation 2,380.20 4,591.10 Accounts Payable 12,487.30 15,130.68 Wages Payable 2,495.10 3,316.55 Sales Tax Payable 2,702.63 3,013.14 Unearned Revenue 1,560.00 1,260.00 Current Portion of Long-term Notes Payable 5,000.00 5,000.00 Long-term Notes Payable 15,000.00 10,000.00 Common Stock 15,000.00 15,000.00 Additional Paid-in Capital 50,000.00 50,000.00 Retained Earnings 30,561.11 55,731.17 Dividends 12,000.00 18,000.00 Sales Revenue 648,632.17 723,154.47 Interest Revenue 1,210.83 1,424.66 Cost of Goods Sold 473,980.16 529,920.29 Advertising Expense 8,385.84 9,623.01 Bad Debts Expense 10,985.01 4,824.31 Depreciation Expense 2,210.90 2,150.69 Income Tax Expense 7,256.03 7,360.10 Insurance Expense 12,112.50 12,112.50 Interest Expense 750.49 725.36 Meals and Entertainment Expense 2,661.49 4,410.60 Miscellaneous Expense 1,875.17 2,639.61 Rent Expense 12,285.73 12,290.69 Repairs Expense 5,980.47 6,030.62 Utilities Expense 3,698.25 4,989.26 Wages Expense 64,870.33 80,230.44 Website and Hosting Expense 5,620.57 6,220.47 Total 792,539.34 792,539.34 893,931.77 893,931.77