Assignment Prompt & Instructions
Due by Friday. Need someone that is advance at excel spreadsheet
– ## Assessment.xlsx
**## Assessment.xlsx**
## Instructions
| | |
| — | — |
| | Please follow the below instructions when completing these Excel Exercises |
| 1 | This is an assessment of what you know and what you are capable of doing in Excel |
| 2 | This is NOT an assessment of "can you research the internet, AI, textbooks or friends to figure out the problems" |
| 3 | This is NOT an assessment of "can you solve this in another software or somewhere else and the move the answers to Excel" |
| 4 | Use only your knowledge |
| 5 | Use only Excel |
| | This assessment should only take 45-60 minutes |
| | At 60 minutes from receiving the Excel Exercise, forward the email received today with your completed Excel exercises spreadsheet attached to email address provided. |
## Problem Solving 1
| | | | | | | | | | | | |
| — | — | — | — | — | — | — | — | — | — | — | — |
| | Problem Solving 1 has 3 parts. Please complete all 3. |
| | 1. Put in all formulas to the current and proposed scenarios to create a fully functioning spreadsheet. |
| | 2. What is the total profit of proposed vs current. |
| | 3. How much additional vendor discount per unit is needed on the 2 Smartwater items to breakeven on Profit. |
| | Item Desc | Item Cost | Vendor Discount % | Vendor Discount $ | Net Cost | Retail | Profit | GM% | | Units Sold | Low Competitor |
| | Current |
| | Smart Water 23.7 | $1.19 | 5.88% | $0.07 | $1.12 | $1.92 | $0.80 | 42% | | 3,479 | $1.67 |
| | Smart Water 33.8 | $1.34 | 0.00% | $0.00 | $1.34 | $2.22 | $0.88 | 40% | | 8,366 | $1.97 |
| | Lifewater 23.7 | $1.25 | 19.95% | $0.25 | $1.00 | $1.71 | $0.71 | 42% | | 6,860 | $1.79 |
| | Lifewater 33.8 | $1.47 | 31.29% | $0.46 | $1.01 | $1.98 | $0.97 | 49% | | 9,980 | $2.49 |
| | Voss 16.9 | $1.08 | 8.34% | $0.09 | $0.99 | $1.63 | $0.64 | 39% | | 1,182 | $0.00 |
| | Voss 28.7 | $1.68 | 20.24% | $0.34 | $1.34 | $2.43 | $1.09 | 45% | | 7,678 | $0.00 |
| | Fiji 16.9 | $1.05 | 0.00% | $0.00 | $1.05 | $1.79 | $0.74 | 41% | | 8,727 | $0.00 |
| | Fiji 33.8 | $1.67 | 0.00% | $0.00 | $1.67 | $2.79 | $1.12 | 40% | | 4,281 | $3.29 |
| | Proposed lower Smart Water retails |
| | Smart Water 23.7 | $1.19 | 5.88% | $0.07 | $1.12 | $1.67 | $0.55 | 33% | | 3,479 | $1.67 |
| | Smart Water 33.8 | $1.34 | 0.00% | $0.00 | $1.34 | $1.97 | $0.63 | 32% | | 8,366 | $1.97 |
| | Lifewater 23.7 | $1.25 | 19.95% | $0.25 | $1.00 | $1.71 | $0.71 | 42% | | 6,860 | $1.79 |
| | Lifewater 33.8 | $1.47 | 31.29% | $0.46 | $1.01 | $1.98 | $0.97 | 49% | | 9,980 | $2.49 |
| | Voss 16.9 | $1.08 | 8.34% | $0.09 | $0.99 | $1.63 | $0.64 | 39% | | 1,182 | $0.00 |
| | Voss 28.7 | $1.68 | 20.24% | $0.34 | $1.34 | $2.43 | $1.09 | 45% | | 7,678 | $0.00 |
| | Fiji 16.9 | $1.05 | 0.00% | $0.00 | $1.05 | $1.79 | $0.74 | 41% | | 8,727 | $0.00 |
| | Fiji 33.8 | $1.67 | 0.00% | $0.00 | $1.67 | $2.79 | $1.12 | 40% | | 4,281 | $3.29 |
## Problem Solving 2
| | | | |
| — | — | — | — |
| | How many units have we sold at the single retail (full retail)? |
| | How many units have we sold at the 2 for $4 retail? |
| | | We have sold a total of 61,775 Purell Wipe 20 ct. packages while running a 2 for $4 promotion. |
| | | We have had an average retail of $2.187132 |
| | | Retail is: | $2.49 for a single retail (full retail) or 2 for $4 |
## Problem Solving 3
| | | |
| — | — | — |
| | If you don't change your retail, what will your new gross margin % be? |
| | | Your vendor/supplier calls and says they need to go up 8% on your cost. |
| | | You currently have a 30% Gross Margin. |
## Problem Solving 4
| | | |
| — | — | — |
| | What is the % of net profits we need to send to payroll that would equal $2000/month? |
| | | Store makes $1,378,589 annual net profit. |
| | | We want to pay an employee a % of net profits that is equal to $2000/month. |
## Problem Solving 5
| | | |
| — | — | — |
| | What is our cost? |
| | | We have a product that we are at $0.40 Retail with a negative 100% Gross Margin. |
## PS 6 -Thought Process-Reasoning
| | | | | |
| — | — | — | — | — |
| | What are your ideas/thoughts on possible causes for the decrease in year over year sales starting in May? |
| | | Sales Trends started downward in May. |
| | | Sales Unit year over year Increase/Decrease (Feb vs Feb, etc.) |
| | | | Hot Dog Unit Sales |
| | | | Month | Year over Year % |
| | | | Feb | 25% |
| | | | Mar | 26% |
| | | | Apr | 25% |
| | | | May | -4% |
| | | | June | -7% |
| | | | July | -10% |
**## Assessment.xlsx**
## Instructions
| | |
| — | — |
| | Please follow the below instructions when completing these Excel Exercises |
| 1 | This is an assessment of what you know and what you are capable of doing in Excel |
| 2 | This is NOT an assessment of "can you research the internet, AI, textbooks or friends to figure out the problems" |
| 3 | This is NOT an assessment of "can you solve this in another software or somewhere else and the move the answers to Excel" |
| 4 | Use only your knowledge |
| 5 | Use only Excel |
| | This assessment should only take 45-60 minutes |
| | At 60 minutes from receiving the Excel Exercise, forward the email received today with your completed Excel exercises spreadsheet attached to email address provided. |
## Problem Solving 1
| | | | | | | | | | | | |
| — | — | — | — | — | — | — | — | — | — | — | — |
| | Problem Solving 1 has 3 parts. Please complete all 3. |
| | 1. Put in all formulas to the current and proposed scenarios to create a fully functioning spreadsheet. |
| | 2. What is the total profit of proposed vs current. |
| | 3. How much additional vendor discount per unit is needed on the 2 Smartwater items to breakeven on Profit. |
| | Item Desc | Item Cost | Vendor Discount % | Vendor Discount $ | Net Cost | Retail | Profit | GM% | | Units Sold | Low Competitor |
| | Current |
| | Smart Water 23.7 | $1.19 | 5.88% | $0.07 | $1.12 | $1.92 | $0.80 | 42% | | 3,479 | $1.67 |
| | Smart Water 33.8 | $1.34 | 0.00% | $0.00 | $1.34 | $2.22 | $0.88 | 40% | | 8,366 | $1.97 |
| | Lifewater 23.7 | $1.25 | 19.95% | $0.25 | $1.00 | $1.71 | $0.71 | 42% | | 6,860 | $1.79 |
| | Lifewater 33.8 | $1.47 | 31.29% | $0.46 | $1.01 | $1.98 | $0.97 | 49% | | 9,980 | $2.49 |
| | Voss 16.9 | $1.08 | 8.34% | $0.09 | $0.99 | $1.63 | $0.64 | 39% | | 1,182 | $0.00 |
| | Voss 28.7 | $1.68 | 20.24% | $0.34 | $1.34 | $2.43 | $1.09 | 45% | | 7,678 | $0.00 |
| | Fiji 16.9 | $1.05 | 0.00% | $0.00 | $1.05 | $1.79 | $0.74 | 41% | | 8,727 | $0.00 |
| | Fiji 33.8 | $1.67 | 0.00% | $0.00 | $1.67 | $2.79 | $1.12 | 40% | | 4,281 | $3.29 |
| | Proposed lower Smart Water retails |
| | Smart Water 23.7 | $1.19 | 5.88% | $0.07 | $1.12 | $1.67 | $0.55 | 33% | | 3,479 | $1.67 |
| | Smart Water 33.8 | $1.34 | 0.00% | $0.00 | $1.34 | $1.97 | $0.63 | 32% | | 8,366 | $1.97 |
| | Lifewater 23.7 | $1.25 | 19.95% | $0.25 | $1.00 | $1.71 | $0.71 | 42% | | 6,860 | $1.79 |
| | Lifewater 33.8 | $1.47 | 31.29% | $0.46 | $1.01 | $1.98 | $0.97 | 49% | | 9,980 | $2.49 |
| | Voss 16.9 | $1.08 | 8.34% | $0.09 | $0.99 | $1.63 | $0.64 | 39% | | 1,182 | $0.00 |
| | Voss 28.7 | $1.68 | 20.24% | $0.34 | $1.34 | $2.43 | $1.09 | 45% | | 7,678 | $0.00 |
| | Fiji 16.9 | $1.05 | 0.00% | $0.00 | $1.05 | $1.79 | $0.74 | 41% | | 8,727 | $0.00 |
| | Fiji 33.8 | $1.67 | 0.00% | $0.00 | $1.67 | $2.79 | $1.12 | 40% | | 4,281 | $3.29 |
## Problem Solving 2
| | | | |
| — | — | — | — |
| | How many units have we sold at the single retail (full retail)? |
| | How many units have we sold at the 2 for $4 retail? |
| | | We have sold a total of 61,775 Purell Wipe 20 ct. packages while running a 2 for $4 promotion. |
| | | We have had an average retail of $2.187132 |
| | | Retail is: | $2.49 for a single retail (full retail) or 2 for $4 |
## Problem Solving 3
| | | |
| — | — | — |
| | If you don't change your retail, what will your new gross margin % be? |
| | | Your vendor/supplier calls and says they need to go up 8% on your cost. |
| | | You currently have a 30% Gross Margin. |
## Problem Solving 4
| | | |
| — | — | — |
| | What is the % of net profits we need to send to payroll that would equal $2000/month? |
| | | Store makes $1,378,589 annual net profit. |
| | | We want to pay an employee a % of net profits that is equal to $2000/month. |
## Problem Solving 5
| | | |
| — | — | — |
| | What is our cost? |
| | | We have a product that we are at $0.40 Retail with a negative 100% Gross Margin. |
## PS 6 -Thought Process-Reasoning
| | | | | |
| — | — | — | — | — |
| | What are your ideas/thoughts on possible causes for the decrease in year over year sales starting in May? |
| | | Sales Trends started downward in May. |
| | | Sales Unit year over year Increase/Decrease (Feb vs Feb, etc.) |
| | | | Hot Dog Unit Sales |
| | | | Month | Year over Year % |
| | | | Feb | 25% |
| | | | Mar | 26% |
| | | | Apr | 25% |
| | | | May | -4% |
| | | | June | -7% |
| | | | July | -10% |