1

ACC 303 MASTER BUDGET GROUP PROJECT

Pocket Pieces, Inc.

Raina and Marcelo became friends during their college years and bonded over their love of art,

particularly all of the public units and sculptures in Chicago. They both recently graduated from DePaul

University. After graduation, Raina and Marcelo decided not to seek jobs at established organizations,

but rather, to start their own small business, Pocket Pieces, Inc. Their business produces small replicas of

art sculptures, sized to fit in a pocket that can be displayed on a desk or bookshelf or used as a hanging

holiday ornament or a keychain.

So far Pocket Pieces, Inc. produces just one product a pocket-sized replica of a unit that can be found

at St. Vincents Circle on DePauls Lincoln Park campus. The unit features a life-sized bronze unit of St.

Vincent de Paul talking with two students. Each unit is molded using a

high quality resin and then hand painted, including an inscription that

reads, Community Dignity Education.

Raina and Marcelo chose this unit

to launch their business

because St. Vincents Circle is

one of their favorite places,

always providing them a

reflective, calm atmosphere. Current students and fellow

alumni purchase their Pocket Piece and proudly display it on

their keychain, office desk or shelf, or on their holiday tree

as a token of affection for DePaul and a reminder of the

Vincentian values that they hold dear, long after graduation.

Raina and Marcelo, not sure about how their business would perform, thought that after a couple of

years if their business was successful on a small scale within the DePaul community, they would

potentially expand to produce pocket sized replicas of other art sculptures found all over the city. For

now though, they sell Pocket Pieces at various locations around the Lincoln Park and Loop campuses,

including the DePaul bookstores, the Student Center, and at pop-ups during large University events such

as basketball games and other sporting events, the Christmas at DePaul event, graduation, and

convocation. In addition to in-person sales, they also launched a website where customers can purchase

24-7.

Together, Raina and Marcelo pooled assets together to fund their business. They took money from their

personal savings that they had accumulated while working part time during college and they also

received a couple of cash gifts from various relatives. It really paid off when they each gave a Shark

Tank type of pitch at Thanksgiving dinner during their senior year at DePaul! Hey, they figured it was

better than talking about politics. With the start-up funds, Raina and Marcelo purchased a little art

studio, some equipment, materials, supplies, and paid administrative fees for items such as a business

license.

Raina thinks they should prepare a master budget for the upcoming year. While Raina and Marcelo are

gifted artists, they are struggling a little with the accounting. They know they need to come up with a

sales budget by forecasting how many units they believe they will sell in each quarter of the upcoming

year. They met and agreed on the following projected sales levels:2

Quarter 1, 2024 2,200 units

Quarter 2, 2024 2,850 units

Quarter 3, 2024 1,800 units

Quarter 4, 2024 1,700 units

Quarter 1, 2025 2,000 units

Quarter 2, 2025 2,500 units

Your team was recently at a party with Raina and Marcelo where they were doing some soul-searching

about whether they should keep going with Pocket Pieces, Inc. or try to get hired by a big firm. As

accounting majors, your team knows these friends need a formal plan. You can see that Raina and

Marcelo have their hearts invested in this business, so your team agreed to help them complete a

master budget for the coming year. Your team even agreed to help them out pro-bono because Here,

We Do!

Your team will prepare a master budget for the year 2024 for Pocket Pieces, Inc. This includes:

1. sales budget

2. expected cash collections schedule

3. production budget

4. direct materials purchases budget (one for each DM)

5. cash purchases budget

6. direct labor budget

7. manufacturing overhead budget

8. ending finished goods budget

9. selling & administrative expense budget

10. cash budget

11. budgeted income statement

12. budgeted balance sheet

Each of the budgets above for the year 2024 and should be broken down by quarter, except for the

budgeted income statement and budgeted balance sheet. For example:

Prepare your master budget in Excel, using formulas wherever you need to perform a calculation. No

credit will be given if you do not use formulas.

This project is open book and open notes. This means you may consult your friends and colleagues and

any books, online sources, or notes you may have. I am here to provide guidance and answer questions

about the project, but I will not check any budgets prior to submission to be fair to all groups in the

class.

Each student should contribute to the group in good faith. Remember that each student will fill out a

self & peer evaluation located in the Week 10 content folder on D2L. Part of your grade this quarter is

your participation on this group project. Your team is counting on you!

Please turn in only one project per group under the Submissions Tab on D2L by the project deadline. Be

sure to include team members names, section number and group number on your file. Your submission3

should include one Excel file with the master budget and 2 dashboard visualizations, along with a

sentence below each visualization that provides your groups interpretation.

Here is the information you need to get started:

Your team is helping to prepare Pocket Pieces, Inc.s quarterly budget for the year 2024. Pocket Pieces,

Inc. has this balance sheet at the beginning of the year:

1. Your team needs to start with the sales budget. The units are expected to sell for $50 each. Pocket

Pieces sells all merchandise on credit. Historically, Pocket Pieces receives 55% of each quarter's sales

during the quarter and 45% in the next quarter.

2. Pocket Pieces plans to stock ending inventory of finished goods to equal 25% of the next quarter's

sales. Finished Goods inventory at 1/1/2024 is 190 units at a cost of $24.25 per unit. Pocket Pieces uses

FIFO to account for finished goods inventory.

3. The units are made with a high-quality resin and paint. The units each require 5 ounces of resin at a

cost of $0.75 per ounce. In addition, the units require 2 ounces of paint at $0.40 per ounce. At the

beginning of 2024, Pocket Pieces had 425 ounces of resin on hand and no paint on hand. Pocket Pieces

plans to keep 30% of the resin and paint required for next quarter's production in inventory at the end

of each quarter. Pocket Pieces pays for all purchases 45% in the quarter of purchase and 55% in the

quarter following purchase.

4. Each unit requires 0.8 hours of direct labor at $20 per hour. Wages are paid on the last day of each

month for that month's work. Other costs are paid in the quarter unless otherwise noted.

5. Variable overhead is estimated at $2 per direct labor hour. Fixed overhead costs are $32,000 per

year. Fixed overhead includes depreciation of $7,000 per year.

6. Variable selling and administrative costs are $1.75 per unit. Fixed selling and admin costs are $39,000

per year. (This includes $3,000 of depreciation per year.)

7. Pocket Pieces makes quarterly income tax payments of $6,000 each. The corporate income tax rate

is 20% of net income before taxes (consider any overpayments or underpayments on your balance

sheet).4

8. Pocket Pieces pays quarterly dividends of $1,500.

9. Pocket Pieces will purchase additional equipment on the following schedule (depreciation is already

included above):

Quarter 1 4,100

$

Quarter 2 3,000

$

Quarter 3 -

$

Quarter 4 -

$

10. Pocket Pieces has to maintain a minimum cash balance of $10,000. Any projected borrowings are

assumed to be borrowed at the beginning of the quarter. Payments are made at the end of the

following quarters to the extent cash is available. All borrowings and repayments are made in $1,000

increments. Interest is paid at the time of repayment. Interest is calculated at 10% per year (no

compounding).

Excel Dashboard

After the master budget is prepared, your team is thinking that it

might be helpful to Raina and Marcelo to prepare an Excel dashboard.

A dashboard transforms the budget data you have into something

visual thats easier for people to grasp and utilize in decision-making.

After all, whats the point of preparing a master budget if Raina and

Marco are lost in a sea of numbers and dont see the overall trends

impacting their business, especially when it comes to revenues,

expenses, profits, and cash. Follow these steps to get your teams Dashboard set up. To create a

dashboard, you will need three separate sheets in your Excel file.

1. Name your first sheet (the one that has your master budget on it) with something that makes sense,

such as Budget or Master Budget.

2. Next, create a second sheet labeled Chart Data. That tab is where youll store only the data that

needs to be fed into different charts for the dashboard.

3. Last, create a sheet labeled Dashboard where visualizations will appear. Your team should create

two visualizations in your dashboard to help you communicate important information from the master

budget to Raina and Marcelo. For each visualization in your dashboard, write a sentence or two below it

to inform Raina and Marcelo how your group interprets the visualization.

You will use the Chart Feature in Excel to create your dashboard. There are many possibilities here so

be thoughtful, explore, and have fun! There are bar charts, column charts, pie charts, line charts, scatter

plots, and many more. Keep in mind that not all of them will be the best at visualizing the data. For

example, a line chart is great for analyzing trends over time but a pie chart is more effective for looking

at the composition of something at a point in time. If you are not sure which chart is the best choice,

play around and think about it as a team. Generate a few different charts, look at them, and judge which

ones make the most sense for displaying an essential part of the budget in a manner that is easily

understood.

When creating a visualization, you may not need to use the whole master budget at onceyoull need

to focus on only the pieces you need at that time. The best way to do this is by copying and pasting the

budget you are interested in (say, the income statement) in the Chart Data sheet that you created. Then,

use the Filter option in Excel. To do that, highlight the entire budget you are visualizing (say, for5

example the income statement), click the Data ribbon in the toolbar, and then click Filter. You will

see that little arrows appear next to your column headers. If you click one of those arrows, youll be

presented with a drop-down menu that you can use to filter your data and select only the line items you

want to include.