Excel Budget Report

ITEC200 Homework 1 – Excel Budget Report

1

Technical Learning Objectives

• Manipulate data and add formulas. • Use: Anchoring, SUMIF, COUNT, IF, copyand-paste. • Build, use, modify and nicely format a "results" worksheet for presentation to management.

Business Learning Objectives

• Learn to prepare one of the most-useful spreadsheets of them all: a pro forma budget, which has applications in Applications: Accounting, Finance, Entrepreneurship, and International Business.

You should have completed most of the necessary data and budget details from Lab 1, so you now need to prepare a nicely formatted summary budget for the company's senior managers all the way out to 2022, with ratios, and with charts on a new tab. Start with your Lab 1 file and rename it to: ITEC200-00X_last name_first name_Homework1 (where 00X = your section number).

In this part, you will see an illustration in the power of formula replication to help you speed up the development of spreadsheet models like this. This is where all the anchoring you did earlier will pay off. Grading is weighted on correct anchoring and copying.

You have built some of Year 1 (2018) so the first thing is to finish Year1, then Year2, and then copy Year2 into subsequent years.

Year 1 (Note that not every line is explained here). a. COGS is structured very much like the Revenues. b. B17, gross profit is B10 - B16 c. Cells B20, B21, B22, B23 HR, travel, office equipment, office supplies. Sum the 2 or 3 items for each of the categories in the Expenses sheet. d. General & Administration Expenses: these expenses are an estimated percentage of all other expenses. In cell B24, sum all other expenses (B20:B23) and multiply this sum by the Misc. Admin Expense rate. e. B25. Subtotal of direct expenses. f. Depreciation is computed in the depreciation sheet. Use Double Declining Depreciation (DDB) with these figures: $219,234 cost basis; salvage is $10,000; life of 5 years. If you are unfamiliar with this term, please see: https://www.accountingcoach.com/blog/doubledeclining-balance-method-of-depreciation. The formula for Excel is explained at: https://support.office.com/en-us/article/DDB-function-519a7a37-8772-4c96-85c0ed2c209717a5. g. Indirect expenses = subtotal Admin + depreciation. h. Profit (or loss) before taxes is gross profit minus indirect expenses. i. Taxes in cell B31 = profit before taxes x tax rate. j. Finally, compute the first ratio: profit (after tax) to revenue rate in cell B36. This is an important rate of the profitability of each revenue dollar. Use percent with 1 decimal. You cannot yet compute the next ratio; only in 2019.

Year 2:

Projections here! Forecasts about the future. a. COGS: increase is found in parameters. b. Since all expenses increase at the same rate, 'Indirect Expenses growth rate' then create an anchored set of figures for 2018, Human Resources, Travel, Office Equipment, Office Supplies, General & Administrative. c. In cell C37, profit increase/decrease = change in profit from previous year, divided by previous year profit. Use percent with 1 decimal.

Note: the assignment up to this point is worth 90% of your grade.

Prepare a Chart (worth 10% of your grade):

You already prepared a chart in the class lab. Now prepare a chart that is a bit more elaborate. Use a line chart with 3 projected lines: Revenues, COGS, and Net Profit (Profit after taxes). Recall that you already did Revenues above. This time, do three lines. Each line should be a different color. The title should be 'Budget prepared by XYZ' where XYZ is your name. Label the lines