I Have two excel assignments to be finished according shown in the files attached. Independent Challenge 1 ? Sunlight Systems

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

Excel Independent Challenge 2

Create the worksheet (name it DATA) shown below. Wait to create the Pivot Table until

instructed to do so.

You work for Brain Trust, a home-based business that creates and manufactures 3-D

printed toys for all age groups. The Excel spreadsheet just created shows the category,

related age-group, inventory quantity, and sales price for each item.

1. Apply Data Bars (Hint: Conditional Formatting) to the Total column. (Your choice

of color).

2. Apply Conditional Formatting of your choice to the Age Group Column so that it

is easy to see each Age Group.

3. Insert a new worksheet into your workbook. Name it REPORT..

4. Create the illustrated Pivot Table sorting on Category and Total.

5. Format the Totals in the Pivot Chart as Accounting.

6. Change the Quantity of STEM Card Sets to 500 and Foam and Foam and Clay

Kit to 100.

7. Apply Data Bars (Hint: Conditional Formatting) to the Total column. (Your choice

of color).

8. Apply Conditional Formatting of your choice to the Age Group Column so that it

is easy to see each Age Group.

9. Create each of the graphs described below and put on a separate worksheet

labeled using the “letter” shown beside the description. In other words, you will

have a worksheet labeled “a”, “b”, etc. You will add a descriptive title to each

chart and a legend of your choosing.

a. Create a Pie Chart that shows each Category as a Percentage of Total

Sales.

b. Create a Pie Chart that shows Quantity of each item as a percentage of

total quantity.

c. Create a Line Chart that shows the total in dollars of each item in the

Toddler category.

d. Extra-Credit: Create a 2-D column chart that shows Quantity (of units by

Category).