Projected Profits Worksheet Assumptions Table Gross Salary
Sheet1projected Profits Worksheetassumptions Table Gross Sales Increas
Create a projected profits worksheet using the template available in D2L under the content tab entitled Excel Assignment 1 Template. 2. Bold all text in column A. 3. Under the Assumptions table enter: 10% for Gross Sales Increase 5% for Wages Increase 5% for Cost of Goods 2% for Advertising Increase in column B next to each category and format as percent with 1 decimal place. 4. This projected profits worksheet covers a 5 year period so in cell C10 enter the year 2021. Then copy cells B10 and C10 to cells D10, E10, and F10 respectively. Used what you learned in the Excel tutorials so you end up with the years 2020 – 2024 as column headings in row 10. 5. In cell B15, calculate Total Income (use the sum function). Format as currency with 2 decimal places 6. In cell B24, calculate Total Expenses (use the sum function). Format as currency with 2 decimal places 7. In cell B26 calculate Net profit which is Total income minus Total expenses. Format as currency with 2 decimal places 8. Gross Sales are predicted to increase by 10% per year for the next 4 years. In cell C12 calculate projected Gross Sales. Use absolute cell referencing and the percentage Gross Sales Increase from the Assumptions Table to calculate the projected Gross Sales for 2022. Do not use the actual percentage (10%) in the formula. Instead use the cell address where the Gross Sales Increase is stored in the table. Then copy the formula to the remaining year columns. Adjust column widths as necessary to properly display data. 9. Sales incentives are not expected to increase so just copy the 5,000 in cell B13 to the remaining year columns. 10. Similar to what you did in #8 above, calculate projected Wages, Cost of Goods Sold, and Advertising for the years 2021, 2022, 2023, and 2024. Format as number with commas and 2 decimal points. Use absolute cell referencing and percent increase from the Assumptions Table. 11. Rent and Supplies are not expected to increase so just copy to 2020 numbers to the remaining year columns. 12. Copy the formulas for Total Income, Total Expenses, and Net Profit to the columns for the years 2021, 2022, 2023, and 2024. Format as currency with 2 decimal places. 13. Create a line chart that graphs Total Income, Total Expenses, and Net Profit for the years 2020 – 2024. Place the chart just below the Net Profit row. Add a chart title “Projected Profits” and make sure the legend correctly identifies the chart lines as Total Income, Total Expenses, and Net Profit. 14. Put your name at the top of the spreadsheet. 15. Save the file with your last name and first name as the file name. Using the name John Smith as an example, the Excel file name would be SmithJohn.xlsx. Upload your Excel file to the D2L Dropbox for Excel assignment 1 by the due date listed in your calendar and the dropbox.
Paper For Above instruction
Sheet1projected Profits Worksheetassumptions Table Gross Sales Increas
The purpose of this assignment is to create a comprehensive projected profits worksheet using a specified Excel template, incorporating assumptions, calculations over a five-year period, and visual representations through a chart. This task involves detailed steps including data input, formula application, formatting, and chart creation, aimed at developing a financial projection model for a small business over the years 2020 to 2024.
Initially, the student must open the provided Excel template and ensure all text in Column A is bolded for clarity and emphasis. The assumptions table should then be populated with specific percentage increases for key financial variables: 10% for Gross Sales Increase, 5% for Wages Increase, 5% for Cost of Goods Sold Increase, and 2% for Advertising Increase. These percentages are formatted with one decimal and displayed as percentages, providing the foundational assumptions for future projections.
The setup requires entering the starting year, 2021, in cell C10, and then referencing and copying data accordingly to generate a sequence of years from 2020 through 2024, aligning with the assignment's temporal scope. The student will employ basic Excel functions like SUM to calculate Total Income and Total Expenses, formatting the results as currency with two decimal places for clarity. The net profit is then derived by subtracting total expenses from total income, again formatted appropriately.
Subsequently, projections are calculated for each variable, including Gross Sales, Wages, Cost of Goods Sold, Advertising, Rent, and Supplies. Using absolute cell referencing, formulas incorporate the percentage increases from the assumptions table to generate forecasts for each year. For variables expected to remain unchanged, values are simply copied across years. These calculations support a detailed financial forecast over the designated period.
Further, the student must replicate aggregate formulas for total income, total expenses, and net profit across all projection years, maintaining consistent formatting. The final component involves creating a line chart that visualizes Total Income, Total Expenses, and Net Profit across 2020 to 2024, with appropriate titles and legends, positioned just below the net profit row. Additionally, the student should add their name at the top of the spreadsheet and save the file with their last and first names integrated into the filename, then upload the completed file to the designated dropbox before the deadline.
References
- Microsoft Support. (2023). Create a chart from start to finish. Microsoft. https://support.microsoft.com
- Excel Easy. (2023). Excel formulas for financial projections. https://www.excel-easy.com
- Investopedia. (2022). How to create financial forecasts in Excel. https://www.investopedia.com
- Wall Street Journal. (2023). Best practices for financial statement analysis. https://www.wsj.com
- AccountingTools. (2021). Tutorials on income statement projections. https://www.accountingtools.com
- Small Business Administration. (2022). Financial forecasting step-by-step. https://www.sba.gov
- Chandoo.org. (2020). Excel dashboards and charts. https://www.chandoo.org
- Corporate Finance Institute. (2023). Financial modeling and analysis. https://corporatefinanceinstitute.com
- Tracy, B. (2019). The complete guide to financial projections. [Book].
- Financial Times. (2023). Key principles in financial planning. https://www.ft.com