Question
In: Accounting
Spring 2020 Spreadsheet Project Name: Lexie's Wool Sweaters Projected Budgeting Data Sales & Collections October 2020...
| Spring 2020 Spreadsheet Project | ||||||
| Name: | ||||||
| Lexie's Wool Sweaters | ||||||
| Projected Budgeting Data | ||||||
| Sales & Collections | ||||||
|
October 2020 |
November 2020 |
December 2020 |
January 2021 |
February 2021 |
||
| Sales in Units (Sweaters) | 30,000 | 34,000 | 55,000 | 47,000 | 32,000 | |
| Selling Price per Sweater | $ 100.00 | |||||
| Cash Sales Collected in the Month of Sale | 30% | |||||
| Credit Sales Collected in the Month of Sale | 50% | |||||
| Credit Sales Collected in the Following Month | 20% | |||||
| Ending FG Inventory Requirement | 3% | of next months unit sweater sales | ||||
| Ending FG Inventory, September 30 , 2020 | 1,500 | sweaters | ||||
| Product Input Expenses | ||||||
| Direct Materials | ||||||
| Ending RM Inventory, September 30, 2020 | 8265.60 | yards | ||||
| Yards of Wool Required per Sweater | 4 | yards per sweater | ||||
| Raw Materials Cost per Yard of Wool | $ 3.50 | per yard | ||||
| Ending RM Inventory Requirement | 7% | of next months sweater production needs | ||||
| Wool Purchases Paid for in the Month of Purchase | 85% | |||||
| Wool Purchases Paid for in the Month following the Purchase | 15% | |||||
| Direct Labor | ||||||
| Number of Workers Required for the Making of Each Sweater | 5 | workers | ||||
| Labor Hours Required per Worker per Unit of FG (Sweater) | 0.5 | hours | ||||
| Labor Cost per Hour | $ 15.00 | per hour | ||||
| Manufacturing Overhead | ||||||
| Variable Manufacturing Overhead | $ 11.75 | per sweater | ||||
| Fixed Manufacturing Overhead | $ 30,200.00 | per month (Oct.) | $ 30,750.00 | per month (Nov. & beyond) | ||
| Noncash Fixed Manufacturing Overhead (included in above) | $ 10,250.00 | per month (Oct.) | $ 15,750.00 | per month (Nov. & beyond) | ||
| Selling & Administrative Expenses | ||||||
| Variable S&A | $ 7.37 | per unit sold | ||||
| Fixed S&A | $ 23,900.00 | per month | ||||
| Noncash Fixed S&A (included in above) | $ (10,750.00) | per month | ||||
| Factory Update & Cash Flow | ||||||
| Factory Update (PP&E) | $ 400,500.00 | paid on October 31, 2020 | ||||
| Principle Borrowed on October 1, 2020 | $ 300,000.00 | |||||
| Principle Repaid on November 30, 2020 | $ 300,000.00 | |||||
| Interest Payment on Borrowings in October & November | $ 9,000.00 |
per month (paid in following month) |
||||
Create a Schedule of Cash Collections in Excel using formulas only
Solutions
Expert Solution
As requested, only cash collections schedule is prepared using excel formulas:
Excel formulas:
ekkarill92 answered 2 years agoRelated Solutions
Spring 2020 Spreadsheet Project Name: Lexie's Wool Sweaters Projected Budgeting Data Sales & Collections October 2020...
Spring 2020 Spreadsheet Project
Name:
Lexie's Wool Sweaters
Projected Budgeting Data
Sales & Collections
October
2020
November
2020
December
2020
January
2021
February
2021
Sales in Units (Sweaters)
30,000
34,000
55,000
47,000
32,000
Selling Price per Sweater
$ 100.00
Cash Sales Collected in the Month of Sale
30%
Credit Sales Collected in the Month of Sale
50%
Credit Sales Collected in the Following Month
20%
Ending FG Inventory Requirement
3%
of next months unit sweater sales
Ending FG Inventory, September...
% - XYZ Company provides you with the following data projected for 2018: Projected sales are...
% - XYZ Company provides you with the following data projected
for 2018:
Projected sales are 30,000 units. Inventory at Dec 31,2017 was
4,000 manufactured units. The desired ending for Dec 31, 2018 is
8,000 manufactured units.
a) 5% - Provide a production budget for the number of units that
need to be produced.
b) 6% - Assume for each unit manufactured, four pounds of raw
materials are needed. Assume the desired ending inventory became
10,000 manufactured units. What effect...
Below is the data for the spreadsheet for a project selection model: Project NPV (return) cash...
Below is the data for the spreadsheet for a project selection
model:
Project
NPV (return) cash investment
personnel requirements
1
75,000
30,000
10
2
50,000
45,000
5
3
25,000
75,000
10
4
85,000
30,000
4
5
50,000
45,000
8
6
25,000
75,000
7
7
95,000
45,000
6
8
25,000
60,000
1
9
75,000
30,000
2
10
65,000
45,000
2
You have $250,000 to invest and 36 people. Maximize NPV by
approving/rejecting projects.
Multiple Choice #8: How many projects of the...
Process Cost Excel Project Create a new Excel spreadsheet and name it “Last name_PC”. You project...
Process Cost Excel Project
Create a new Excel spreadsheet and name it “Last name_PC”. You
project is to create a model for a production cost report using the
weighted average method for the month of May. Following
good Excel design techniques, you should have an input area in
which you put the department information for the month, and an
output area that calculates the production cost report. As always,
you should have only formulas or references in your output area....
Process Cost Excel Project Create a new Excel spreadsheet and name it “Last name_PC”. You project...
Process Cost Excel Project
Create a new Excel spreadsheet and name it “Last name_PC”. You
project is to create a model for a production cost report using the
weighted average method for the month of May. Following
good Excel design techniques, you should have an input area in
which you put the department information for the month, and an
output area that calculates the production cost report. As always,
you should have only formulas or references in your output area....
Process Cost Excel Project Create a new Excel spreadsheet and name it “Last name_PC”. You project...
Process Cost Excel Project
Create a new Excel spreadsheet and name it “Last name_PC”. You
project is to create a model for a production cost report using the
weighted average method for the month of May. Following
good Excel design techniques, you should have an input area in
which you put the department information for the month, and an
output area that calculates the production cost report. As always,
you should have only formulas or references in your output area....
Patricia’s housekeeping company has the following data projected for June. Beginning cash balance $24,500 Cash collections...
Patricia’s housekeeping company has the following data projected
for June. Beginning cash balance $24,500 Cash collections from
customers 36,200 Cash disbursements 54,300 Patricia wants to
maintain a cash balance of $10,000 or more. Any amount below this
can be borrowed from a local bank as needed in increments of
$1,000. All borrowings are made at month end. In Patricia's cash
budget for June, how much money will Patricia need to borrow at
month end? A. $6,000 B. $5,000 C. $7,000...
Graham Potato Company has projected sales of $13,200 in September, $16,000 in October, $23,200 in November,...
Graham Potato Company has projected sales of $13,200 in
September, $16,000 in October, $23,200 in November, and $19,200 in
December. Of the company's sales, 25 percent are paid for by cash
and 75 percent are sold on credit. Experience shows that 40 percent
of accounts receivable are paid in the month after the sale, while
the remaining 60 percent are paid two months after. Determine
collections for November and December.
Also assume Graham’s cash payments for November and December...
Graham Potato Company has projected sales of $20,400 in September, $22,000 in October, $30,400 in November,...
Graham Potato Company has projected sales of $20,400 in
September, $22,000 in October, $30,400 in November, and $26,400 in
December. Of the company's sales, 30 percent are paid for by cash
and 70 percent are sold on credit. Experience shows that 40 percent
of accounts receivable are paid in the month after the sale, while
the remaining 60 percent are paid two months after. Determine
collections for November and December.
Also assume Graham’s cash payments for November and December...
Graham Potato Company has projected sales of $14,400 in September, $17,000 in October, $24,400 in November,...
Graham Potato Company has projected sales of $14,400 in
September, $17,000 in October, $24,400 in November, and $20,400 in
December. Of the company's sales, 25 percent are paid for by cash
and 75 percent are sold on credit. Experience shows that 40 percent
of accounts receivable are paid in the month after the sale, while
the remaining 60 percent are paid two months after. Determine
collections for November and December.
Also assume Graham’s cash payments for November and December...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- 1D List Practice Could you write the code to solve the following problem that uses 1D...
- How the concept of moral hazard can be applied to the 2008 financial crisis?
- Write a program that prints and calls two methods. 1. Compute and return the future value...
- Assume you have been selected to lead your team in the development of a quality management...
- java You are creating an array where each element is an (age, name) pair representing guests...
- Joni is a financial planner for ABC firm. She provided all of her clients with the...
- Suppose you plan to retire in 40 years. If you make 10 annual investments of $...
ADVERTISEMENT