Project | Accounting homework help
- Invoice Status: This field indicates if the customer has paid the invoice (paid) or not (open).
General instructions for the case You will be using Excel to build an interactive dashboard to answer a set of multiple-choice questions in eLearning as well as 2 short answer questions. You will start with an Excel file containing a set of sales transactions and then modify the data file as instructed. Each section needs to be completed in its entirety before proceeding to the next section. You will have to submit your excel file to eLearning when you are done. This will be requested as one of your questions. Be sure to save your Excel file frequently. Step 1 - Prepare the Sales Data worksheet. In this step, you will open the Excel file, establish the table, and find and fix any errors in the data. · Open your assigned Excel file. · Convert the data on the worksheet to a table called RawData and rename the worksheet Sales Data . Now, it’s time to analyze your data for issues. It is your job to determine what the errors are and to correct them. Review the data provided and the description of the data in the data table. Hint: the Division, Product, and Sales Tax Amount columns should be your focus. If you find an error, then you need to correct the data. You will need to use the other data in the worksheet and the data dictionary to help determine data errors. Be sure to save your file with the corrected data. HINT: Make a note of what you had to fix and how you found the errors. Step 2- Working with Divisions and Regions. · Create a pivot table using the RawData table. Place the pivot table in a new worksheet called Div_Region. The pivot table should display the Division in the rows of the pivot table. The columns of the pivot table should display the total sales revenue for each region and the percentage of that region’s total sales revenue of the grand total sale revenue. Do not separate this by year. For example: The grand total of all sales revenue for all divisions and regions is $5000. Division A-South Region has $500 sales revenue for the two years. Division A would have 10% of the sales revenue. · Add a slicer to this PivotTable for Year Points will be awarded not only for the correct data but also for proper formatting of the values presented, as well as custom labels of the field. Include Grand Totals for rows and columns. Use common formatting standards and common sense labels. Step 3 – Working with Products Insert a column in your RawData table (at the far right) to calculate gross profit in dollars for each sale. (HINT: Think about how to treat sales tax – is this revenue?) HINT: Refresh your table and be sure the RawData table includes this new column. Create a new pivot table from the RawData. Place this PivotTable in a new worksheet called Products. Use the data to prepare an analysis of the total product sales revenue for the current year only for each product. Show the sales revenue in order from greatest to least. Then insert a calculated field in the pivot table that calculates the gross profit % for each product. This must be a calculated field on your pivot table and cannot be a column in your RawData. HINT: You will use the gross profit dollars column you added above. Points will be awarded not only for the correct data and formula but also for proper formatting of the values presented, as well as custom labels of the field. Include Grand Totals for rows. Step 4 – Forecasting (SEE NOTE BELOW FOR MAC USERS)
If you are MAC USER – see the note below:
You have been asked to predict the total revenue for the 1st 6 months of 2025 based on the sales revenue of 2024. You are going to use the Forecast Sheet tool in Excel. Before you can do this, you need to prepare some data. Create a new worksheet called Forecast Sheet. On this worksheet, prepare a table with the Total Sales Revenue by month for the current year. HINTS: You must set the table up with proper dates (Use the last day of the month) and other proper labels. See the example below. For example: (Not the actual numbers) Once your data is ready, select the data and select Forecast Sheet from the Data tab of your Ribbon. You can leave all defaults as is on the Forecast Sheet tool. Be sure to set the Forecast end to 6/30/2025. Your result will be a new forecast for the 1st 6 months of 2025. HINT: If you get a message about dates not being consistent, use the last day of each month as your date. Place this forecast chart on your dashboard. (Element #3 below). NOTE: If you are a MAC user, you will need to create the table of sales revenue data, and then make a line chart that shows all revenue for 2024 by month and add a linear trendline. Format the trendline to forecast forward for 6 months. Creating the Dashboard Create a new worksheet titled Dashboard and make this the first sheet in your workbook. This is where you will combine several elements in a visual representation. There will be 4 charts on your dashboard. The elements should be arranged in a visually appealing manner and all charts should be able to be seen at the same time when the workbook is opened. Please use a consistent color scheme with all elements. Use titles and labels where appropriate along with reasonable axes. Grades will be assigned based on your proper choices. For some of the elements, you are not creating a PivotChart and may have to create a separate table or calculation to prepare the element. You can create a new worksheet called Other Calcs to help you create that data. Element #1: Total Sales Revenue by Region for the Current Year Create a static (not a pivot chart) bar chart that presents Total Sales Revenue by Sales Region for the Current year. Element #2: Proportion of Sales Revenue by Product On your “Other Calcs” worksheet create a new pivot table from the Raw Data Table. Use Product in the rows and present the total sales revenue for each product in the current year as a % of the total sales revenue. Sort it from largest to smallest percentage. To make it cleaner, only show those products whose sales are 15% or more of the total in the current year and group the other products together to represent “ all other” products. Make a chart of this and put it on your dashboard. Be sure your final chart does not show the details of what is in “All other”. You must determine the proper type of chart to show. Make a note of how many products had to be combined in the “all other” group. Element #3: Forecast The 3rd element of the analysis is the forecast of total sales revenue prepared above. NOTE: If you are a MAC user, you will have a line chart with a trendline. Element #4: Your choice. Using the RawData provided, you select the 4th chart element to be presented that includes an analysis of something related to either the distribution channel or the payment status. The analysis should be reasonable, and you will be explaining it and your reasoning as part of your assignment. I should be able to understand your analysis based on your chart elements (title, axis, labels, etc.) ACCT 3312 –Fall 2024 1 Final Project
- CMGT 556 Entire Course
- critical thinking question for madam- professor
- Can you do this
- In a minimum of 500 words Man versus society is a shared theme of Frankenstein and One Flew Over the Cuckoo’s Nest. m
- CJS 240 Week 6 Checkpoint Probation
- BIO 315 Week 3 Individual Assignment Yeast Culture Lab
- ACC 561 Week 1 Individual WileyPlus Exercises BE1 7, BE1 8, BE1 9
- ACC 410 WEEK 5 FINAL PAPER AUDIT PLAN KEYSTONE COMPUTERS
- Assignment 1: Biology Article
- Zappos