The real-world (this company is based in the northwest suburbs of Chicago) scenario: Your company assembles wire (light-metal) closet shelving units at a manufacturing facility and ships them to home builders and chain "big box" do-it-yourself stores.
It is October, 2023 and you, the budget manager, just received an e-mail from the CFO, asking you to put together a 2024 budget.
The projected (because the year is not yet over) 2023 financial information is below.
There are three items for you to complete by filling in the applicable personalized information.
Then a 2024 budget will need to be prepared by filling in the 2024 boxes below and submitting to the CFO.
So how to get that done?
For a successful Master Budget, you would seek information and input (even if not very specific) from your company's:
Sales Manager
Production Manager
Administration Manager
This input is provided further below:
				2023 Projected		2024 Budget
Sales				$ 2,340,000
Production Costs:
Direct Material			630,000
Direct Labor			420,000
Manufacturing Overhead		(you fill in)
Other Expenses:
Sales commissions			468,000
Office/Mgmt. Salaries		335,000
Rent				(you fill in)
Utilities				18,000
Supplies				25,000
All Other				50,000
Total costs and expenses		(you fill in)
Net Income			(you fill in)
Budget input from key personnel:
SALES MANAGER
The Sales Manager is in charge of sales of wire shelving in linear feet (the key metric for this business).
with the role of soliciting and receiving orders from home builders and chain stores
The following is projected sales data for (full year) 2023:
150,000 linear feet to the home builders market at a sales price of $10 per linear foot.
60,000 linear feet to the chain store market at a sales price of $14 per linear foot.
In 2024, predicted higher interest rates is causing an expected decrease in sales to the home builders market.
However, in 2024 it is also predicted that inflated travel/vacation prices is going to cause
a small increase in sales to the chain store market (as more people "staycation" and work on home improvement projects)
Sales prices (what is charged to the customers) may be changed if considered appropriate.
(but you would want to consider a change in sales price's effect on volume of sales)
Salespersons are compensated fully on commission based on sales dollars (and the 2024 commission rate is expected to be the same as 2023)
PRODUCTION MANAGER
The production manager is in charge of assembly of these units in the assembly facility adjacent to the office.
Production at the company facility is based on the assembly of the shelving by the linear foot.
You assemble what you need to fill orders, since there is no space for unsold inventory.
The production manager is also responsible for procurement of the raw materials (metal).
You currently pay at a rate of $3 per linear foot, but your supplier is planning a price increase.
The production manager is also responsible for the assemblers that she directly supervises.
They are paid by production at $2 per linear foot.
The production manager earns an annual salary (which is the entire manufacturing overhead).
as follows:
$88,000
However, the production manager wants a raise in 2023!
Reminder: we have covered the cost behavior of direct material and direct labor - An appropriate budget would be based on expected cost per linear feet produced.
ADMINISTRATION MANAGER
The administration manager is in charge of basically everything at your facility except sales and production.
A couple of the issues the administrative manager is working on include:
Hiring an administrative assistant to help implement the new accounts payable software.
Negotiating with the landlord - your lease is up in December and the landlord plans to increase the rent in 2024.
The 2023 full year rent is as follows:
$150,000
Requirements:
Using either this Excel document or a separate format (for example, a Word document, but NOT GoogleDocs or ".numbers"!),
Make a 2024 Master Budget by completing where applicable for 2022 and then filling in all the boxes as applicable for 2023.
In addition, provide a 200-300word description
that describes specifically how you determined the 2024 Sales Budget (for each of the two markets) and your assumptions,
and how you determined the budget for all of the costs and expenses budgeted for 2024.
(hint; are the costs as we know them, fixed or variable?)
In other words, clearly show how you arrived at all of the numbers that you entered into the 2024 boxes, doNOTmake
instructor (or in the future, your supervisor, company controller or CFO) go on an analytical safari hunt to try to figure it out!
For sales, show a breakdown of both markets. Make how you got each number absolutely clear!
So to summarize: We are looking for all boxes in this document to be filled in with explanations for each. There are no
"right answers" here, because it requires some interpretation of the information provided.
However, you do want to use the master budget steps included in this module.
And there is no need for monthly breakdowns in this assignment! This is NOT a cash budget.