This is an assignment from Finance 4020 Advanced Excel. Its from chapter 6.

Need help ASAP.

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 graphically 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 page layout functi - You can use as many support worksheets as you would like as long as they are set up in a way that facilitates updating the - Do not change anything on the Instructions tab. - All support data for your charts should reference the Instructions using a lookup function, referencing a cell or some othe 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 2 3 4 5 6 7 8 9 10 11 12 Gross Margin Average Monthly Fixed Costs Net Income target as a percent of revenue 201,909.0 206,399.8 214,493.8 228,469.6 237,305.9 241,193.5 249,461.8 254,657.0 259,955.5 265,427.8 276,444.9 290,264.2 627,719.0 668,478.3 686,657.8 690,461.9 726,733.0 740,686.2 762,276.3 804,743.5 825,225.7 831,123.0 864,237.4 875,597.4 157,474.0 159,560.0 168,033.1 171,100.1 176,992.7 189,744.9 198,721.2 203,936.5 215,833.5 222,975.6 240,708.7 247,338.0 25% 65,000.00 10% 30% 150,000.00 10% 40% 75,000.00 10% 1) Create a chart for each region and total company comparing month over month revenue to breakeven and the target rev 2) Area chart showing month over month net income spread by region. Overlay the consolidated net income by month. (C Sales Persons Productivity East Actual Referrals Customer Sales Calls Total Customer Contacts Contracts Signed Cancellations Current Month Target 25 50 75 12 1 25 40 65 33 West Actual Referrals Customer Sales Calls Total Customer Contacts Contracts Signed Cancellations Current Month Target 40 40 80 35 - 30 35 65 33 - South Actual Referrals Customer Sales Calls Total Customer Contacts Contracts Signed Cancellations Current Month Target 10 20 30 10 - 5 15 20 7 - 3) Comparison of productivity to targets with colors or icons emphasizing areas that are doing well and those that need improvements. (These indicators are not comparable to each other. You should compare only to the target.) 4) A chart or schedule indicating how successfully customer contacts are being converted into sales by region and total company. 5) A chart or schedule indicating the percent of customer contacts that are coming through referrals. Credit and Collections Accounts Receivable Stats Month January 1 A/R Balance 1,085,812.20 Sales Per Day 32,903.40 DSO 33 February March April May June July August September October November December 2 3 4 5 6 7 8 9 10 11 12 1,275,806.98 1,354,300.57 1,562,378.53 1,597,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 34,481.27 35,639.49 36,334.38 38,034.39 39,054.15 40,348.64 42,111.23 43,367.16 43,984.22 46,046.37 47,106.65 37 38 43 42 37 35 32 34 33 29 27 7) Sparkline graphically depicting the monthly A/R balance 6) Month to month comparison of DSO to Target. 7) Month to month past due trend. (Past due is any aging over 30 days) 8) Compare the amount in each aging categories in December to November month by dollar and % of the total, flag catego An increase in the 0 to 30 category is good and increase in past due categories is bad (past due is```` 9) Same as 8 except compare December to the average in each aging 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 Creativity - use of many different types of charts and creative ways to communicate Organization - how well organized your charts are (Sized, lined up and pleasing to the eye) Communicate - do the charts deliver the right message to management Presentation - well formatted with titles, borders etc. Followed Instructions 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 50% 10% 10% 10% 10% 10% 100% ta graphically You can find examples of Dashboards by googling Dashboards and selecting images u can use page layout functions to print two pages). that facilitates updating the model monthly. rencing a cell 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 1,381,391.0 1,413,199.5 31% 290,000.00 10% reakeven and the target revenue (4 charts). ed net income by month. (Combo Chart) Breakeven revenue = fixed cost / gross margin Target revenue = fixed costs / (gross margin - the target percent) East Year to Date Target Actual 200 300 500 117 24 250 400 650 330 - West Year to Date Target Actual 356 263 619 364 10 270 315 585 300 - South Year to Date Target Actual 32 128 160 94 - 45 135 180 63 - well and those that need o the target.) ales by region and total d Collections DSO Target 30 0 to 30 781,784.78 30 to 60 162,871.83 60 to 90 86,864.98 over 90 54,290.61 30 30 30 30 30 30 30 30 30 30 30 956,855.24 1,110,526.46 1,203,031.47 1,309,904.27 1,112,652.83 1,101,517.99 1,104,998.80 1,164,841.81 1,161,183.29 974,801.60 1,017,503.65 - 191,371.05 162,516.07 203,109.21 175,718.87 202,300.51 141,220.25 134,755.95 162,193.16 188,692.28 200,301.70 127,187.96 - d % of the total, flag categories that are improving or getting worse. 30 30 30 30 30 30 15 3 3 3 3 3 102,064.56 67,715.03 140,614.07 79,872.21 72,250.18 ### 67,377.98 103,213.83 72,573.96 120,181.02 76,312.77 25,516.14 13,543.01 15,623.79 31,948.88 57,800.15 28,244.05 40,426.79 44,234.50 29,029.58 40,060.34 50,875.18 argin - the target percent) Name