Excel Assignment #2 – Master Budget Project

Subject: Business / Accounting

Congratulations (or maybe condolences). As the newbie in the office, you’ve been assigned the
task of preparing the company’s master budget for the next quarter. The company, Crane Corp,
only produces one product, a widget. This widget is made up of 5 gizmos and 3 doodads. Your
job is to prepare all the schedules necessary to complete the master budget including: the Sales,
Production, Direct Materials, Direct Labor, Manufacturing Overhead, Selling General &
Administration, and Cash budgets. In addition, you will need to prepare a schedule showing the
Finished Goods Inventory costs, a budgeted Income Statement, and a Balance Sheet for the
To help you, there is an Excel template titled “BIGBUD Assumptions Sheet”. The first
worksheet in this file is titled Assumptions and contains all the information you need to complete
the project. The Excel file has worksheets titled for all the schedules you will need.
You will complete this project by following the example schedules in Chapter 8 of the text. Note
that you are preparing the budget for the 1st quarter of 2017 (January, February, and March) so
you should have a column for each month as well as a column for the totals for the quarter. On
the production and direct materials budget, you will need to include a column for April as well.
This column should be added to the right of your total column which should include only the
totals for the first quarter.
When you complete your work make sure your Balance Sheet balances. If your balance sheet
does not balance, you have done something wrong and you will need to go back and find your
mistake before submitting your work. Your total assets at the end of March should be $1,036,858
To further assist you, here’s a couple of hints: the unit product cost of the Widget that you calculate in the Finished Goods Inventory
Costs Schedule should be exactly the same as the beginning unit cost of the widgets in
the Assumptions worksheet. No financing activities are required on the Cash Budget for this quarter
This assignment is designed not only to test your knowledge of preparing a master budget, but
also to test your Excel skills.
Here are the Excel items that will be graded:
1. Each worksheet must print on one page in portrait orientation with no scaling. Many
times we get lazy and don’t both to see how our printed Excel reports look when printed.
Don’t fall into this trap. I recommend you use the Print Preview feature in Excel to make
sure your report will print appropriately.
2. All worksheets must have a proper title centered across the top of the page. A proper title
consists of: Company Name, Report Title, Period (or Date). You can either put the report
title in the worksheet header or on the first three lines of the worksheet. Either way, the
titles must be centered on the page. Again, check the Print Preview feature to verify
correct orientation and formatting of your title.
Page 1 of 3 Pages Excel Assignment #2 – Master Budget Project 3. Each worksheet must print centered horizontally on the page. To make this happen go
into margins, select custom margins, and then check the box that says “Center on Page:
4. ALL schedules must use cell referencing to the numbers in the Assumptions worksheet
or to a previous budget worksheet. This is extremely important. DO NOT enter the
numbers themselves from the Assumptions worksheet into the other worksheets. Use an
appropriate cell reference instead.
5. All dollar amounts should be formatted using the Accounting format with zero decimal
places with the following exceptions:
o The cost per gizmo and cost per doodad in the Direct Materials worksheet should
be formatted using the Accounting format with two decimal places.
o The predetermined overhead rate in the Manufacturing Overhead worksheet
should be formatted using the Accounting format with two decimal places.
o The Totals in the Finished Goods Inventory Costs worksheet should be formatted
using the Accounting format with two decimal places.
6. All worksheets must use proper formulas and functions. (I.e. if you’re adding a column
of numbers use the SUM function. DO NOT add the numbers on a calculator and then
enter them into Excel manually.)
7. Spelling. What would your boss think if you gave him a report with spelling mistakes?
You instructor feels much the same. Spelling mistakes will be penalized. With the Excel
spell checker, there are no excuses for spelling errors.
8. Consistency. As you work in Excel, you will develop your own style. Part of this style is
consistency. You want your reports to look like they were created by the same person.
This means you don’t want to change the font, type size, etc. on each separate worksheet.
Whatever you select (my recommendation is to leave the default font and font size as is.
This will make it easier to fit the worksheets on one page). It also means proper
underlining, use of the dollar sign (dollar signs should be used at the first instance of a
dollar amount in a column and in the total (see below)
Proper underlining is important also. Single underline on the line before you total a
column of numbers and double underline the total.
Although you want your Excel reports to be visually appealing, don’t go overboard on
your use of color. Remember, your final product may be printed on a black and white Page 2 of 3 Pages Excel Assignment #2 – Master Budget Project printer. Frankly, in the real word, color is not often used in Excel spreadsheets (shading
is used more frequently).
Excel skills needed to successfully complete this assignment:
Cell referencing
Margin setting
Cell formatting
Merging cells
Centering work on printed page
Spell checker
Submit your assignment through the Canvas Assignments section with the file BIGBUD_Last
Name_First Name where First Name and Last Name are your first and last names. Page 3 of 3 Pages


We can do it for you