Microsoft Excel Major Assignment - Glen Morris Diabetes Association

The Glen Morris Diabetes Association is in the process of preparing its organization’s proposed budget for the next fiscal year.

For this major course assignment, you have been provided with an Excel workbook that was created by someone with minimal knowledge of spreadsheet formulas.

• Add appropriate formulas and formatting to the spreadsheet to make the budget process more efficient and effective.
• Save your work often and back up your assignment
• Submit the assignment on or before the Due Date 

Using the tools in Microsoft Excel, follow the instructions below to add the appropriate formulas to the spreadsheet. 

1) Download the word document named, Excel-ASN-START.xlsx, from our course shell.

2) Rename this document, using the FILE tab, Save As icon to save it under a new name

For example: Excel Assignment - MSauve-Sec1.pptx. 

Create a Documentation Sheet to include the company logo; you as the author of the workbook; a date using a date function and then reformat it to the long date; and include a purpose for this workbook. You can copy the Glen Morris logo from the PowerPoint Assignment “Start” file. Change the color of the worksheet tab as well. Documentation sheet is always the first sheet, similar to a cover page in Word. It is for information only.

3) Back up all of your assignment files to a memory stick and/or your home computer.

4) Complete the following instructions for the worksheets of the Excel workbook. NOTE: Each set of instructions is preceded by the sheet tab name to which they apply.

1. In Cell D7, enter a formula that uses an Absolute reference to the “Profit as Percentage of Sale Price” cell, the sale price for the item and estimate of the number of items that will be sold to calculate the estimated profit for “Tee Shirts”.

2. Copy the formula from cell D7 down to D18, to calculate the estimated profit for the other items being sold.

3. In Cell C20, enter a formula to total the estimated number of items sold.

4. In cell D20, enter a formula to total the estimated profits; format all totals.

5. Save your work now.

1.  Enter the formula in cell C23 that calculates the total of all items in column J.
2.  Enter the formula in cell C24 that calculates the total number of people anticipating attending the event from column L.
3.  Enter the formula, in cell C25, that will calculate the total sale of tickets based on the total number of people attending and the ticket price indicated in cell C27
4.  In cell H23 enter a formula that will automatically enter the Total Item Value.
5.  In cell K23 enter a formula that will automatically enter the Total Item Value.
6.  In cell H24 enter a formula that will automatically enter the estimated Total Attending amount.
7.  In cell K24 enter a formula that will automatically enter the estimated Total Attending amount.
8.  In cell H25 enter a formula that will enter the Total Estimated Tickets amount.
9.  In cell K25 enter a formula that will enter the Total Estimated Tickets amount.
10. Format this spreadsheet appropriately and for understanding as discussed in class.

Project Details

1. In the Budget Estimate Working Sheet Template (starts in Row 17), there are 7 cells with a yellow background. (E17 thru E23). Determine appropriate formulas for these cells that will calculate the various Total Expense values.

2. In cell B7 enter a formula to display the previously calculated Total Expense for Salaries and Benefits SUB TOTAL from the Working sheet.

3. In cell B8, enter a formula to display the previously calculated Total Expense for Program Costs SUB TOTAL from the Working sheet.

4. In cell B9, enter a formula to display the previously calculated Total Expense for Administration SUB TOTAL from the Working sheet.

5. In cell B10, enter a formula to display the previously calculated Total Expense for Capital Costs SUB TOTAL from the Working sheet.

6.In cell B11, enter a formula to display the previously calculated Total Expense for Miscellaneous Costs SUB TOTAL from the Working sheet.

7. In Row 12 columns B through G, enter formulas to add up the column data.

8. In Column H row 7 through 11, enter formulas to add up the appropriate row data.

9. Format appropriately.

10. Save your work now!

1. In cell B12, enter a formula to add up all the data in column B.
2. From the data, in cell range A7 to B10, create a 3D Pie Chart that communicates the distribution of the different revenue sources. Make sure that your chart displays the Value, Percentage and Category Name in each slice of the pie.
3. Add an appropriate title to the 3D Pie Chart.
4. Move the 3D Pie Chart to a new worksheet and name it “Revenue Sources”.
5. Move the Revenue Sources worksheet between the Legacy Fund and Eat Healthy for Life worksheets.
6. Format as necessary.
7. Save your work now.

1. In cell B12, enter a formula to add up all the expenses.
2. In cell B21, enter a formula to add up all the revenues.
3. In cell B22, enter a formula to determine the potential Profit/Loss for the event.
4. From the data, in cell range A6 to B11, create a 2D Clustered Column Chart that communicates a comparison of the different expense types.
5. Create an appropriate title for the 2D Column Cluster Chart. 
6. Create a new worksheet tab named Expense Types.
7. Move the 2D Column Cluster Chart to the Expense Types worksheet.
8. Move the Expense Types worksheet between the Eat Healthy for Life and Budget Summary worksheets.
9. Format as necessary.
10. Save your work now.

1. In cell E7, enter the Consolidation Formula that will bring the Total Promotional Sales amount to this Budget Summary Sheet.
2. In cell E8, enter the Consolidation Formula that will bring the Total Silent Auction Ticket Sales - Purple       scenario amount to this Budget Summary Sheet.
3. In cell E9, enter the Consolidation Formula that will bring in the Outreach Initiative:   Total for Requested Income OTF from the Outreach Initiative worksheet to this budget Summary worksheet. 
4. In cell E10, enter the Consolidation Formula that will bring the Outreach Initiative Revenue from All Other Income Sources to this Budget Summary worksheet. (Columns D to G)
5. In cell E11, enter the Consolidation Formula that will enter the Total Revenues for the Legacy Fund into this Budget Summary worksheet.
6. In cell E12, enter the Consolidation Formula that will enter the Total Estimated Revenue, from the Eat Healthy for Life Initiative to this Budget Summary worksheet.
7. In cell E13, enter a formula to add up the Proposed Revenues.
8. In cell E17, enter the Consolidation Formula that will enter the Salaries and Benefits, from the Outreach Initiative worksheet to this Budget Summary worksheet.
9. In cell E18, enter the Consolidation Formula that will enter the Program Costs from the Outreach Initiative worksheet to this Budget Summary worksheet.
10. In cell E19, enter the Consolidation Formula that will enter the Administration Costs from the Outreach Initiative worksheet to the Budget Summary worksheet.
11. In cell E20, enter the Consolidation Formula that will enter the Capital Costs from the Outreach Initiative worksheet to this Budget Summary worksheet.
12. In cell E21, enter the Consolidation Formula that will enter the Miscellaneous Costs from the Outreach Initiative worksheet to this Budget Summary worksheet.
13. In cell E22, enter the Consolidation Formula that will enter the Profit/Loss from the Eat Healthy For Life Initiative worksheet.
14. In cell E23, enter a formula to add up the Total Expenses from cells E17 thru E22.
15. In cells C24, D24 and E24, enter formulas to calculate the Surplus/Loss.
16. Format this spreadsheet as necessary.

1) Review all the worksheets for completeness and formatting and proofread anything that you typed.
2) Check to be sure you have retained all the existing formatting in the modified cells.
3) Remember – you must create formulas. You will not be given credit for numbers derived from a calculator; at the same time, if you make an error early in the process but later use the correct formula, you will be credited.
4) Upload your assignment file to the eConestoga Drop Box for the Excel assignment by the deadline date.