ECOFP 1100 Capella University Data Visualizations Creating Budget Graphs Excel Task

Content Type

User Generated

User

aznd091081

Subject

Humanities

Course

ECO FP 1100

School

Capella University

Department

ECOFP

Description

Introduction
Data Visualization – Revisited
You're already familiar with data visualizations that inform your decision making. On your phone or laptop, you might look at a pie graph to check how much cloud storage you're using versus how much is still available. Or you might look at line graphs online to see how the weather is forecast to change temperature over the next week. These visualizations are built on data that your computer or the weather service compiles into a specific dataset and then analyzes and organizes into the graph or chart you see so that it’s easy to understand.Visualizing data is all about making it easier to communicate the most important information. If you're trying to find out the weather for your weekend plans, you need the right data, presented in an intelligent way to know if you should go out or stay home. When working with datasets, it's imperative to keep in mind who the audience is, what information they might value the most, and what you want their takeaway to be.For example, your office might be upgrading a software program and the chief financial officer (CFO) may ask for a cost-benefit analysis of upgrading all at once versus a gradual rollout of the upgrade over a number of months. What is the most critical information is the CFO likely to want? How would be the best way to communicate this to them?When it comes to thinking critically about the data you analyze and using it to make decisions, your problem-solving skill can help you examine how a particular set of data relates in the most meaningful way for you and your audience. By using data to identify and frame the problems that concern a group, you can help them verify what matches and what contrasts in the dataset, identify clear trends, draw conclusions about basic relationships, and make predictions about patterns.Just like with your personal finances, if you are dealing with financial considerations in your career, you can use the same process to make visualizations about potential budget plans or even to recognize how much certain areas, resources, or even lack of investment could end up costing the company.
Presenting Professionally
Throughout the course of your career, you are most likely going to be called upon to present on some topic relevant to your work. This could be a project, a department, or a proposal for something new at your workplace. No matter what the specifics are, it will be critical that you present yourself and your material professionally. This means making sure you are using appropriate language and terminology for the topic and the audience (it is important to speak to your audience at their level). It also means that the information and visuals you present should be well polished, well researched, well cited, and well organized. Haphazard visuals, incomplete data, plagiarized research, or disorganized presentations not only make it difficult for the audience to understand you, it also makes you appear unprofessional.So when presenting:
- Consider your audience.
- Consider the right information.
- Polish your words.
- Polish your visuals.
- Cite your sources.
- Stay organized.
These are basic points, but if followed, these can help you act professionally and deliver a quality presentation.For this assessment, you will be drawing upon your problem-solving, agility, and technology skills to design a budget that works and communicate in an effective way.
Overview
In our careers, as our responsibilities grow and we take leadership of a department a natural request is for a budget proposal each fiscal year. Budgets provide parameters for us to achieve our goals. Spend too much and you will find yourself in a deficit. Spend too little and you could have a surplus, which may not be a good sign if an organization's goals have not been met. Using technology can help you stay organized throughout your analysis, as well as help visualize financial data. Displaying the data visually will allow you to observe general trends in your department finances and to communicate about them effectively to appropriate stakeholders.For this assessment, you will be using Excel as your technology tool to analyze department data and display that data visually. There are many other technologies available for professional use that can be used for this type of analysis. The approach to learning and working with Excel software in this course is one you can apply to other technology tools in your life and career.Your goal for this assessment is to build your confidence and practice in the technology skill by conducting a department financial analysis using a technology tool. You will use Excel to organize a quarterly and an annual budget, visualize this financial data using charts and graphs, and discuss the results of your financial analysis, including progress toward your selected department financial goal.
Preparation
Read the scenario below to understand the context for this assessment. Review the Assessment 4 Template [XLSX]. This completed template will be one of your deliverables for this assessment.
Scenario
You are the associate administrator of a large department in the City of Acme with an operating budget of 1.8 million dollars. Your boss, the administrator, has asked you to create a presentation with graphs that demonstrate how you should allocate next fiscal year's budget.
Instructions
Using what you have learned about budgeting, Excel, and creating data visualizations, complete the Assessment 4 Template [XLSX]. Then create a 5–10-minute video in which you present your proposed budget to the department and leadership.For this assessment, complete the following steps:
- Step 1: Use the Assessment 4 Template [XLSX] to review the current annual budget. After your review the current budget, create a new Proposed Budget.
  - Scoring guide criteria: Create a Proposed Budget based on a professional financial analysis of a Year-to-Date Budget.
    - You have the original budget, the 9-month budget (or Year-to-Date at 9 months), and the empty budget spreadsheet for the next year.
    - Please read the comments (notes) in the cells (indicated by red triangles in the upper right corner of the cells).
    - Based on this data, how will you propose the budget be updated in the following fiscal year? Consider the following before filling in the cells for the next fiscal year:
      - It is rumored there will be a 3-percent increase in cost of living. Consider budgeting this increase for personnel cost.
      - There were many outdated computers that completely crashed in the current budget. This is why there is an increase at the 9-month mark. It is up to you if you plan for more issues next year as only two-third of the computers were replaced in the current year.
      - You will not need as many vehicles in the next year. If each vehicle is valued at about 36,475 dollars, consider a conservative amount to budget for next year. If you do not add more vehicles, consider a substantial increase to Vehicle Maintenance in the new year for repairs.
- Step 2: Create graphs of the Current Year Budget, the Year-to-Date Budget, and the Proposed Budget.
  - Scoring guide criteria: Create appropriate graphs to illustrate the findings of a professional financial analysis.
    - You should use Excel to create these graphs in the Assessment 4 Template [XLSX].
    - Remember, you may wish to display these graphs during your video. So, it is worth considering the audience for your video and how the graphs will appear during the presentation.
- Step 3: Make a video presentation using Kaltura (or another video recording method with which you are familiar) in which you review your analysis and Proposed Budget. At the end, briefly explain why you chose the graphs you did.
  - Scoring guide criteria: Explain the results of a professional financial analysis, including overall progress and why the Proposed Budget is the best possible forecast for the new year.
    - Make your recommendation for the Proposed Budget with your logic for the proposed increase or decrease in the new budget.
    - Discuss whether your final budget is conservative (meaning in the worst possible scenario it would still work) or aggressive (meaning that all budget metrics needs to be hit for you not to be in a deficit next year).
  - Scoring guide criteria: Explain how selected graphs are the best choice to communicate financial information.
    - Discuss the graphs that you chose to present the data from the three budgets in the Assessment 4 Template.
      - How do they help the audience better understand the presented financial data?
      - How are they better than other types of graphs you considered?
  - Scoring guide criteria: Address the appropriate audience, using familiar, discipline-specific language and terminology.
    - Are you speaking to the audience in the scenario (that is, department members and leadership) in your presentation?
    - Is the language you are using appropriate for the scenario and your role as an assistant administrator?
    - Are you using appropriate economic and finance terminology?
Remember, you need to submit both completed Assessment 4 Template and your 5–10-minute video presentation for this assessment.
Additional Requirements
Your assessment should also follow the following requirements:
- Communication: Communicate in a manner that is scholarly, professional, respectful, and consistent with expectations for professional practice in education. Original work and critical thinking are required regarding your assessment and scholarly writing. Your writing must be free of errors that detract from the overall message.
- Video presentation: 5–10 minutes.
Competencies Measured
By successfully completing this assessment, you will demonstrate your proficiency in the following course competencies and assessment criteria:
- Competency 3: Explain the findings of a personal financial analysis with regard to progress toward a financial goal.
  - Create a Proposed Budget based on a professional financial analysis of a Year-to-Date Budget.
  - Explain the results of a professional financial analysis, including overall progress and why the Proposed budget is the best possible forecast for the new year.
- Competency 4: Create spreadsheets, charts, graphs, or other data visualizations to organize and present financial information.
  - Create appropriate graphs to illustrate the findings of a professional financial analysis.
  - Explain how selected graphs are the best choice to communicate financial information.
- Competency 5: Develop professional written communication in a well-organized text, incorporating appropriate evidence and tone in grammatically sound sentences.
  - Address the appropriate audience, using familiar, discipline-specific language and terminology.
SCORING GUIDE
Use the scoring guide to understand how your assessment will be evaluated.VIEW SCORING GUIDE
CRITERIANON-PERFORMANCEBASICPROFICIENTDISTINGUISHEDCreate a Proposed Budget based on a professional financial analysis of a Year-to-Date Budget.Does not create a partial Proposed Budget.Creates a partial Proposed Budget, or the Proposed Budget is complete but contains multiple errors that make it inaccurate.Creates a Proposed Budget based on a professional financial analysis of a Year-to-Date Budget.Creates a Proposed Budget based on a professional financial analysis of a Year-to-Date Budget. Provides specific and accurate rationale notes about decisions made for the Proposed Budget.Create appropriate graphs to illustrate the findings of a professional financial analysis.Does not create any graphs to illustrate findings.Creates graphs to illustrate findings. However, either does not create at least one graph per budget, or graphs are inappropriate or unclear.Creates appropriate graphs to illustrate the findings of a professional financial analysis. Formatting of graphs may contain minor errors that does not detract from the meaning of the graphs.Creates appropriate graphs to illustrate the findings of a professional financial analysis. Graphs created utilize formatting that improves the clarity of the data depicted.Explain the results of a professional financial analysis, including overall progress and why the Proposed Budget is the best possible forecast for the new year.Did not describe the results of a professional financial analysis in general terms.Describes the results of a professional financial analysis in general terms.Explains the results of a professional financial analysis, including overall progress and why the Proposed Budget is the best possible forecast for the new year.Explains the results of a professional financial analysis, including overall progress and why the Proposed Budget is the best possible forecast for the new year. Specifically addresses whether proposed budget is conservative or aggressive, including why the budget is that type.Explain how selected graphs are the best choice to communicate financial information.Does not explain what financial information the graphs present.Explains what financial information the graphs present, but does not include why they are the best choice to communicate the financial information.Explains how selected graphs are the best choice to communicate financial information.Explains how selected graphs are the best choice to communicate financial information. Notes specific examples of other types of that were considered and the reasoning as to why they would not have been as effective as the chosen graphs.Address the appropriate audience, using familiar, discipline-specific language and terminology.Does not address the appropriate audience, using familiar language and terminology.Focuses on the intended audience wavers, or uses jargon, slang, idioms, colloquialisms, or terminology that inhibits clarity of expression.Addresses the appropriate audience, using familiar, discipline-specific language and terminology.Addresses the appropriate audience, using familiar, discipline-specific language and terminology. Uses precise and accurate vocabulary and effective word choice for articulate, fluent expression.

Tags: Liability Insurance Budget Graphs Data Visualizations Operating Funds Current Year Budget

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached. Please let me know if you have any questions or need revisions.
Kindly use this ziped file in your presentation. Ignore the previous one. In case of any concerns, kinldy let me know. Thank you so much

ECO-FP1100: Assessment 4

Current Year Budget
Personnel
Salaried Employees 8 FTE
Non Salaried Employees 13 FTE
Benefits
Operating Funds
Office Supplies
Utilities
Vehicle Maintenance
Liability Insurance
Contracts
Capital
Computers
Vehicles
Infrastructure
Miscellaneous
Petty cash
Other expenses

Enter data
Populates Automatically
$

1,800,000.00

$
$
$

600,000.00
450,000.00
315,000.00

$
$
$
$
$

30,000.00
45,000.00
900.00
65,000.00
38,000.00

$
$
$

35,000.00
145,900.00
65,000.00

$
$
$

200.00
10,000.00
1,800,000.00

Year-to-Date Budget
9 months of info (3 Qtrs)
Personnel
Salaried Employees 8 FTE
Non Salaried Employees 13 FTE
Benefits
Operating Funds
Office Supplies
Utilities
Vehicle Maintenance
Liability Insurance
Contracts
Capital
Computers
Vehicles
Infrastructure
Miscellaneous
Petty cash
Other expenses

Note: Remember to create graphs for each of the three budgets in this template.
FORECASTING
Years
Current Year Budget
Personnel
Salaried Employees 8 FTE
Non Salaried Employees 13 FTE
Benefits
Operating Funds
Office Supplies
Utilities
Vehicle Maintenance
Liability Insurance
Contracts
Capital
Computers
Vehicles
Infrastructure
Miscellaneous

$

2020
1,800,000.00

$

2021
1,800,000.00

$
$
$

600,000.00
450,000.00
315,000.00

$
$
$

450,000.00
298,500.00
224,550.00

$
$
$
$
$

30,000.00
45,000.00
900.00
65,000.00
38,000.00

$
$
$
$
$

30,000.00
34,762.50
475.00
48,749.99
24,750.00

$
$
$

35,000.00
145,900.00
65,000.00

$
$
$

41,950.00
124,973.00
73,000.00

Petty cash
Other expenses

$
$
$

200.00
10,000.00
1,800,000.00

$
$
$

100.00
3,756.00
1,355,566.49

Note: Remember to create graphs for each of the three budgets in this template.

$
nths of info (3 Qtrs)

1,800,000.00

$
$
$

450,000.00
298,500.00
224,550.00

$
$
$
$
$

30,000.00
34,762.50
475.00
48,749.99
24,750.00

$
$
$

41,950.00
124,973.00
73,000.00

$
$
$

100.00
3,756.00
1,355,566.49

$

2022
1,362,482.98

$

$

300000
597,000.00
134100
30000
25,875.00
50
32499.98
11500
48900
104046
81000

Proposed Budget
Personnel
Salaried Employees 8 FTE
Non Salaried Employe...