How To Adjust A Budget: Original, Cash, Income And Balance Sheet Budgets

Blondia Rocks Inc (BRI) is a manufacturing company in Southwestern Ontario that manufactures and sells a single product, a pet rock affectionately named Rocky! The company produces a rolling 12 month master budget several months in advance of the end of the year. The fiscal year end is December 31.

Unfortunately last month, BRI’s controller unexpectedly left the company for a large multi-national pet rock distributor. She provided no notice and had not even started the budget process when she left. Time is quickly running out and you have been retained by the CEO of Blondia Rocks Inc., Carter Blondia, to prepare the master budget for the coming 2020 fiscal year. He has provided you with an Excel file with information compiled by the staff accountant and sales manager. He believes everything you will need to prepare the master budget for the first quarter of 2020 is included in the document (refer to the “Accountant Data File” tab).

Required:
1. The CEO has requested you prepare a master budget (month by month) for the first quarter of the 2020 fiscal year. He would like to see the following Operating Budgets included in the package:
Sales Budget
Schedule of Cash Receipts
Production Budget
Direct Materials Budget
Schedule of Cash Disbursements
Direct Labour Budget
Manufacturing Overhead Budget
Selling and Administrative Budget
Ending Finished Goods Inventory Budget (including COGS calculation)
Budgeted Income Statement

2. He would also like to see the following Financial Budgets included in the package:
Cash Budget
Budgeted Balance Sheet

3. A capital expenditure budget is required to analyze the three possible projects
identified by management. Mr. Blondia would like you to analyze each project utilizing
the net present value method and provide your recommendation of which project
should be undertaken along with rationale. BRI has limited funds to invest in these
capital expenditures and thus only one project can be chosen. It is management’s
intention to move forward with the project sometime in late July and thus the decision
of which project to choose should not impact your operating or financial budgets for
the first quarter.


Mr. Blondia has also made it clear he does not want to reinvent the wheel each year during the budgeting process. He would like you to submit an electronic version of the budget file to use as a template in future years. Since he is a whiz at Excel he knows this is best accomplished by using one Excel workbook with multiple tabs and linking numbers as much as possible across tabs.

Schedule of Deliverables

1. Budget completion schedule – Should show when each budget is to be completed by
to make sure you complete the budget on time. You should also have a group
member asigned to complete each budget. How you break the budgets up is your
choice. Some will be easier than others.

	Due:
	Deliverables:
	1. Hard copy of the budget signup sheet submitted in class
	2. Electronic copy of the budget signup sheet submitted into the Submissions dropbox by end of the day.

2. Submit a hard copy before end of class of the completed budget. You will be required to
submit an electronic copy of the project to the FOL Submissions dropbox at the end of Week 10.
Put effort into the presentation of the budget (i.e. how it looks). Look at the grading
rubric and you will notice there are several marks assigned to the asthetics and
professionalism of the project. How does it look? Did you use colour? Graphs? Charts?
Was your formatting consistent etc. Also note several marks are assigned to linking
the electronic file together properly. This is an important part of any electronic file.

	Due:
	Deliverables:
	1. Hard copy (report quality) submitted in class
	2. Electronic copy (presentation quality) submitted into the Submission dropbox in FOL by the end of the day
3. Capstone Project Test – the test will ask you various questions regarding the project
your group has submitted. It could be asking you how a specific number was calculated
or which budget a number comes from, for example. You need to be involved in the entire
budget process to be well prepared for this 45 minute test.

	Due:

	Deliverables:
	1. Capstone project test in class

Additional Notes:

You have been provided with an Excel file in which to complete the Master Budget.
The “Instructions” and “Accountant Data File” tabs contain all the information you need to
complete the project. The “Data” tab is where you can enter key information from the
aforementioned tabs and do much of your rough work. Make sure you organize your
rough work logically on this tab and label your work spaces (in case I have to peruse it
for part marks).

What follows after that is a number of blank worksheets already labeled for each of
your individual budgets. These tabs should be used to present your final budgets as you
would present them to a budget committee. No rough work should appear on these
tabs and each budget should be properly formatted as to look professional in every
manner. Use dollar signs and underlining where you feel is appropriate (think back to
your accounting courses and financial statement presentation). Show decimals only
where appropriate (per unit costs and in smaller numbers). For all larger dollar figures
allow Excel to round up to the largest dollar for you (show no decimals).

When preparing your individual budgets use the textbook to determine the proper
format and layout to use. If you feel that additional notes to a specific budget are
required these may be displayed below the budget and properly identified. Do not
comment on information that is obvious from the budget. If you have made any
assumptions this would be the place to present those assumptions.
Below is a list of account balances from the company’s general ledger projected for the
year end December 31, 2019:
Cash
Accounts receivable
Allowance for Doubtful Accounts
Inventory – raw materials
Inventory – finished goods
Property, plant and equipment
Accumulated depreciation – PPE
Accounts payable
Income tax payable
Non interest bearing loan payable
Dividends payable
Common shares
Preferred shares
Retained earnings

In conversations with the auditors it is believed that Blondia Rocks Inc. will be required to
make monthly income tax installments during 2020 of
income tax liability from 2019 will be paid in March 2020 and the projected income tax rate
on net income for 2020 is expected to be

Management has experienced stock outs in prior years and the response from the
merchandisers who purchase BRI’s pet rocks has not been good when this happens. The
senior purchasing manager has concluded that to prevent future stock outs BRI should
maintain an ending inventory equivalent to

Annual sales forecasts for the current year and 2020 are as follows:
31-Dec-19	73,00,000
31-Dec-20	82,00,000

BRI does not earn it’s revenue evenly across the year. Sales are strongest in the months
leading up to Christmas because of Rocky’s appeal as a unique gift idea. Summer also
tends to have stronger sales as the nice weather allows consumers to take Rocky for long
walks in the summer sun. Below is the breakdown of the projected sales allocation:

January	7%
February	15%
March	4%
April	2%

Management believes this trend will continue into the foreseeable future.
The closing accounts payable on the projected 2019 balance sheet comes from the
following direct materials purchases

Nov-19	$ 14,79,840
Dec-19	$ 18,91,680

The accounting department policy for payment of raw materials is as follows:
Paid in the month of purchase
Paid one month after purchase
Paid two months after purchase

Rocky is sold as a smoothly polished rock with no discerning features. BRI has entered into
a joint venture with a party supply manufacturer who provides “Mod Kits” that allow a
consumer to customize their Rocky based on their individual tastes! Different facial
features are available as well as small leashes for taking Rocky for a walk. The joint
venture is outside the scope of this budget (meaning you don’t need to incorporate it into
your analysis) and as such there is only one raw material used in production. Giant Boulder
(GB for short) is exactly what it sounds like… a giant boulder. BRI orders in these giant
boulders and then uses jack hammer like machines to chip away chunks of the rock. These
rock chunks are then transported inside the manufacturing facility where they are further
reduced in size and then shaped and polished before being packaged and shipped out.
Below are the details for the raw materials used in production:

GB required per unit (kg):
Cost of GB (per kg)

The senior purchasing manager has also requested that a minimum ending raw materials
inventory be maintained. The company policy is to maintain an ending raw materials
inventory equivalent to

Historical collection data shows that not all sales are collected in the month of sale.
Collections spread out over three full months and a small portion of all sales is shown to be
uncollectible. These bad debts are to be allowed for in the month of sale (i.e. use the
allowance for doubtful accounts account). Below is the forecasted collection allocation:
Collected in the month of sale
Collected one month after sale
Collected two months after sale
Uncollectible

Due to the nature of the work required to transform the GB raw material into the finished
Rocky product the process is highly automated. Thus the labour costs incurred by BRI are
relatively low. The payroll costs listed below include all employer payroll costs as well as
fringe benefits and are paid in the month incurred.
Average hourly labour rate
Labour time required per unit (min)

The closing accounts receivable on the projected 2019 balance sheet comes from the
following sales:

Nov-19	$ 36,50,000
Dec-19	$ 1,09,50,000

The staff accountant remembers the old controller mentioning something about
administrative expenses being a mixed cost. He is not sure what she meant but found
this information scribbled on a piece of paper on her desk regarding previous year’s
performance

Lowest level of sales (units)
Highest level of sales (units)

The staff accountant does know that administrative costs are paid in the month in which
they are incurred and that the bad debts expense is recorded each month separate from
the above costs.

Manufacturing overhead is broken down into two categories, Variable and Fixed. The
information for each is presented below:

Variable overhead costs:
Indirect materials
Utilities
Maintenance (factory)
Purchasing costs
Other (miscellaneous)
Total variable overhead per unit

Fixed overhead costs (annual):
Training & development
Repairs and maintenance
Supervisors’ salaries
Depreciation on the equipment
Insurance (factory)
Other (miscellaneous)
Total fixed overhead (annual)

Additional notes:
1. To avoid financing fees on the insurance policy it is paid in full at the beginning of the
fiscal year (January).
2. All other fixed overhead is incurred evenly across the year (including straight line
amortization) and all cash overhead is paid for in the month incurred.

The dividends payable at the end of last year are to be paid at the end of March.

Several years ago BRI management negotiated an arrangement with their bank that if
they maintained a minimum closing cash balance of
provide them with an operating line of credit at a preferred rate of

The operating line has a maximum borrowing limit of
the budget, you find that BRI will need funds in excess of this amount or is coming close to
exceeding it’s limit the CEO has asked that you notify him immediately.

The arrangement with the bank stipulates that all borrowing is considered to happen on
the first day of the month and repayments occur on the last day of the month. Monthly
interest is calculated based on the balance at the end of the month. The monthly interest
payment is automatically withdrawn from the account on the first day of the next month.
The managers of BRI have identified three separate capital projects they would like to
move forward with. When analyzing the three options please keep in mind the following
assumptions:
1. All cash flows come at the end of the year
2. All cash flows are immediately reinvested in another project that has a similar return
3. All cash flows can be predicted with certainty

As mentioned by the CEO the three projects listed below are mutually exclusive. Therefore
only one project can be chosen for implementation.

The discount rate that should be used to analyze all three projects is

Project 1: Purchase a new jack hammer machine that will increase efficiencies in reducing
the Giant Boulder raw material into manageable chunks.

Project 2: Install a conveyor belt from the GB yard directly into the facility to replace the
aging transport buggies used to get the chunks of raw materials into the factory for
processing

Project 3: Install a state of the art crane system for moving the GB materials around the
storage yard and transferring the chunks destined for production onto the transport
buggies

The original investment required and cost savings from each project are listed below.


Capital Investment

Annual net cost savings:
Year 1
Year 2
Year 3
Year 4
Year 5


The terms of the non interest bearing note require the full amount to be paid in the
following manner during 2020.

January 16th, 2020
February 16th, 2020

The loan is payable to a shareholder of the company who loaned the money to BRI a few
years ago to help the company through a tight cash flow period. The board of directors
has approved the repayment.
october sales	=

october ending inventory	=

june sales	=
may ending inventory	=

material required for may production	=


Difference between highest and lowest sales	=
Difference between highest and lowest cost	=

variable cost per unit	=

Maximum variable cost	=
Minimum Variable Cost	=

Fixed Cost	=
Fixed Cost per month	=
Blondia Rocks Inc.
Sales Budget
For the year ending December 31, 2020

Particulars	November	June	Quarter (Jan – Mar)
	10%	4%
Expected Unit Sales	730000	328000	2132000
Unit Selling Price	$ 5.00	$ 5.61	$ 5.61
Total	$ 36,50,000.00	$ 18,40,080.00	$ 1,19,60,520.00

	Blondia Rocks Inc
	Schedule of Cash Receipts
	For the year ending 2020

Month	November	Quarter
November Sales
12% X $ November Sales	4,38,000.00
70% X $ November Sales
18% $ November Sales		6,57,000.00
December Sales
12% X $ December Sales
70% X $ December Sales		76,65,000.00
18% X $ December Sales		19,71,000.00

January Sales
12% X $ January Sales		3,86,416.80
70% X $ January Sales		22,54,098.00
18% X $ January Sales		5,79,625.20
February Sales
12% X $ February Sales		8,28,036.00
70% X $ February Sales		48,30,210.00
18% X $ February Sales		12,42,054.00
March Sales
12% X $ March Sales		2,20,809.60
70% X $ March Sales
18% X $ March Sales

Total Cash Collection		1,93,92,195.60
Cash Collection For Quarter		28,61,324.40
Blondia Rocks Inc.
Direct material Budget
For the year ending December 31, 2020


	November
Units to be produced	1022000
Direct material per unit	3.00
Total Kg needed for production	30,66,000.00
Add: Desired ending materials(kilograms)	14,00,100.00
Total Material Required	44,66,100.00
Less: beginning direct materials(kG)	7,66,500.00
Direct material purchases	36,99,600.00
Cost per kilogram(in Dollars)	0.40
Total cost of direct material purchases	14,79,840.00
For The Year Ending 2020

Month	November	Quarter (Jan-Mar)
November Purchases
25% X $ November	3,69,960.00
70% X $ November
5% X $ November		73,992.00
December Purchases
25% X $ December
70% X $ December		13,24,176.00
5% X $ December		94,584.00

January Purchases
25 % X $ January		2,37,390.00
70 % X $ January		6,64,692.00
5 % X $ January		47,478.00
February Purchases
25 % X $ February		2,58,300.00
70 % X $ February		7,23,240.00
5 % X $ February
March Purchases
25 % X $ March		79,950.00
70 % X $ March
5 % X $ March
Total Cash Disbursements		35,03,802.00
Accounts Payable		2,91,510.00
Blondia Rocks Inc.
Direct Labour Budget
For the year ending December 31, 2020


Units to be produced
direct labour time(hour) per unit
Total required direct labour hours
Direct labour cost per hour(in dollars)
Total direct labour cost
Blondia Rocks Inc.
Manufacturing overhead Budget
For the year ending December 31, 2020

Variable Cost
Months	January
Units to be Produced	7,05,200.00
Indirect Material cost per unit	0.17
Utilities per unit	0.24
Maintenance cost per unit	0.18
Purchasing cost per unit	0.13
Others (miscellaneous) cost per unit	0.03
Total Indirect material cost	1,19,884.00
Total Utilities cost	1,69,248.00
Total Maintenance cost	1,26,936.00
Total Purchasing costs	91,676.00
Total miscellneous costs	21,156.00

Total Variable Overhead costs	5,28,900.00

Fixed Cost
Month	January
Training & development	3,625.00
Repairs and maintenance	4,333.33
Supervisors’ salaries	12,500.00
Depreciation on the equipment	54,166.67
Insurance (factory)	20,000.00
Other (miscellaneous)	7,791.67
Total fixed overhead (annual)	1,02,416.67

Manufacturing Overhead
Months	January
Total Manufacturing Overhead	6,31,316.67

Reconicilliation of Cash
Month	January
Depriciation of Equipment	54,166.67
Prepaid Insurance	20,000.00
Insurance Payment	240000
Manufactuing Overhead	7,97,150.00
For The Year Ending 2020 (amount in ‘000)

	January
Units to be Produced	5,74,000.00
Variable Cost per unit	0.08
Total Variable cost	45,920.00
Total Fixed Cost per Month	55,413.67
Total Costs	1,01,333.67
Per Unit Manufacturing Cost	Quantity

Direct Materials	3
Direct Labour	0.17
Manufacturing Overhead	1
Unit Product Cost

Budgeted Finished Goods Inventory
Ending Finished Goods Inventory in Units	32800
Unit Product Costs(See above)	$4.31
Ending Finished Goods Inventory in Dollars	$1,41,364.00

Budgeted Cost of Goods Sold	Value
Beginning Finished Goods Inventory	480232.67
Plus Cost Of Goods Manufactured	$88,35,250.00
Cost Of Goods Available for Sale	$93,15,482.67
Less: Ending Inventory	$1,41,364.00
Cost of Goods Sold	$91,74,119
Capital budget analysis
	Project 1

Initial outlay	$ (4,50,000)
Annual net cost savings:
Year 1	$ 1,03,000.00
Year 2	$ 1,03,000.00
Year 3	$ 1,03,000.00
Year 4	$ 1,03,000.00
Year 5	$ 1,03,000.00
Net present value

Project 2 shall be selected as it has positive net present value and other 2 project has negative net present value

Turn in your highest-quality paper
Get a qualified writer to help you with

“ How To Adjust A Budget: Original, Cash, Income And Balance Sheet Budgets ”

Get high-quality paper

NEW! AI matching with writer

Order an Essay Now & Get These Features For Free:

Turnitin Report

Formatting

Title Page

Citation

Outline

Place an Order