Capstone assignment( Admin2390- Capstone Experience for APT

Excel Independent Challenge 1

Create the six-month budget shown in the picture below. Sunlight Systems is a small-

consulting company that advises consumers and business about energy options. Apply

the following formatting to the spreadsheet:

1. Landscape Orientation

2. Apply the Integral Theme

3. Use Median Color Theme to apply shading as shown. (Your choice).

4. Enter Formulas to derive Totals cells through worksheet.

5. Profit/Loss formula is Total Revenues minus Total Expenses.

6. Save the file as Excel Independent Challenge 1.

Answer the following questions and insert the answers in the question blanks at the

bottom of the spreadsheet:

1. In February, you estimate that three new business will ask for consultations at an

average rate of 1200 per consultation. These contracts are in addition to the

current revenue for consultations. You project that the contract revenue

generated in February will increase by 5 percent in March, 10 percent in April,

then 20 percent in May and June. Calculate all increases based on February

revenue. Hint: The formula for cell D6 is =$C$6*1.05. What is the total revenue

for consulting in cell H6? Enter the cell address H^ in cell B20.

2. Make the salaries expense for both May and June $15,000. What is the total

salaries expense for the six-month period after the change.

3. Increase the advertising costs for March by 30% of the costs for February. Hint:

The required formula is =C14*1.3. Enter the answer to Question 3 using a cell

address.

4. Double the equipment lease for March. What is the total projected profit/loss for

March after the increase? Insert a cell address in B23 which is the answer to

question 4.

5. Create a pie chart (using totals after steps 1-4 above are applied) that shows the

percentage of each expense for the six-month period as a part of the total

expense. (What percentage was Salaries, Rent, etc. of the total expense?) Insert

the chart under the spreadsheet. Add a title to the chart. Add labels for each pie

segment. Show percentages for each segment. Display the labels outside the

pie. HINT: Think carefully when you are choosing your ranges for the pie chart.

1