Dashboard

Mufunwa Michelle

New Member
Joined
Aug 27, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Activity 6.7
The CEO of Ikhishi Likagogo was very impressed after you have developed the initial dashboard. Due to the dashboard of 2022, she was able to identify that cash was not a KPI for the company. She also looked at other KPIs for similar companies and decided the major KPI will be net profit percentage (NP%). She decided that the budgeted np% for the divisions would be as follows:
54 | P a g e

Division NP%
Breads 25% Cakes 40% SA Delights 30%
Table 6.15: Net profit percentage for divisions - 2023
Her requirement is to be able to select a division and then to immediately see what the division’s net profit percentage, net profit and revenue are. Also, part of this view should indicate if the selected division is over or under budget with the variance. She wants to be able to see it per the selected month. To assist her in identifying top performing products and poor performing products, she wants to see a chart displaying the top 5 or the bottom 5 products per the division selected initially (please show actual and budget values). Another requirement (on a separate chart in the dashboard) is to see the np% for a specific product (per the division selected initially). This product should track the actual np% versus the budgeted np%. She wants to see this for the whole year, viewing the trend of the np%. She calls this chart a “Net profit percentage tracker”.
Lastly, she told you that she is a bit old school and still want to include a table in the dashboard. When selecting the division and month above, the table should populate the following fields:
NP% Profit Revenue Nr* Act Var Act Var Act Var
Table 6.16: Table requirement in dashboard
The rows with the * should not have a header, but their meanings are as follows:
Robot:
Smileys: Bar:
55 | P a g e
If the np% is below budget, then a red light should appear. If budget is net plus 10% above budget, a yellow indicator should appear. If over 10% of budget, a green
indicator should appear. For example, .
If the variance between actual and budget is positive, then awill appear;
otherwise ashould appear.
A bar where 0 is the mid-point. Any variance above zero should be a bar chart to the right, and a value below zero should be a bar chart to the left.
Product Name
Bud
Robot*
Bud
Smileys*
Bud
Bar*

This table should be sorted from the highest net profit percentage to the lowest. She also only wants to see 10 products at a time. She asks you to insert a scroll bar to scroll to the next products.
You can download the data spreadsheet that was supplied by the CEO here. Please note that the spreadsheet has two sheets. (The date of this dataset is 31 July 2023.)
You are required to develop this dashboard in Microsoft Excel 365. The CEO requested that you use company logo colours in your graph unless specifically requested otherwise. Once your dashboard is done, print your output (dashboard) to a PDF file (please select July and the Cakes division; the table should display product 1-10, Top 5, Profit and Chocolate cake). Make sure that it fits on one page and that the content is readable. You will be assessed on the content and best practice principles of your dashboard.
Please upload your PDF document here.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
This looks like some sort of assignment, homework, or a test.
Please do not expect people to do your work for you! That would not be ethical.

If you show your work (what you have done so far), and ask very specific questions, people may be willing to give you some tips to point you in the right direction.
But I do not see any of that in your question.

I would also recommend breaking up your question into smaller, individual questions. You are much more likely to get help with singular pointed questions than you are with a whole litany of questions all rolled into a single post. Most people will not touch questions like that.
 
Upvote 0
well, Why don't you try to accomplish the task and see if the forum can help you improve or make suggestions to what you try?

Also, to add onto what @Joe4 wrote, posting your data using xl2bb add in (link below) can give the forum your data without typos and see your formulas.
(be sure to anonymize your data).
 
Upvote 0

Forum statistics

Threads
1,224,315
Messages
6,177,845
Members
452,810
Latest member
jeffrey0409

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top