I don’t understand this Excel question and need help to study.
Excel Case 1B –Trekker’s Shoe Store (v7.2) – Updated 2/10/2020
This project will import 2 worksheets of data (Assumptions and P&L) and create 3 more worksheets in an Excel workbook. The first and second sheets will be imported from TEXT files that contain the basic structure for use in creating other worksheets.
- The first file contains the assumptions about the business. These assumptions will be used to create the detail projected sales, cost of goods sold, expenses and any income taxes, resulting in an ending Profit or Loss.
- The second file contains the area used to calculate the expected Profit and Loss for the current year and the next 3 years. This document will be presented to a local banker, for the purpose of acquiring an expansion loan. It will also be used to plan possible salary increases for employees and to project future Net Profit (Loss).
PART 1 – Import of data and create 4 Year “P&L“ (Profit & Loss, i.e. Income Statement)
Save the “Trekkers_Shoe_input” file found in D2L and save on your computer (to your desktop, downloads folder, or flash drive. The D2L “input” file is in compressed “Zip” file format and you cannot import of use while compressed. After you downloaded the .ZIP file, then you must ‘extract’ and decompress the contents. Use Windows Explorer to view the file, then right click on Zip file and select “Extract all…” to create decompressed folder containing the text file(s) for use in this case assignment. Open a new Excel workbook file and import the Assumptions file into Sheet1 and rename it “Assumptions”. Then, import the P&L file into Sheet2 and rename the sheet “4Yr P&L”. Save the Excel file as
(increment v# number (v0, then v1, v2…) as you update & save to keep track of your work)
Retain the data in the same rows and cells as when correctly imported (as demoed in class).
Create a “defined named” range of contiguous cells to be used within the VLOOKUP command. Ensure you use the “defined named” when constructing the VOOKUP formulas.
In the Assumptions worksheet- Cells A6:E15 name this range of cells “Percent”
Step 1 – Four year Projected Income Statement (4Yr P&L Worksheet)
This step will create Sales for 2019 starting in cell C6 in “4Yr P&L” worksheet. Use a VLOOKUP() formula to create the department sales amounts for 2019 by returning the “percent of total sales” for 2019 by classification code from the cell range percent. Remember that the assumptions about the business are located in the Assumptions worksheet.
Once the 2019 sales amount have been created:
- Use the 2019 sales amounts to project the future years (2019, 2020, 2021, 2022) sales based on the specific year growth % in the assumptions. Each year is a growth over the previous year. Hint: Create a formula that uses relative and absolute references, so that the formulas for 2019 can be correctly copied to 2020, 2021 and 2022.
- Then using the sales amount for 2019, 2020, 2021, 2022 above, calculate Cost of Goods Sold (COGS) for 2019, 2020, 2021 and 2022 for each department by creating a VLOOKUP() formula using classification code and the cell range percent. Hint: the VLOOKUP formula should return the appropriate “COGS percent of sales” from cell range percent, then multiply by the sales for the corresponding year and department.
- Next, calculate all expenses for each year from 2019 to 2022. In the Assumptions section you will find these row labels. Each parameter’s usage is described below.
- Marketing. Multiply the percentage number by total sales to get marketing expenses for each year.
- Office and Administrative. Multiply the percentage number by total sales to get office and administrative expenses for each year.
- Transportation, Multiply the percentage number by total cost of goods sold to get transportation expenses for each year.
- Depreciation per year. Depreciation cost is the same for each year.
- Utilities cost here is a monthly average. Therefore, you need to multiply it by 12 to get annual utilities cost. Note that the cost are expected to increase by 2% every year. Accordingly, multiply the monthly average from previous year by 1.02 to account for the increase.
- [Corporate Tax Rate (used later, below in 4. Calculating Tax). Later, you will multiply the percentage number by ‘income for taxation’ that you get on row 50 to get ‘tax due’. See number 4 below for condition when calculating tax.]
- Wages include those for 5 executives (namely the owner, 2 managers, 2 buyers) and payroll for all hourly workers.
- There is currently no annual loan payment due in 2019, or in the following years. The last loan was paid off in prior year of 2018. So, the current annual loan payment is zero and same is projected for next 3 years.
- Calculating tax: [must consider carry-over losses that might reduce taxable income]
- First, calculate “Raw Income” by subtracting total expenses from gross profit
- Second, calculate “income for taxation” by using an IF statement.
** If last year’s Income for Taxation was a LOSS, then lower the RAW income by the amount of the loss. Otherwise, it is the same as RAW income. **
- Use an IF statement to calculate the ‘Tax Due’ amount. Taxes are not calculated or paid on LOSS amounts, only PROFITS.
CHECK FIGURES: When this worksheet labeled “4 Year P&L” (Income Statement) is completed, then check in D2L assignment to compare CHECK FIGURES with correct NET INCOMES per year.
PART 2: Copy the finished and checked “4Yr P&L” (Income Statement) worksheet and create a copy as a new worksheet called: “Loan Projections”. Follow the steps below explaining how to modify the new worksheet.
Step 2 – Loan Projection (name the sheet “Loan Info”).
Click on the tab “FOUR YEAR P&L”, hold down CTL, drag to the right and copy the sheet. Rename the new sheet “Loan Info”.
- The company is planning a small expansion in 2020 to allow more inventory and selling space. They have received a loan conformation for an additional $250,000.00. Enter the new loan requested amount on assumptions sheet on cell C50.
Use the information from B50 to C53 and the PMT function to calculate the monthly payment. From that, calculate the annual payment amount to be used in the “Loan Information” worksheet. New loan payments start in 2019.
- When the expansion is complete the company will need to add 2 more hourly employees starting in 2021 as shown on the Assumptions sheet (NOT 2020 as written in previous instructions). Recalculate wages (But ignore mention of health care premiums as stated in previous instructions, as there are no health care premiums in this dataset).
- In 2020 the new loan request should be depreciated (as we assume the loan is invested in assets). Depreciation should start in 2020 as stated in the Assumptions worksheet. (NOT 2019 as the previous instructions stated. The additional amount will be fully depreciated in a straight line method for 10 years (Hint: best to use Straight line depreciation function named as SLN () function).
Step 3 – “Salary what-if”
Copy the “Loan Info” sheet to a new sheet. Rename the new sheet “Salary what if”.
Over the next few years management would like to see what salary increases will do to the profitability. The owner (Mr. Shoemaker) would like to see the following:
- The owner’s salary draw will increase by $20,000 in 2020 and continue at this new salary.
- The Managers will receive a 3.5% increase each year starting in 2020.
- The buyers will receive a 4% increase each year starting in 2020.
COGS will increase by .05% from that in 2019 for next three years after 2019, as a result of an increase in direct labor cost.[Dropped, ignore this outdated instruction].
- The hourly workers will get an additional 0.50 per hour starting in 2020. [NOT 2019 as stated in the previous instructions.] They will then receive another raise of 0.25 per hour starting in 2022. [NOT 2021 as stated in the previous instructions]. The Assumptions worksheet has cells which also refer to the incorrect years 2019 and 2021. See the screenshot below. Do still use these Assumption cell’s of C57 and C58, but change the column B labels to years 2020 and 2022, respectively [do not leave old year labels of 2019, and 2021].
Below shows OLD Imported values circled below. Need to change years: 2019 becomes 2020; 2021 becomes 2022.
[In more detail: Above, The cells circled in blue refer to the Step 3: for Salary What-If worksheet. These C column values are salary raises from the imported Assumptions sheet. You can see Cell B57 should say ‘Hourly Worker increase in 2020’, not 2019. Cell B58 should say 2022, not 2021. Note that your Assumptions worksheet might differ by a row# depending on if you inported to start in cell A1, as instructed. If not starting import at A1, then this screen shot above should give you an idea of where the incorrect description is. ]
Step 4 – Add a worksheet called “Analysis”, copy the questions below into the worksheet and answer them.
- What is the four year total Net Income projected in the original 4Yr P&L worksheet?
- What year is projected to be most profitable? What is the projected Net Profit for that year?
- Compare with the Loan Info worksheet. Did Net Income increase from P&L to getting Loan, or decrease?
By how much?
- How much added sales do you think would make it worthwhile to get this loan which costs so much during this timeframe?
- Salary What-If: is the owner being too generous if he/she raises salaries to these amounts? Why do you think that?
- What revision to the salary increase would you recommend to the owner (as experienced employees may leave if there are no salary increases)? Note that you could use the “Solver” feature in Excel, as mentioned below in Extra Credit.
New Worksheet: (Extra credit) Salary Solver: Use the Excel analysis function “Solver”.
Make a copy of the “4Yr P&L” worksheet before creating the “Solver” solution, label the new worksheet “P&P-Solver”. A correct solver worksheet earns 5 points extra credit.
In Solver worksheet, If the 4 year net Income is less than $750,000, Mr. Shoemaker would like to know what the total sales needs to be for 2020-2022 to assure that the net Income reaches $750,000.
Use solver to determine the necessary total sales for those 3 years of 2020, 2021 and 2022.
CHECK FILE NAME: You should have named your file as:
Turn in the complete Excel workbook to the First Excel Case drop box in D2L before the deadline.