| | Tiger Paws LLC Income Statement for January 2018 |
| | The accounts and numbers in this worksheet are for education and Excel skills only and do not reflect Generally Accepted Accounting Principles. Expenses are not all |
| | inclusive and taxes do not consider federal taxes. Sales are net of returns, and sales taxes collected which are recorded and paid separately. |
| | Most of the time at work when we are assigned tasks they involve gathering data from multiple sources. Some data will be given to you, some you will need to research |
| | from inside and/or outside the company, and some you may have to create yourself with formulas and other mathematical tools. |
| | This is an example of one of those types of assignments. |
| | In this assignment you need to create an initial MONTHLY Income Statement for this company to see if they need to make any adjustments to reach their financial goals. |
| | As with any budget this one has some information that has been given to you, some you need to research, and some you need to calculate (create) using formulas. |
| | You also have to be careful that you convert ALL values to the same time frame. A major problem we have is consistency of data. |
| | Make sure your values are converted to the same unit of measure. |
| | To get started, create a new worksheet. Label it Variables. On this worksheet you should list all of the variables in one column, and values you are given, |
| | or find through your research, in the adjoining column. Third column you might want to place unit of measure of the data. |
| | There will not be ANY formulas in these variables. This is your data that you will use for problem solving later in the Income Statement. |
| | 1. When looking at the dry goods retail industry we discover that the average firm earns sales of $584,000 per employee per year on the payroll. |
| | That means if 1 person were on the payroll they would need to sell $584,000 worth of goods to cover their expenses, and make a normal profit for the year. |
| | As an example I would set up the variables this way: |
| | Variable | Value | Unit of Measure |
| | Sales | $584,000 | per person per year |
| | Continue on by listing all of the other variables below this one that you are given, or find from the steps below. |
| | There will not be ANY formulas in these variables. This is your data that you will use for problem solving later in the Income Statement. |
| | 2. It has been determined that the average space required for each person in an office is 250 square feet. This takes into consideration working office space, showroom, storage |
| | and utility areas, restrooms, breakroom, aisles, exits and all that are needed to run the business. |
| | 3. The following positions are part of this company. There are a total of 6 people in the office. |
| | | Use Salary.com to find the salaries for each of the 6 positions on the payroll. On the bell curve, use the 25% salary rate for zip code 10016. |
| | | In the cell containing the salary, insert a comment with the URL address of the position from salary.com. |
| | President who is also the Director of Marketing | | | When looking up the salary for this position look up Director of Marketing. |
| | Vice President who is also the Controller | | | When looking up the salary for this position use Controller. Don't be surprised if this salary is higher than the President. |
| | Sales Representatives (2) of varying experience. | | | Industry calls these positions Sales Representative I, and II. | | | | | | This means each person receives different salary. |
| | Receptionist or Administrative Assistant | | | You may use either job title. |
| | Purchasing Agent | | | Industry calls this position Production Scheduler Manager II |
| | 4. Listed below are a variety of expenses and how to estimate their cost for the month. |
| | Payroll Benefits | In addition to salary, companies pay an additional 13.25% in Social Security, disability insurance and other benefits. |
| | Rent | Determine the square footage you need based on the number of personnel. Use Loopnet.com to find the cost per square foot of the space |
| | | that you would need. You will not find the exact space, but rather a general rate per square foot in the area. Make sure you are looking at lease, not purchase. |
| | Communications | The industry standard is about 3.75% of sales. |
| | Advertising and Marketing | The industry standard is about 14.5% of sales. |
| | Supplies | The industry standard is about 2.75% of sales. |
| | Interest on the Loan | 8.33% | annual interest rate. |
| | Amount Borrowed | $550,000 |
| | Time to pay back the loan. | 7 | years |
| | Utilities | The controller was able to secure an average monthly contract with Con Ed for $6.50 per square foot per month. |
| | Cleaning and Maintenance | The controller found a cleaning service that will charge $7.45 per square foot each month. |
| | Health Insurance | For a plan that covers the employees only, has prescription drug, office visits, hospitalization, each person will be $900. per month. |
| | Professional Services | The industry standard is 6.5% of sales. The maximum the officers have determined they will budget is $40,000 per month regardless of sales. |
| | | Use the IF statement to calculate this item in the budget. The logic question then is 6.5% of Sales > $40,000? |
| | Miscellaneous Expenses | The industry standard is 3.5% of sales. |
| | Cost of Goods Sold | The controller has advised that after reviewing all contracts the CGS is presently 59.5% of sales. |
| | Tax Rate | Business flat tax rate is 21% of Gross Income. |
| | 5. Your Income Statement is for January 2018. |
| | All of the variables you use in the table should be on the worksheet with labels indicating what they are. There should not be any numbers in your formulas. |
| | All formulas should only contain cell references and not numbers. i.e. =B7 * Z45 not =9*15 |
| | Your Revenue is generated from Sales and Investment Income. You need to use the industry information given to you in item 1 to estimate the sales value. |
| | The Investment Income for the month of January is estimated to be $20,000. |
| | Payroll is the name of the expense that will be used in place of inserting each person's salary into the Income Statement. Your formula in this cell will add up the salaries and convert them to monthly. |
| | Loan Repayment is the name of the expense that will be used when calculating the cost of the monthly loan payment. |
| | Use the absolute function and the payment functions to calculate the repayment of this loan. |
| | 6. You are now ready to take the data to create a table that shows us the following information. Do not use a Microsoft Style for the table. |
| | Use the information from your variables above. Enter formulas where needed. There should NOT be any numbers in any formula, cell references only. |
| | I would suggest that you copy the statement format below to a row below your variables in the Variables worksheet to make it easier for you to construct your formulas for the problem. |
| | | | Tiger Paws LLC Income Statement |
| | | | For Month Ending January 2018 (in USD) |
| | Revenue | | | | | | | |
| | | Sales | XXX | Replace the XXX's with formulas and values. Follow the same format for Expenses. |
| | | Investments | XXX |
| | Total Revenue | | XXX |
| | Expenses |
| | | Payroll | XXX | In column C, insert each expense here from steps 3 and 4. List the expense in column C and the $ value in column D. |
| | | Payroll Benefits | XXX |
| | | Next Expense | XXX | Insert additional rows as needed. |
| | Total Expenses | | XXX |
| | Gross Income Before Taxes | | XXX | This is Total Revenue minus Total Expenses. The end result may be negative depending on your revenue and expenses. |
| | Taxes | | XXX | If Gross Income is greater than 0 then you pay 21% of it in taxes. If Gross Income is 0 or negative, you don't pay any taxes. You need an IF statement here. |
| | Net Income After Taxes | | XXX | Is Gross Income minus Taxes. |
| | Net Income to Sales % | | XX.XX% | Is Net Income divided by Sales. Any time someone asks you for one value as a percent of another it means you have to divide the terms. |
| | | | | Format the result to 2 decimal places. |
| | 7. Remove the instructions from the Income Statement that are in column E. |
| | To proofread your work, flip this worksheet into formula view. (Ctrl/~ or Formulas, Show Formulas.) When you look at the formulas you should only see cell references. |
| | Numbers (variables) should be in other cells with labels in adjoining cells identifying what they are. There must NOT be any formulas in the variables section of the worksheet. |
| | 8. Copy the worksheet that has the completed Income Statement and create Variables (2) worksheet in this workbook (Right click on the Source tab, choose Move or Copy, |
| | then click in the Create a Copy box in the window.). |
| | Rename the Variables(2) worksheet: Goal Seek (Right click on the tab and select Rename.). |
| | In the Goal Seek worksheet, remove any formula you may have used to calculate Sales. Type in the $ value in that cell instead, or Goal Seek will not work. |
| | Why? If there is a formula in the cell Excel has to use that to calculate the value in the cell. If there is just a number, Excel may change it as we wish to do with Goal Seek. |
| | DO NOT REMOVE ANY OTHER FORMULAS!! |
| | 9. Using Goal Seek determine what Total Revenue would have to be for us to have Net Income to Sales % of 8.45%. |
| | To find Goal Seek look on the ribbon under Data, then What If Analysis. |
| | Target cell is Net Income to Sales %, To Value is 8.45%, by Changing Cell is Sales. | | | | | | | | |
| | If done correctly your percentage will be less than 8.45%. This is approximate. The less the number of calculations in the worksheet, the closer it becomes. |
| | 10. Make sure that all numbers in your worksheets are properly formatted. |
| | 11. Create a pie chart based on either of the 2 worksheets. |
| | You may create the chart in either of the worksheets or a separate one. If you create a new worksheet for it rename the tab. |
| | a. The chart must indicate the distribution of all of the expenses for January. |
| | We want to focus on the 4 largest expenses. Consolidate the rest into a group called Other Expenses. |
| | To do that you may need to bring your data to a different place on the worksheet. That is typical of what happens at work. |
| | Arrange the data in a manner convenient for creating the chart. |
| | Worksheets are set up for people to view and use the data. Charts are not the priority. |
| | b. Properly title the chart, including company name, subject of the data, date, and unit of measure for the data. |
| | 12. Cash Flow statements are very important for companies. Most bankruptcies for small businesses are not due to product |
| | or service, but from lack of cash to pay bills. Looking at the following example of a Statement of Cash Flows please answer the questions to the side. |
| | The numbers below are unrelated to the Income Statement we created earlier. |
| | Tiger Paws Inc. |
| | Statement of Cash Flows |
| | For Year Ending December 31, 2017 |
| | | | | | | | | | | | | | | | | | Type answers to questions in the spaces provided below. |
| | Operating Activities |
| | Sales Receipts | | $5,000,000.00 | | | a. What is the beginning and end time for this statement of cash flows? |
| | Payments for Products | | ($2,500,000.00) |
| | Payments for Operations | | ($2,000,000.00) | | | b. What do the red numbers indicate in the statement? Are they a source or use of cash for the company? |
| | Interest Payments | | ($100,000.00) |
| | Taxes | | ($227,500.00) | | | c. We have noticed customers paying bills in an average of 35 days instead of 28. |
| | Extraordinary Items | | $200,000.00 | | | What are some possible reasons the company went out and added new loans and equity financing |
| | Net Cash Flow from Operating Activities | | $372,500.00 | | | even though the business seems to be generating cash from operations? |
| | Investing Activities |
| | Purchase of New Fixed Assets |
| | Property/ Machinery | | ($210,000.00) |
| | Interest Received | | $5,000.00 |
| | Net Cash Flow from Investing Activities | | ($205,000.00) | | | d. How much money was in the bank on January 1, 2017? |
| | Financing Activities | | | | | e. Looking at your cash position as of December 31, 2017 what might be prudent action to take that |
| | Short-term Debt | | $700,000.00 | | | would be beneficial to the company? |
| | Long-term Debt | | $1,100,000.00 |
| | New Equity Issued | | $5,000,000.00 |
| | Net Cash Flow from Financing Activities | | $6,800,000.00 |
| | Net Increase (Decrease in Cash) | | $6,967,500.00 |
| | Cash at the Beginning of the Year | | ($1,300,000.00) |
| | Cash at the End of the Year | | $5,667,500.00 |
efstudent19
TigerPawsLLCIncomeStatementW2018.xlsx
