M06 Dashboard Homework.xlsm - (1)

xlsm

School

University of Utah *

*We aren’t endorsed by this school

Course

4020

Subject

Management

Date

Apr 3, 2024

Type

xlsm

Pages

31

Uploaded by PresidentStrawReindeer42

Report
You are a financial analyst working for Tommy Tim Electronics. Your management would like you to create a monthly dashboard highlighting Key Monthly Performance. Management wants to see the data graphica where possible using colors and icons to indicate good, okay and poor performance. Required: - You should use at least 3 different chart types. - You should use at least one example of spark lines and one example of conditional formatting. - You will be graded on presentation and proper use of formatting tools. - The dashboard should fit on two pages (a separate worksheet can be used for each page or you can use pa - You can use as many support worksheets as you would like as long as they are set up in a way that facilitat - Do not change anything on the Instructions tab. - All support data for your charts should reference the Instructions using a lookup function, referencing a ce hardcode numbers from the instructions worksheet) - I am not looking for any particular charts or formatting except where noted (be creative) Below are the KPIs that management wants included on the Dashboard: Sales, Net Income and Breakeven Month East West South Revenue by month 1 201,909.0 627,719.0 157,474.0 2 206,399.8 668,478.3 159,560.0 3 214,493.8 686,657.8 168,033.1 4 228,469.6 690,461.9 171,100.1 5 237,305.9 726,733.0 176,992.7 6 241,193.5 740,686.2 189,744.9 7 249,461.8 762,276.3 198,721.2 8 254,657.0 804,743.5 203,936.5 9 259,955.5 825,225.7 215,833.5 10 265,427.8 831,123.0 222,975.6 11 276,444.9 864,237.4 240,708.7 12 290,264.2 875,597.4 247,338.0 Gross Margin 25% 30% 40% Average Monthly Fixed Costs 65,000.00 150,000.00 75,000.00 Net Income target as a percent of revenue 10% 10% 10% 1) Create a chart for each region and total company comparing month over month revenue to breakeven an 2) Create an area chart showing month over month net income spread by region. Overlay this chart with a month.
Sales Persons Productivity East Current Month Actual Target Referrals 25 25 Customer Sales Calls 50 40 Total Customer Contacts 75 65 Contracts Signed 12 33 Cancellations 1 1 West Current Month Actual Target Referrals 40 30 Customer Sales Calls 40 35 Total Customer Contacts 80 65 Contracts Signed 35 33 Cancellations - 1 South Current Month Actual Target Referrals 10 5 Customer Sales Calls 20 15 Total Customer Contacts 30 20 Contracts Signed 10 7 Cancellations - 1 3) Comparison of productivity to targets with colors or icons emphasizing areas that are doing well and thos indicators are not comparable to each other in the current presentation. Can you make the comparable? I the dashboard comparable before formatting. 4) A chart or schedule indicating how successfully customer contacts are being converted into sales by regio company. 5) A chart or schedule indicating the percent of customer contacts that are coming through referrals. Credit and Collection Accounts Receivable Stats Month A/R Balance Sales Per Day DSO January 1 1,085,812.20 32,903.40 33 February 2 1,275,806.98 34,481.27 37 March 3 1,354,300.57 35,639.49 38
April 4 1,562,378.53 36,334.38 43 May 5 1,597,444.24 38,034.39 42 June 6 1,445,003.67 39,054.15 37 July 7 1,412,202.55 40,348.64 35 August 8 1,347,559.52 42,111.23 32 September 9 1,474,483.30 43,367.16 34 October 10 1,451,479.12 43,984.22 33 November 11 1,335,344.65 46,046.37 29 December 12 1,271,879.57 47,106.65 27 7) Insert a Sparkline in your dashboard graphically depicting the A/R balance by month. 6) Create a chart or schedule showing the month to month comparison of the actual DSO to the target DSO 7) Generate a chart or schedule graphically depicting the Month to month past due trend. (Past due is any a 8) Calculate the percent of total receivables in each aging category for November and December. Put a char by aging category. 9) Same as 8 except compare December to the average in each aging category for the year I will grade the homework in the following areas Turned in the assignment on time with an attempt to complete each activity 50% Creativity - use of many different types of charts and creative ways to communicate 10% Organization - how well organized your charts are (Sized, lined up and pleasing to the eye) 10% Communicate - do the charts deliver the right message to management 10% Presentation - well formatted with titles, borders etc. 10% Followed Instructions 10% Did you follow all the instructions Does your dashboard fit on two pages when I print them Did you reference the instructions vs. hard coding or copy / pasting data 100%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
e ally age layout functions to print two pages). tes updating the model monthly. ell or some other function (Do not copy or Total 987,102.0 1,034,438.1 1,069,184.7 1,090,031.5 1,141,031.6 1,171,624.6 1,210,459.3 1,263,337.0 1,301,014.7 1,319,526.5 Breakeven revenue = fixed cost / gross margin 1,381,391.0 Target revenue = fixed costs / (gross margin - the t 1,413,199.5 31% 290,000.00 10% nd the target revenue (4 charts). line chart of consolidated net income by You can find examples of Dashboards by googling Dashboards and selecting images You can find examples of Dashboards by googling Dashboards and selecting images You can accomplish this by using a Combo chart. You can accomplish this by using a Combo chart.
Year to Date Actual Target 200 250 300 400 500 650 117 330 24 12 Year to Date Actual Target 356 270 263 315 619 585 364 300 10 12 Year to Date Actual Target 32 45 128 135 160 180 94 63 - 12 se that need improvements. These If you can, make the schedules used on on and total ns DSO Target 0 to 30 30 to 60 60 to 90 over 90 30 781,784.78 162,871.83 86,864.98 54,290.61 30 956,855.24 191,371.05 102,064.56 25,516.14 30 1,110,526.46 162,516.07 67,715.03 13,543.01 One way you can make key indicators comparable is to calculate the percent that each indicator is different from the target. One way you can make key indicators comparable is to calculate the percent that each indicator is different from the target. To save space, you could combine 4 & 5 with 3. To save space, you could combine 4 & 5 with 3. Hint: You might consider building this schedule on the Dashboard first and then laying out everything else around it. Hint: You might consider building this schedule on the Dashboard first and then laying out everything else around it.
30 1,203,031.47 203,109.21 140,614.07 15,623.79 30 1,309,904.27 175,718.87 79,872.21 31,948.88 30 1,112,652.83 202,300.51 72,250.18 57,800.15 30 1,101,517.99 141,220.25 141,220.25 28,244.05 30 1,104,998.80 134,755.95 67,377.98 40,426.79 30 1,164,841.81 162,193.16 103,213.83 44,234.50 30 1,161,183.29 188,692.28 72,573.96 29,029.58 30 974,801.60 200,301.70 120,181.02 40,060.34 30 1,017,503.65 127,187.96 76,312.77 50,875.18 - - O. aging over 30 days) rt on you dashboard graphically comparing the November percentage to December 30 15 30 3 30 3 30 3 30 3 30 3 Hint: if you Sparkline in the Sparklin dashboard. the Sparklin Hint: if you Sparkline in the Sparklin dashboard. the Sparklin DSO stands is a typical in managed. DSO stands is a typical i managed.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
target percent)
merge cells then enter the nto the merge cells you can size ne to fit better on your Be sure to put a title above ne. merge cells then enter the nto the merge cells you can size ne to fit better on your . Be sure to put a title above ne. for Days Sales Outstanding. This ndicator of how well A/R is for Days Sales Outstanding. This indicator of how well A/R is
Name Tim Odjav u1366760
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
1 2 3 4 5 6 7 8 9 10 11 12 $- $100,000 $200,000 $300,000 $400,000 $500,000 $600,000 $700,000 $800,000 $900,000 $1,000,000 West Revenues WestRev BreakevenRev TargetRev 1 $- $50,000 $100,000 $150,000 $200,000 $250,000 $300,000 $160,000 Net Income by Region Current Month % of Targe YTD % of Targe Referrals 133% 132% Customer Sales Calls 114% 83% Total Customer Contacts 123% 106% Contracts Signed 108% 121% Cancellations 0% 83% Productivity - West C Referrals Customer Sales Calls Total Customer Contacts Contracts Signed Cancellations Produ Region East West South East West Total Customer Contacts 75 80 30 500 Contracts Signed 12 35 10 117 % of Contacts Converted to Sales 16% 44% 33% 23% 5 Sales from Contacts by Region Current Month YTD January February March April May June July A/R Balance 1,085,812.20 1,275,806.98 1,354,300.57 1,562,378.53 1,597,444.24 1,445,003.67 1,412,202
1 2 3 4 5 6 7 8 $- $20,000 $40,000 $60,000 $80,000 $100,000 $120,000 $140,000 $160,000 Column B Column C Column D Column
1 2 3 4 $- $50,000 $100,000 $150,000 $200,000 $250,000 $300,000 $350,000 $400,000 $450,000 $500,000 East EastRev 1 2 3 4 5 6 7 8 9 10 11 12 South Revenues SouthRev BreakevenRev TargetRev December vs. Average R Current Month % of Ta Referrals 10 Customer Sales Calls 12 Total Customer Contacts 11 Contracts Signed 3 % of Contacts Signing Contrac 3 Cancellations 10 Productivity - East Current Month % of Targe YTD % of Targe Referrals 200% 71% Customer Sales Calls 133% 95% Total Customer Contacts 150% 89% Contracts Signed 143% 149% Cancellations 0% 0% Productivity - South South East West South 30 500 619 160 10 117 364 94 33% 23% 59% 59% Region YTD Region East West Referrals 25 Total Customer Contracts 75 % of Contacts Coming from Referra 33% Referrals Current M May June July August September October November December 97,444.24 1,445,003.67 1,412,202.55 1,347,559.52 1,474,483.30 1,451,479.12 1,335,344.65 1,271,879.57 Monthly A
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
9 10 11 12 n E 0 to 30 30 to 60 6 $- $500,000 $1,000,000 $1,500,000 $2,000,000 $2,500,000 1017503.65487601 127187.956859501 76312. 1083300.18310166 171019.903636712 94188. December vs. Average R December Avera 0 t o 3 0 3 0 t o 6 0 6 0 t o 0.73 0.15 0.09 0.8 0.1 % of Total Receiv November Decembe
5 6 7 8 9 10 11 12 t Revenues BreakevenRev TargetRev 1 2 3 4 5 6 7 $- $200,000 $400,000 $600,000 $800,000 $1,000,000 $1,200,000 $1,400,000 $1,600,000 Total Revenues TotalRev BreakevenRev Receivables Actual vs. Target DS Current Month % of TargetYTD % of Target 100% 80% Calls 125% 75% Contacts 115% 77% d 36% 35% gning Contrac 32% 46% 100% 200% Productivity - East Region East West South East West South 25 40 10 200 356 32 ts 75 80 30 500 619 160 om Referra 33% 50% 33% 40% 58% 20% Referrals by Region Current Month YTD er December 4.65 1,271,879.57 Monthly A/R
60 to 90 Over 90 .7741157008 50875.1827438005 .4030375009 35966.0843600235 Receivables rage o 9 0 Ov er 9 0 0.03 0.06 0.04 vables er January Feb r uary March April May June July August Sep t emb er Oc $- $50,000 $100,000 $150,000 $200,000 $250,000 $300,000 $350,000 $400,000 Past Due (Over 30 Days) Ja nu ar y February March April May June July Aug ust September 0 5 10 15 20 25 30 35 40 45 50 DSO DSO Target Days Sales Outstanding
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
8 9 10 11 12 Target Rev O
Octo b er No v e mb e r Dec ember ber Octobe r November Decembe r
Q1 Month EastRev BreakevenRev TargetRev 1 201,909.00 260,000 433,333.33 2 206,399.80 260,000 433,333.33 3 214,493.78 260,000 433,333.33 4 228,469.56 260,000 433,333.33 5 237,305.93 260,000 433,333.33 6 241,193.49 260,000 433,333.33 7 249,461.80 260,000 433,333.33 8 254,657.04 260,000 433,333.33 9 259,955.48 260,000 433,333.33 10 265,427.80 260,000 433,333.33 11 276,444.93 260,000 433,333.33 12 290,264.18 260,000 433,333.33 Month WestRev BreakevenRev TargetRev 1 627,719.00 500,000.00 750,000.00 2 668,478.33 500,000.00 750,000.00 3 686,657.78 500,000.00 750,000.00 4 690,461.91 500,000.00 750,000.00 5 726,733.01 500,000.00 750,000.00 6 740,686.24 500,000.00 750,000.00 7 762,276.27 500,000.00 750,000.00 8 804,743.48 500,000.00 750,000.00 9 825,225.71 500,000.00 750,000.00 10 831,123.02 500,000.00 750,000.00 11 864,237.41 500,000.00 750,000.00 12 875,597.37 500,000.00 750,000.00 Month SouthRev BreakevenRev TargetRev 1 157474.00 187,500.00 250,000.00 2 159559.96 187,500.00 250,000.00 3 168033.09 187,500.00 250,000.00 4 171100.06 187,500.00 250,000.00 5 176992.65 187,500.00 250,000.00 6 189744.87 187,500.00 250,000.00 7 198721.25 187,500.00 250,000.00 8 203936.52 187,500.00 250,000.00 9 215833.49 187,500.00 250,000.00 10 222975.64 187,500.00 250,000.00 11 240708.68 187,500.00 250,000.00 12 247337.97 187,500.00 250,000.00 Month TotalRev BreakevenRev Target Rev 1 2 3 $- $50,000 $100,000 $150,000 $200,000 $250,000 $300,000 $350,000 $400,000 $450,000 $500,000 EastRev 1 2 $- $100,000 $200,000 $300,000 $400,000 $500,000 $600,000 $700,000 $800,000 $900,000 $1,000,000 West $80,000 $100,000 $120,000 $140,000 $160,000
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
1 987102.00 921,643.84 1,351,004.02 2 1034438.09 921,643.84 1,351,004.02 3 1069184.66 921,643.84 1,351,004.02 4 1090031.53 921,643.84 1,351,004.02 5 1141031.60 921,643.84 1,351,004.02 6 1171624.60 921,643.84 1,351,004.02 7 1210459.33 921,643.84 1,351,004.02 8 1263337.05 921,643.84 1,351,004.02 9 1301014.68 921,643.84 1,351,004.02 10 1319526.47 921,643.84 1,351,004.02 11 1381391.02 921,643.84 1,351,004.02 12 1413199.52 921,643.84 1,351,004.02 Q2 Month East West South Total 1 20190.90 62771.90 15747.40 98710.20 2 20639.98 66847.83 15956.00 103443.81 3 21449.38 68665.78 16803.31 106918.47 4 22846.96 69046.19 17110.01 109003.15 5 23730.59 72673.30 17699.27 114103.16 6 24119.35 74068.62 18974.49 117162.46 7 24946.18 76227.63 19872.12 121045.93 8 25465.70 80474.35 20393.65 126333.70 9 25995.55 82522.57 21583.35 130101.47 10 26542.78 83112.30 22297.56 131952.65 11 27644.49 86423.74 24070.87 138139.10 12 29026.42 87559.74 24733.80 141319.95 1 $- $20,000 $40,000 $60,000
4 5 6 7 8 9 10 11 12 East Revenues v BreakevenRev TargetRev 3 4 5 6 7 8 9 10 11 12 West Revenues tRev BreakevenRev TargetRev 1 2 3 4 5 6 7 $- $50,000 $100,000 $150,000 $200,000 $250,000 $300,000 South Revenues SouthRev BreakevenRev 1 2 3 4 5 6 7 8 $- $200,000 $400,000 $600,000 $800,000 $1,000,000 $1,200,000 $1,400,000 $1,600,000 Total Revenues TotalRev BreakevenRev Net Income by Region
2 3 4 5 6 7 8 9 10 11 12 Column B Column C Column D Column E
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
8 9 10 11 12 TargetRev 9 10 11 12 Target Rev
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Productivity - East Current Month % of TargetYTD % of Target Referrals 100% 80% Customer Sales Calls 125% 75% Total Customer Contacts 115% 77% Contracts Signed 36% 35% % of Contacts Signing Contract 32% 46% Cancellations 100% 200% Productivity - West Current Month % of TargeYTD % of Target Referrals 133% 132% Customer Sales Calls 114% 83% Total Customer Contacts 123% 106% Contracts Signed 108% 121% Cancellations 0% 83% Productivity - South Current Month % of TargeYTD % of Target Referrals 200% 71% Customer Sales Calls 133% 95% Total Customer Contacts 150% 89% Contracts Signed 143% 149% Cancellations 0% 0%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Sales from Contacts by Region Current Month YTD Region East West South East West Total Customer Contacts 75 80 30 500 619 Contracts Signed 12 35 10 117 364 % of Contacts Converted to Sales 16% 44% 33% 23% 59% Referrals by Region Current Month YTD Region East West South East West Referrals 25 40 10 200 356 Total Customer Contracts 75 80 30 500 619 % of Contacts Coming from Referral 33% 50% 33% 40% 58%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
South 160 94 59% South 32 160 20%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
January February March April May June A/R Balance 1,085,812.20 1,275,806.98 1,354,300.57 1,562,378.53 1,597,444.24 1,445,003.67 Month DSO DSO Target January 33 30 February 37 30 March 38 30 April 43 30 May 42 30 June 37 30 July 35 30 August 32 30 September 34 30 October 33 30 November 29 30 December 27 30 Month Past Due January 304,027.42 February 318,951.75 March 243,774.10 April 359,347.06 May 287,539.96 June 332,350.84 July 310,684.56 August 242,560.71 September 309,641.49 October 290,295.82 November 360,543.06 December 254,375.91 % of Total Receivables Month 0 to 30 30 to 60 60 to 90 Over 90 November 73% 15% 9% 3% December 80% 10% 6% 4% January Feb r uary Marc h April May June July Au $- $50,000 $100,000 $150,000 $200,000 $250,000 $300,000 $350,000 $400,000 Past Due (Over 30 D January February Mar c h April Ma y Ju ne Jul y Aug 0 5 10 15 20 25 30 35 40 45 50 Actual vs. Targe DSO DSO Target Days Sales Outstanding 0.73 0.8 % of
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
December vs. Actual 0 to 30 30 to 60 60 to 90 Over 90 December 1,017,503.65 127,187.96 76,312.77 50,875.18 Average 1,083,300.18 171,019.90 94,188.40 35,966.08 0 to 30 $- $500,000 $1,000,000 $1,500,000 $2,000,000 $2,500,000 1017503.65487601 1083300.18310166 December v 0 t o 3 0 3 0 0.15
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
July August September October November December Month 1,412,202.55 1,347,559.52 1,474,483.30 1,451,479.12 1,335,344.65 1,271,879.57 Au gust Septemb er Oc tober Novem ber Decem ber Days) Augu s t Septe mber Octobe r Nove mber Decemb e r et DSO Total Receivables November December
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
30 to 60 60 to 90 Over 90 127187.956859501 76312.7741157008 50875.1827438005 171019.903636712 94188.4030375009 35966.0843600235 vs. Average Receivables December Average t o 6 0 6 0 t o 9 0 Ov er 9 0 0.09 0.03 0.1 0.06 0.04
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
hly A/R
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

HRM 200 Project One Speaker Notes Template Oct 2.docx
HRM 400 Milestone One.docx
HRM 400 Mod 3 HR Inbox.docx
Individual Case Study.docx
Apple (1).pdf
Final Exam Review - SOLUTIONS.xlsx
WeekK 3 - Summative Assessment - Business Strategies Proposal_MGT-526.docx
3GR0 Business Workbook-2024-Part2.docx
Week 5 Assessment Prep - Organizational Structure Outline_MGT-526.docx
Week 2 Summative Assessment - Business Administration Organizational Proposal_MGT-526.docx
My primary conflict style is.docx
Chapter 9 Case Study.docx
Recommended textbooks for you
Text book image
Understanding Business
Management
ISBN:9781259929434
Author:William Nickels
Publisher:McGraw-Hill Education
Text book image
Management (14th Edition)
Management
ISBN:9780134527604
Author:Stephen P. Robbins, Mary A. Coulter
Publisher:PEARSON
Text book image
Spreadsheet Modeling & Decision Analysis: A Pract...
Management
ISBN:9781305947412
Author:Cliff Ragsdale
Publisher:Cengage Learning
Text book image
Management Information Systems: Managing The Digi...
Management
ISBN:9780135191798
Author:Kenneth C. Laudon, Jane P. Laudon
Publisher:PEARSON
Text book image
Business Essentials (12th Edition) (What's New in...
Management
ISBN:9780134728391
Author:Ronald J. Ebert, Ricky W. Griffin
Publisher:PEARSON
Text book image
Fundamentals of Management (10th Edition)
Management
ISBN:9780134237473
Author:Stephen P. Robbins, Mary A. Coulter, David A. De Cenzo
Publisher:PEARSON
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Understanding Business
Management
ISBN:9781259929434
Author:William Nickels
Publisher:McGraw-Hill Education
Text book image
Management (14th Edition)
Management
ISBN:9780134527604
Author:Stephen P. Robbins, Mary A. Coulter
Publisher:PEARSON
Text book image
Spreadsheet Modeling & Decision Analysis: A Pract...
Management
ISBN:9781305947412
Author:Cliff Ragsdale
Publisher:Cengage Learning
Text book image
Management Information Systems: Managing The Digi...
Management
ISBN:9780135191798
Author:Kenneth C. Laudon, Jane P. Laudon
Publisher:PEARSON
Text book image
Business Essentials (12th Edition) (What's New in...
Management
ISBN:9780134728391
Author:Ronald J. Ebert, Ricky W. Griffin
Publisher:PEARSON
Text book image
Fundamentals of Management (10th Edition)
Management
ISBN:9780134237473
Author:Stephen P. Robbins, Mary A. Coulter, David A. De Cenzo
Publisher:PEARSON
SEE MORE TEXTBOOKS