Question

My Company Projected Budget - 2019 Expense Category 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. Annual Total Average Minimum % of Total Rent 2800 2800 3000 3000 Electric 700 500 500 800 Phone 300 300 300 300 Supplies 800 600 600 500 Payroll 6000 9000 12000 12000 Total Average Maximum

 Create a new workbook in Excel

 Save workbook as “yourinitialsBUDGET” (replace “yourinitials” with your initials)

 Insert a row between PHONE and SUPPLIES and Enter the following: Parking 80 120 160 160

 Calculate quarter totals and averages o Do not use a function – create a formula that simply uses cell references o Examples: =C1 + C2 + C3 OR = (D1 + D2)/2)

 Determine Maximum for each Quarter using a Function (MAX)

 Calculate Expense Category Totals using a Function (Hint: Use AutoSum)

 Determine Expense Category Average and Minimum using a Function (AVG, MIN)

 Build a formula with an absolute reference for % of total for each category. Base % of total on the total of all categories.

 Format cells as currency with no decimal points  Name the worksheet “Sales” (click on the worksheet tab and select rename)

 Center and Bold column headings, Bold Row Titles. Bold the Totals Row.

 Adjust Column Width if necessary (Hint: Is text truncated or does ####### show in a numeric cell? If so, this means the column needs to be wider)

 Merge and Center each row of worksheet titles across the columns (first 2 rows).

 Format first title line as Times New Roman, 20 point, bold  Format second title line as Times New Roman, 14 point, italic bold

 Add a light yellow fill color to the background of the first title line

 Create an appropriate pie chart for first quarter expenses on the worksheet (Hint: total should not be a piece of the pie).

 (Add a chart title and category labels to pieces of the pie) o Show % on the actual pie chart

 Create a bar chart for all expenses by quarter (on it’s own worksheet o Add chart title and also titles for x and y axis o Increase height/size if needed for readability

Answer 1

-BUDGET - Microsoft Excel - 2 X File Home Insert Page Layout Formulas Data Review View Developer * Cut 1 I - A M Insert Delete Format E AutoSum- A Fill - Sort & 2 Clear Filter Editing Find & Select Formatting as Table Styles Styles Cells L M N O P Q R S T 1st Qtr. 20% Rent 26% Calibri - 11 - A À = = Wrap Text General E Copy Paste Format Painter BI U D. A Merge & Center 9% 88 Clipboard Font Alignment Number 015 for A B C D E F G H I J K My Company 2 Projected Budget-2019 3 Expense 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. Annual Total Average Minimum % of Total 4 Rent $2,800 $2,800 $3,000 $3,000 $11,600 $2,900 $2,800 5 Electric $700 $500 $500 $800 $2,500 $625 $500 4% 6 Phone $300 $300 $300 $300 $1,200 $300 $300 2% 7 Parking $80 $120 $160 $160 $520 $130 $80 8 Supplies $800 $600 $600 $500 $2,500 $625 $500 4% 9 Payroll $6,000 $9,000 $12,000 $12,000 $39,000 $9,750 $6,000 68% 10 Total $10,680 $13,320 $16,560 $16,760 $57,320 11 Average $1,780 $2,220 $2,760 $2,793 12 Maximum $6,000 $9,000 $12,000 $12,000 Projected Budget-2019 13 $14,000 $12,000 a $10,000 $8,000 $6,000 $4,000 $2,000 1% Electric -7% Payroll 5696 _Phone Parking 3% Supplies 1% 7% 14 Rent Electric Expense Category Phone Parking Supplies Payroll 1st Qtr. 4th Qtr. 2nd Qtr. 3rd Qtr. Quarter K Sales Sheet2 Sheet3 N Ready + Type here to search o BOU 100% -_ 1_ - 16:29 ^° 4x ENG 05-02-20202 WK OP 9 e

-BUDGET - Microsoft Excel - 2 X File Home Insert Page Layout Formulas Data Review View Developer % Cut M Cell Styles Insert Delete Format AutoSum A Fill - Sort & 2 Clear Filter Editing Find & Select Cells H I 2 Times New Roman - 20 - A A = = = Dr Wrap Text General E Copy Paste BI U F A Format Painter E BE Merge & Center - % * .. Conditional Format Formatting as Table Clipboard Font Alignment Number Styles A1 fox My Company Α F G 1 My Company Projected Budget-2019 3 Expense 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. Annual Total Average 4 Rent 2800 2800 3000 3000 =SUM(B4:E4) AVERAGE(B4:14) 5 Electric 700 500 500 800 =SUM(B5:E5) AVERAGE(B5:E5) 6 Phone 300 300 300 =SUM(B6:E6) AVERAGE(B6:16) 7 Parking 80 120 160 =SUM(B7:E7) =AVERAGE(B7:17) 8 Supplies 800 600 600 500 =SUM(B8:58) =AVERAGE(B8:E8) 9 Payroll 6000 9000 12000 12000 =SUM(B9:E9) AVERAGE(B9:E9) 10 Total =B4+B5+B6+B7+B8+B9 =C4+C5+C6+C7+C8+C9 =D4+D5+D6+D7+D8+D9 =E4+E5+E6+E7+E8+E9 =SUM(B10:E10) 11 Average =B10/6 =C10/6 =D10/6 =E10/6 12 Maximum =MAX(B4:39) =MAX(C4:09) =MAX(D4:09) =MAX(E4:E9) 300 Minimum =MIN(B4:14) =MIN(B5:E5) =MIN(B6:56) =MIN(B7:57) =MIN(B8:E8) =MIN(B9:E9) % of Total =F4/$F$10 =F5/$F$10 =F6/$F$10 =F7/$F$10 =F8/$F$10 =F9/$F$10 160 1st Qtr. Projected Budget-2019 $15,000 Rent Electric Expense Category Phone Payroll 56% Rent 26% Electric -7% _Phone 3% _Parking Supplies 1% 7% Parking 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. Supplies Payroll Quarter Sheet2 Sheet3 KIN | Ready | Sales || BOU 100% ^ (x ENG 05-02-2020 16:32 Type here to search o W X OP 9 e F2