United Way of the Desert 's (Links to an external site.) mission is to break the cycle of poverty in the Coachella Valley through engaging the caring power of the community. United Way of the Desert raises funds used to support their own programs as well as support programs at other non-profit organizations in the areas of health, education, and financial stability.

United Way's revenue is made up of several components: Average Donation $225 per donor Special Event Revenue $250,000 Grants $200,000 Tocqueville Donors $200,000 Miscellaneous Income $25,000

Expenses consist of: Overhead/Fixed Costs $450,000 Pledge Loss/Bad Debt 6% of total revenue Donor Expense $25.00 per donor

The Number of Donors is your decision variable. Total Funds Available to fund programs equals Total Revenue minus Total Expenses.

Your task is to create a spreadsheet model for a Budget Summary using the given information. Follow the directions in each question. Be sure to show all of your work in your excel file.

Please show your work for each question in the appropriate tab:

1. Build a spreadsheet model that calculates the Total Funds Available for 100 donors. Save your spreadsheet in the UW Budget Summary tab. Be sure to use principles of good spreadsheet design from the chapter. The parameters are provided for you. Build your model starting with the decision variable. Use clear, simple formulas dividing revenue and cost into several cells. Formulas should reference the parameters above.

What are the Total Funds Available when there are 100 donors? Enter the value only rounded to the nearest whole number.

2. Using your spreadsheet model, create a one-way table for computing Total Funds Available for various numbers of donors ranging from 0 to 1,000 in increments of 50. Format the values for Total Funds Available as currency.

What is the range of donors needed to obtain $250,000 in Available Funds?

3. Create a two-way data table with the same column values for number of donors from Q2. Vary the rows with pledge loss expense ranging from 4% to 8% in increments of 0.5% (Be sure your number is formated as a percentage). Format the values in the table as currency and add a heat map (Home - Conditional Formatting - Color Scale)

In the two-way data table, if pledge loss is 8.0%, what is the range of donors needed to obtain $250,000 in Available Funds?

A B D 1 UW Budget Summary 2 Parameters 3 4 Campaign Revenue $225.00 per donor 5 Special Event Revenue $250,000.00 Fixed Revenue (FR) 6 Grants $200,000.00 FR 7 Tocqueville Donors $200,000.00 FR 8 Miscellaneous income $25,000.00 FR 9 10 Overhead/Fixed Costs $450,000.00 FC 11 Pledge Loss/Uncollectat 6.00% of total revenue 12 Donor Expense $25.00 per donor 13 14 15 Model 16 17 Number of Donors 100 18 19 Campaign Revenue 20 Special Event Revenue 21 Grants 22 Tocqueville Donors 23 Miscellaneous income 24 Total Revenue 25 26 Overhead Fixed Costs 27 Pledge Loss/Uncollectable 28 Donor Expense 29 Total Expenses A B D 1 UW Budget Summary 2 Parameters 3 4 Campaign Revenue $225.00 per donor 5 Special Event Revenue $250,000.00 Fixed Revenue (FR) 6 Grants $200,000.00 FR 7 Tocqueville Donors $200,000.00 FR 8 Miscellaneous income $25,000.00 FR 9 10 Overhead/Fixed Costs $450,000.00 FC 11 Pledge Loss/Uncollectat 6.00% of total revenue 12 Donor Expense $25.00 per donor 13 14 15 Model 16 17 Number of Donors 100 18 19 Campaign Revenue 20 Special Event Revenue 21 Grants 22 Tocqueville Donors 23 Miscellaneous income 24 Total Revenue 25 26 Overhead Fixed Costs 27 Pledge Loss/Uncollectable 28 Donor Expense 29 Total Expenses